VBA Code - Open Folder, count Excel files - open one at a time for automation

Rx_ · Sep 10, 2012

Need to locate code that will open a specific network folder, count the Excel files, open them one at a time.
This is what I need.

While open, some VBA automation will harvest the data from Excel.
I can handle this part, but would be happy to see any examples.

My thoughts are to have code that can:
1 Open a folder with dozens of Excel files (all formatted from the same template)
2. find an Excel file - open it, harvest the data into a recordset
3. Insert a "x" into the unused cell A1 to indicate it was harvested
4. Close the Excel file - move the file to a sub-folder named "harvested"
5. Repeate Step 2

Rx_ · Sep 10, 2012

There is probably more than one way to accomplish this. Other solutions are encouraged!

Here is some code, and I will keep looking at other possible solutions:

Code:

Public Sub OpenEachExcelInFolder()
      ' Note Must have object References set
          Dim strFile As String
          Dim strDirectory As String
          Dim CurrentWorkbookName As String
          Dim SheetCount As Integer
          Dim CurrentSheetNumber As Integer
          Dim FileCount As Integer
10        FileCount = 0
20        strDirectory = "C:\MyFolder\MySubFolder\" 'List directory with some excel files, slash required
 
30        strFile = Dir(strDirectory & "*.xls*")
40        Do Until strFile = ""
50            FileCount = FileCount + 1
60            Workbooks.Open strDirectory & strFile
70            Debug.Print "ActiveWorkbook Name is : " & ActiveWorkbook.Name
80            CurrentWorkbookName = ActiveWorkbook.Name
90            ActiveWorkbook.Close True
100           strFile = Dir()
110       Loop
120       Debug.Print "The number of Excel files in this directory are : " & FileCount
End Sub

My Allen Browne (favorites) also had this code that could be useful:
http://allenbrowne.com/ser-59alt.html

AccessMSSQL · Sep 10, 2012

I just posted some similar code in this thread, please see if this helps you!

http://www.access-programmers.co.uk/forums/showthread.php?t=233007

apr pillai · Sep 10, 2012

The following link will give you code, which creates the list of files of required type (.xls, .doc, .txt or whatever) from specified folder in a list box as hyperlinks.

External Files' List in Hyperlinks

Depending on the structure of the data in excel, VBA Code must be developed to transfer the contents to access tables. If all the excel tables have uniform worksheet name, structure and Range Address then better idea is to link excel file to the database, create a common append query and run it to transfer the data into the target table, one by one.

Check the following link for few programs:

Rx_ · Sep 11, 2012

Got this done after a busy day.
Just a sampler that works - an attached DB.

Thanks for the help. You will see some of your code in here.
There was another member asking me about this on a PM.
Posted the entire Access db because it is small.

vbaInet · Sep 11, 2012

Rx_ said:
3. Insert a "x" into the unused cell A1 to indicate it was harvested
4. Close the Excel file - move the file to a sub-folder named "harvested"

I won't perform step 3 if I were you. Opening the Excel file alone is time consuming. The fact that the file was successfully moved to the harvested folder alone would indicate that it was harvested. It will only make sense to do step 3 if your harvesting process requires the file to be open, but if it doesn't I would just move the file to the Harvested folder and if you wish you can add a special prefix to the file name.

Rx_ · Sep 13, 2012

http://allenbrowne.com/ser-59.html

My requirements changed (wow, that is a first time ever! LOL). There are dozens of folders each with many dozens of Excel workbooks. So, I won't move anything as suggested.

Did need to add recursive code to start at a root folder and search each sub-folder.

Of course, I should have started with AllenBrowne's website!
Here is an all-in-one routine that can search either a single folder or all of the sub folders.

Anyone considering searching files in a folder from Access should look at this site first.
Allen provides some samples to call from the Immediate Window (Ctrl+G)
Here is an additional combination that also works:
'To include files in subdirectories as well:
'Call ListFiles("C:\MyFolder\MySubFolder\","*.xls*" , True)
(The True argument instructs the code to look into subfolders)

VBA Code - Open Folder, count Excel files - open one at a time for automation

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

AccessMSSQL

Seasoned Programmer-ette

apr pillai

AWF VIP

Rx_

Nothing In Moderation

Attachments

vbaInet

AWF VIP

Rx_

Nothing In Moderation

Similar threads

Users who are viewing this thread