0

I give up. I've searched, I've hunted through SO, I've seen any number of deeply confusing three-page code segments to go through, and frankly while I'm sure they're all good solutions the code is just beyond me.

I have a bunch of data sheets, identical in structure but with lots of different data in them. They're all being stored in the same directory, and I'm trying to make a macro to cycle through them.

This was my original attempt (where RunImport is the function that copies over all those data fields). It doesn't work.

Private Sub But_BatchRun_Click()
Dim sImpFolder As String, wb As Workbook

sImpFolder = GetFolder

For Each wb In sImpFolder
    call runimport (wb)
Next wb

End Sub

Is there any easy and short way to do this that doesn't involve eight pages of code?

Community
  • 1
  • 1
Andrew Perry
  • 743
  • 2
  • 11
  • 32
  • 1
    Why don't you just use this solution and probe each file for the `.xls` (or any other relevante) extension: http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba – Ralph Aug 15 '16 at 12:41
  • what Ralph wrote, its less than 10 lines of code, also don't forget to fix your type error to `Call RunImport (wb)` – Shai Rado Aug 15 '16 at 12:46
  • Have you checked Microsoft's VBA material? They do it in 5 lines of code. https://support.microsoft.com/en-us/kb/139724 – Ambie Aug 15 '16 at 12:51
  • Shai, well spotted - cheers. Ambie, Ralph - thanks, I'll take a look at those links. – Andrew Perry Aug 16 '16 at 06:51
  • @Ambie, your suggestion is working nicely. Combined with Thomas Inzina's note to open the workbook it all seems to be a go. Would you prefer me to post my finished code with credit to you both or do you want to post it as an answer yourselves? – Andrew Perry Aug 16 '16 at 08:34
  • 1
    Neither, just accept the answer from @ThomasInzina. Are you also aware that there are ways of reading closed workbooks, so you don't have to open each one? http://stackoverflow.com/questions/38783448/reference-a-cell-if-the-sheet-contains-a-certain-string-using-vba/38788689#38788689 – Ambie Aug 16 '16 at 09:28
  • Done, cheers. Will look at that link too - thanks for the tip. – Andrew Perry Aug 18 '16 at 08:32

1 Answers1

1

Whether you use a FileSystemObject or a Dir search; you'll still need to open the file.

Sub ProcessXLFiles()
    Const FolderPath = "C:\stackoverfow\Sample Data File\*.xl*"
    Dim FileName As String
    Dim wb As Workbook
    FileName = Dir(FolderPath)

    Do While Len(FileName) > 0
        Set wb = Workbooks.Open(FileName)
        callrunimport FileName
        FileName = Dir
    Loop

End Sub