prompt for path to loop through files

I have the code below and it works fine but I would like it to prompt for the 
file path as each month the path changes slightly.  

Sub LoopOWC()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

Application.ScreenUpdating = False
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    Set Folder = oFSO.GetFolder("c:\new_files\Jan")

    For Each file In Folder.Files
        If file.Type Like "*Microsoft Excel*" Then
            Workbooks.Open Filename:=file.Path

            UploadData

            ActiveWorkbook.Close SaveChanges:=True
        End If
    Next file
    Set oFSO = Nothing
Application.ScreenUpdating = True
End Sub

0
Utf
1/26/2010 4:15:02 AM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
911 Views

Similar Articles

[PageSpeed] 24

VBANovice,

If you are looking for a way to get the folder path as per the user's 
selection, then you'll need some API calls.  If this is your intention, then 
you'll find SHGetPathFromIDListA and SHBrowseForFolderA useful.  Chip has 
this laid out on his website at the following link if you don't want to use 
MSDN to piece the API calls (and their associated structures) together 
yourself:  http://www.cpearson.com/Excel/BrowseFolder.aspx.  If this is not 
what you are looking for, then please be more specific as to how you want to 
prompt the user and how you intend on using the user's result in your code.

Best,

Matthew Herbert

"VBANovice" wrote:

> I have the code below and it works fine but I would like it to prompt for the 
> file path as each month the path changes slightly.  
> 
> Sub LoopOWC()
> Dim oFSO
> Dim Folder As Object
> Dim Files As Object
> Dim file As Object
> 
> Application.ScreenUpdating = False
>     Set oFSO = CreateObject("Scripting.FileSystemObject")
> 
>     Set Folder = oFSO.GetFolder("c:\new_files\Jan")
> 
>     For Each file In Folder.Files
>         If file.Type Like "*Microsoft Excel*" Then
>             Workbooks.Open Filename:=file.Path
> 
>             UploadData
> 
>             ActiveWorkbook.Close SaveChanges:=True
>         End If
>     Next file
>     Set oFSO = Nothing
> Application.ScreenUpdating = True
> End Sub
> 
0
Utf
1/26/2010 5:16:05 AM
Try out the below

Sub LoopOWC()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

Application.ScreenUpdating = False
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    Set Folder = oFSO.GetFolder(GetSelectedFolder)

    For Each file In Folder.Files
        If file.Type Like "*Microsoft Excel*" Then
            Workbooks.Open Filename:=file.Path

'            UploadData

            ActiveWorkbook.Close SaveChanges:=True
        End If
    Next file
    Set oFSO = Nothing
Application.ScreenUpdating = True
End Sub

Function GetSelectedFolder() As String
    Dim objShell As Object, objTemp As Object
    Set objShell = CreateObject("Shell.Application")
    Set objTemp = objShell.BrowseForFolder(0, "Select folder", ssfWINDOWS)
    If Not objTemp Is Nothing Then GetSelectedFolder = objTemp.Self.Path
End Function

-- 
Jacob


"VBANovice" wrote:

> I have the code below and it works fine but I would like it to prompt for the 
> file path as each month the path changes slightly.  
> 
> Sub LoopOWC()
> Dim oFSO
> Dim Folder As Object
> Dim Files As Object
> Dim file As Object
> 
> Application.ScreenUpdating = False
>     Set oFSO = CreateObject("Scripting.FileSystemObject")
> 
>     Set Folder = oFSO.GetFolder("c:\new_files\Jan")
> 
>     For Each file In Folder.Files
>         If file.Type Like "*Microsoft Excel*" Then
>             Workbooks.Open Filename:=file.Path
> 
>             UploadData
> 
>             ActiveWorkbook.Close SaveChanges:=True
>         End If
>     Next file
>     Set oFSO = Nothing
> Application.ScreenUpdating = True
> End Sub
> 
0
Utf
1/26/2010 5:40:01 AM
My method posted below plus a couple of curiosity questions for Jacob.

Why use Shell when there is a built in FileDialog? I have seen it used a lot 
and wonder if there is a specific advantage.

What is the variable ssfWINDOWS. It comes up as Compile error: Variable not 
defined.

Anyway for the OP this is another option.

Sub LoopOWC()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fd As FileDialog
Dim myPath As Variant


Application.ScreenUpdating = False
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    With fd
    
      .AllowMultiSelect = False
      
      'Edit following line to where you want to start
      .InitialFileName = "c:\new_files"
      If .Show Then
        myPath = .SelectedItems(1)
      Else
        MsgBox "User cancelled" & vbLf & vbLf & _
              "Processing terminated"
              Exit Sub
      End If
      
    End With
    
    Set fd = Nothing
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    Set Folder = oFSO.GetFolder(myPath)
    
    For Each file In Folder.Files
        If file.Type Like "*Microsoft Excel*" Then
            Workbooks.Open Filename:=file.Path
            
            UploadData
            
            ActiveWorkbook.Close SaveChanges:=True
        End If
    Next file
    
    Set oFSO = Nothing
    Set Folder = Nothing
    
Application.ScreenUpdating = True
End Sub
 
-- 
Regards,

OssieMac

0
Utf
1/26/2010 6:50:01 AM
--You can use both methods..The point is its better to have this as a 
separate function so that your code doesnt look crowded and can be re-used. 
It should have been as below...

--(Sorry both ssfWINDOWS is not specified which should be the initial 
folder) The below would restrict the user to have access only to the 
specified folder or within the folder specified..

Function GetSelectedFolder2() As String
    Dim objShell As Object, objTemp As Object
    Set objShell = CreateObject("Shell.Application")
    Set objTemp = objShell.BrowseForFolder(0, "Select folder", 0, "d:\")
    If Not objTemp Is Nothing Then GetSelectedFolder = objTemp.Self.Path
End Function


Or


Function GetSelectedFolder1(Optional strPath As String) As String
Dim objFldr As FileDialog
Set objFldr = Application.FileDialog(msoFileDialogFolderPicker)
With objFldr
    .Title = "Select a folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GetSelectedFolder = "": Exit Function
    GetSelectedFolder = .SelectedItems(1)
End With
Set objFldr = Nothing
End Function

You can use at your convenience...

--
Jacob


"OssieMac" wrote:

> 
> My method posted below plus a couple of curiosity questions for Jacob.
> 
> Why use Shell when there is a built in FileDialog? I have seen it used a lot 
> and wonder if there is a specific advantage.
> 
> What is the variable ssfWINDOWS. It comes up as Compile error: Variable not 
> defined.
> 
> Anyway for the OP this is another option.
> 
> Sub LoopOWC()
> Dim oFSO
> Dim Folder As Object
> Dim Files As Object
> Dim file As Object
> Dim fd As FileDialog
> Dim myPath As Variant
> 
> 
> Application.ScreenUpdating = False
>     Set fd = Application.FileDialog(msoFileDialogFolderPicker)
>     
>     With fd
>     
>       .AllowMultiSelect = False
>       
>       'Edit following line to where you want to start
>       .InitialFileName = "c:\new_files"
>       If .Show Then
>         myPath = .SelectedItems(1)
>       Else
>         MsgBox "User cancelled" & vbLf & vbLf & _
>               "Processing terminated"
>               Exit Sub
>       End If
>       
>     End With
>     
>     Set fd = Nothing
>     
>     Set oFSO = CreateObject("Scripting.FileSystemObject")
> 
>     Set Folder = oFSO.GetFolder(myPath)
>     
>     For Each file In Folder.Files
>         If file.Type Like "*Microsoft Excel*" Then
>             Workbooks.Open Filename:=file.Path
>             
>             UploadData
>             
>             ActiveWorkbook.Close SaveChanges:=True
>         End If
>     Next file
>     
>     Set oFSO = Nothing
>     Set Folder = Nothing
>     
> Application.ScreenUpdating = True
> End Sub
>  
> -- 
> Regards,
> 
> OssieMac
> 
0
Utf
1/26/2010 8:21:01 AM
thanks everyone, lots of good solutions provided.  this one worked perfectly 
for what I'm trying to do. 

"OssieMac" wrote:

> 
> My method posted below plus a couple of curiosity questions for Jacob.
> 
> Why use Shell when there is a built in FileDialog? I have seen it used a lot 
> and wonder if there is a specific advantage.
> 
> What is the variable ssfWINDOWS. It comes up as Compile error: Variable not 
> defined.
> 
> Anyway for the OP this is another option.
> 
> Sub LoopOWC()
> Dim oFSO
> Dim Folder As Object
> Dim Files As Object
> Dim file As Object
> Dim fd As FileDialog
> Dim myPath As Variant
> 
> 
> Application.ScreenUpdating = False
>     Set fd = Application.FileDialog(msoFileDialogFolderPicker)
>     
>     With fd
>     
>       .AllowMultiSelect = False
>       
>       'Edit following line to where you want to start
>       .InitialFileName = "c:\new_files"
>       If .Show Then
>         myPath = .SelectedItems(1)
>       Else
>         MsgBox "User cancelled" & vbLf & vbLf & _
>               "Processing terminated"
>               Exit Sub
>       End If
>       
>     End With
>     
>     Set fd = Nothing
>     
>     Set oFSO = CreateObject("Scripting.FileSystemObject")
> 
>     Set Folder = oFSO.GetFolder(myPath)
>     
>     For Each file In Folder.Files
>         If file.Type Like "*Microsoft Excel*" Then
>             Workbooks.Open Filename:=file.Path
>             
>             UploadData
>             
>             ActiveWorkbook.Close SaveChanges:=True
>         End If
>     Next file
>     
>     Set oFSO = Nothing
>     Set Folder = Nothing
>     
> Application.ScreenUpdating = True
> End Sub
>  
> -- 
> Regards,
> 
> OssieMac
> 
0
Utf
1/26/2010 5:24:02 PM
Reply:

Similar Artilces:

Can't open files from outside Excel
I am running Windowns XP home with Office 2000. Suddenly the progra will not allow me to open files from outside Excel. If I go to Window Explorer and click on a particular file to open it, the error messag says the system cannot locate the file. I then have to open excel an find the file through the program to be able to open it. Same problemn if I receive an excel sheet in an email. I have to firs save the file to some folder and then open it by first opening Excel an looking for the file. I have downloaded the latest update but I am unable to install thes because my licence disks a...

SQL Select Query Prompting for NonExistent Parameter
I'm using Access 97. I've entered the following SQL Query into the Query Tab. =================== SELECT * FROM tblVehicles WHERE fldVehicleTag = ABC123; ==================== When I double click the Query to execute: Instead of returning the recordset Access prompts me for a Parameter. When I enter the tag # --> ABC123 it returns the correct record. Why am I being prompted for a Parameter and how do I stop it? Thanks David On Wed, 17 Mar 2010 22:25:13 -0400, "David" <NoWhere@earthlink.net> wrote: >I'm using Access 97. &g...

Missing File
I am trying to run Publisher 2000, and I am recieving an error code that MSWSTR10DLL is missing. I cannot locate it in my computer and I reloaded the program. This did not help. I searched the Microsoft website for this missing file. I could not find it. I have the file. Do you want a copy? -- Don ------- My real e-mail address is: dschmidtATpacifierDOTcom "MonsterMama" <anonymous@discussions.microsoft.com> wrote in message news:03e901c3d0b7$ba195200$a001280a@phx.gbl... > I am trying to run Publisher 2000, and I am recieving an > error code that MSWSTR10DLL ...

Do Loop
Hi I have a Do Loop which performs the task required however it does leave one row left which it should also delete. In one column I have a K and then numbers or Ktba. The Do Loop is to go down the column to find and delete all the rows with Ktba in them and only stop the loop when the column is empty. The loop does work except it leaves a Ktba behind and I dont understand why. IF you can see what is wrong with my code and advise me I would really appreciate it. count = 4 Do If Application.Range("J" & count) = "Ktba" Then Application...

Where is the file ?
Does anyone know if there is a way to tell when you open a spreadsheet what files it is linking to (other excel spreadsheets)? Thanks. Ty Use Edit, Links to find the workbooks the active workbook is dependent on. -- Jim Rech Excel MVP Jim - sorry one more question - it shows me the links how can I cut off one of the links as it is no longer needed ? Thanks. Ty >-----Original Message----- >Use Edit, Links to find the workbooks the active workbook is dependent on. > >-- >Jim Rech >Excel MVP > > >. > Hi you may have a look at the following tool: htt...

insert text from file
I am not in compatibility mode, but this is not an option as it is grayed out. Any suggestion. Is your cursor in the document where you want the insertion to go? On Jan 14, 10:20=A0am, Tammy O <Tammy O...@discussions.microsoft.com> wrote: > I am not in compatibility mode, but this is not an option as it is grayed > out. =A0Any suggestion. ...

Word Access to Outlook Contact File
I recently moved my data from one computter to a new one and was able to move my contacts to the new outlook file as a sub file of Contacts called My Contacts. I can't get Word to recognize the sub file. -- John Your question may be better answered in a newsgroup appropriate to your problem. This newsgroup is dedicated to the Microsoft Access database product. The Microsoft website is not all that clear and may have misdirected you. -- Arvin Meyer, MCP, MVP Free MS-Access downloads: http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "JOHNH" <...

big file
Hi One of my files has suddenly increased in size from 52k to 1.27MB. I have tried deleting all the cell round my 2 page doc, but to no avail. I am sure it must be a formatting thing for all cells, but don't know what. Help! Jo Hi Jo, See Debra Dalgleish's notes at: http://www.contextures.com/xlfaqApp.html#Unused --- Regards, Norman "Jo" <Jo@discussions.microsoft.com> wrote in message news:DFCB9759-137D-4A89-B39A-AE5498AC8DDC@microsoft.com... > Hi > One of my files has suddenly increased in size from 52k to 1.27MB. I have > tried deleting...

How To Validate the XML file
Hi, I have the following xml file <?xml version="1.0" encoding="UTF-8" ?> <n1:Message xmlns:n1="nz:govt:moh:schemas:diabetes-cardio.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <MessageHeader> <Site>pho1diab</Site> <SendingApplication>MyPracticePMS 8.05a</SendingApplication> <SendingFacility>PracticeName or HeathLinkMailBox</SendingFacility> <ReceivingApplication>Diabetes/CVD v2</ReceivingApplication> <ReceivingFacility>pho1diab</ReceivingFa...

How to change Machine name without Prompt for credentials
Hi I'm hoping there's a way to do this, but I'm not sure. I have an OU configured which contains some Computer accounts and a couple of user accounts. To one of the accounts I've delegated permissions to add and delete "computer" objects in that OU. When I'm logged into one of the computers in the OU and I try to rename the Computer, I'm prompted for credentials, even though I'm already logged on using the delegated account. I enter the username/password and all is good but I'd like not to be required to enter the user/pass. Is the...

Changing printer while excel-file is open
Hello, I have an Excel-file with about 2MB which contains a lot of formattings and named cells. If I now change my Default Printer in the Control Panel while this file is open then Excel needs a few seconds to overtake this new setting. If I then take a look at my open file then I suddenly can see the pagebreaks which were not there before. After that my program tries to hide/unhide columns. But this action now takes much more time than before changing the Default Printer - before it needed about 0,1s and now about 4,0s. I have a similar Excel-file where this does NOT happen. So it must depen...

Copying Pivot Table to new file--why does it still refer to original file?
Hello, I created a Pivot Table in an Excel file sent to me by a co-worker. She sends a updated file each month with the same layout, and I use the Move/Copy Sheet function to add my Pivot Table to the new file. The problem is, the table still points to the consolidation ranges in the file I copied from. So, I must manually redefine all the page fields to point to the current file. Is there any way around this? Thanks! LJ You could record a macro that creates a pivot table from the ranges in the active workbook. Store the macro in Personal.xls, or another workbook that is always ...

"File does not exist" question
Hello! A remote user says this: "Thank you for including me in your emails. However, whenever you send one, I am not able to open the file. My computer says that the file does not exist. Can you remedy this, please ? Thanks." Since no one else has this problem, there couldn't be anything wrong with our server or application, could it? Does this sound like a virus? Where should we start looking? Thank you! -Bahman Do you have other remote users working ok ? What version of Outlook ? What version of Exchange ? What AV software is on the client ? What AV software ...

Where to start render loop?
Hi, I have a dialog based MFC application that renders lots of D3D stuff into its main window. Until now, I've started the rendering with a command on the main menu. My rendering loop is just a big loop, like so: void CMyDlg::RenderLoop() { while(true) { MSG Message; while (::PeekMessage(&Message, 0, 0, 0, PM_REMOVE)) { ::TranslateMessage(&Message); ::DispatchMessage(&Message); if (Message.message == WM_QUIT) { return; } } Render()...

Problem when opening a file by double-click
Just upgraded from Office 2003 to 2007. When I try to open an existing Word or Excel file by double-clicking the file, Word or Excel opens up but the file doesn't display. As soon as I click on the Button, the file then displays...but of course, the Button menu opens as well, forcing me to press Escape to get rid of it. Why doesn't the file just display? I don't seem to have the issue with PowerPoint, but only Word or Excel. It happens no matter which version of the app the file was saved in. I don't believe there's a setting for this kind of thing. A small...

Unknown/broken .doc file
Hello, I have several .doc files which fail to open in Word 2007. Can you help me identify what file type it is exactly and how to open or convert it? Here's an example file: http://rapidshare.com/files/389929742/DP_Kolousek_-_Olejnicek.doc.html This file looks like it might once have been a Word Document but it has been seriously messed up in some way (perhaps with an attempt to convert it to a different format, that rather looks like it may have failed). By opening it in NotePad, I managed to manually extract a page or so of text from it that looks as though it may be in...

Prompting for customer information
Does anyone know of a way to force the POS to prompt for an E-mail address at each transaction? I would like to start sending coupons and such using E-mail. Thanks in advance, Scott Scott, There is no such feature out of the box with RMS, however we have two applications that are very similar in nature that could be easily modified to suit your needs. If you are interested please drop me a line at 1-888-267-RITE or drop me a line at rsakry@rite.us. -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises (RITE) rsakry@rite.us http://www.rite.us 320-230...

I need a shortcut to make a excel file open to a specific sheet
I need to know how to modify an excel shortcut to make a file always open to a specific named "intro" sheet. You can use a macro: http://vbaexpress.com/kb/getarticle.php?kb_id=338 ************ Anne Troy VBA Project Manager www.OfficeArticles.com "EAHRENS" <EAHRENS@discussions.microsoft.com> wrote in message news:4CEF8135-5F57-400F-B5AF-9A11396110A8@microsoft.com... >I need to know how to modify an excel shortcut to make a file always open >to > a specific named "intro" sheet. Try doing it within the files workbook_open event in the ThisWork...

Console log File Trimming
I have a very large console log file (1.5 GB) that has grown to big to effeciently open up with Wordpad or Notepad to trim. Can I just rename the file to consoleold.log and let sbs recreate the file. Make sure everything runs fine for about a week and then delete the consoleold.log file to free up space on the C: drive? Hickinator Sure. -- Chris Puckett This posting is provided "AS IS" with no warranties, and confers no rights. "Hickinator" <Hickinator@discussions.microsoft.com> wrote in message news:91596417-D317-48AE-86E6-B5A605AF9891@m...

Backing up to get rid of Log Files...Exchange 2003
Hello, We have a backup solution that is not "Exchange Aware" As I recall from a long call to Support if I do a "Backup" from the computer it will get rid of those logs... Question, what do I need to select on the backup? Will I have to manually delete the files? Can I back up to a Network drive? Thanks, David Bocl "David Bock" <David@dvbock.com> said > Hello, We have a backup solution that is not "Exchange Aware" As I > recall from a long call to Support if I do a "Backup" from the computer > it will get rid of those logs....

Embedded Excel file gridlines printing
I have an embedded Excel file in word and when I go to print, it i printing with the gridlines. In excel I have the gridlines turned of and it is still not working. HELP! Thank -- tanyhar ----------------------------------------------------------------------- tanyhart's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3514 View this thread: http://www.excelforum.com/showthread.php?threadid=55105 ...

How can I embed a file as an unopened object in a worksheet or wo.
I want ot place Word, pdf, and other documents into a spreadsheet as objects that can be opened by clicking the icon. Have you tried Insert|object Goat Boy wrote: > > I want ot place Word, pdf, and other documents into a spreadsheet as objects > that can be opened by clicking the icon. -- Dave Peterson ...

Opening xls file opens 2 instances of Excel
When I double click on an xls or xlsx file, I get one empty excel window and then another opens up with the file open in it. Two instances of excel from one xls file... How can I fix this please? Thanks!@ ...

Money Help Files
I am having trouble setting up multiple accounts for a single bank. I found something to try in another thread using the "Ask Money" and then choosing "Set up services for two accounts with different passwords at one bank.' That would indeed be the solution I think, but my Help files are broken and all I get is the message Unable to load topic. Click here to try again." I tried reinstalling it, removing it completely and reinstall again but with no success. I have even tried reinstalling on a different machine and I still get the same problem. Any ideas? Anyw...

Security prompt also
I have Verizon ISP Have Windows Vista w/ sevice pack 2. Windows mail is asking for ID and password, so I tried, SORRY. Now won't work properly, can't receive or send mail. I am trying to get my info on Acct. cuz forgot it (password). Please bare with me as I am trying to fix this. I would like to cancel Windows Mail and use Windows Live instead. I did try to add the Hotmail address to WM but it wouldn't let me. This problem did happen all of a sudden. I've been using WM since '08 and never any issues til now (prompt). Thank you for your time and any suggestions. ...