[vba] How to import sheets regardless the name (code inside need a tiny bit of help)

[Vexorg]

Hello,

I want to adapt my code below for a new function I need. This code imports sheets with the name 'Result1, Result2, Result3, etc.' for ranges 1 to 11.

Public Sub Import_Core()
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    For x = 1 To 11
        strSheet = "Result " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "core", _
            "\\msad\root\NA\NY\LIB\fid\FIDSTP\Centralized Reference Data\Clear Exports\core.xls", _
            True, _
            strSheet
    Next x
MsgBox "Core Complete."
Exit Sub
Handler:
MsgBox Err.Description
End Sub</pre>

I now want to import sheets '3 To ShtCount' , each sheet name will be different.


My issue is that I want this to import from all files in a directory, and each sheet may not always be in each file.

Should I just find each of the sheet names that might occur and hard code them in?

 

[Damon Ostrander]

Hi Vexorg,

If I understand your problem correctly, this should do it.

Public Sub Import_Core()
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    Workbooks.Open "\\msad\root\NA\NY\LIB\fid\FIDSTP\Centralized Reference Data\Clear Exports\core.xls"
    For x = 3 To ActiveWorkbook.Worksheets.Count
        strSheet = Worksheets(x).Name
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "core", _
            "core.xls", _
            True, _
            strSheet
    Next x
MsgBox "Core Complete."
Exit Sub
Handler:
MsgBox Err.Description
End Sub

I assumed you already have code to loop through all the workbooks in the folder, but if not feel free to follow up. Also, I figured the entire path was not required of the last argument of TransferSpreadsheet since the workbook would be already open, but since I do not have the code for this procedure I can't be sure. If it can't find the workbook, try putting the entire path back in.

Damon

 

[Vexorg]

Hi Damon,

Thanks for the code but there are 2 things:

1. Ive updated the code for my specific file, but is there anyway for me to avoid hard coding in the file names so i can import the entire directory?

2. Also when I tried to run this on the file i hard coded in I am receiving an error that highlights

strSheet = Worksheets(x).Name

And states 'Compiles Error: Sub of Function not defined'


This is the updated code:

Public Sub Import_Core()
Dim x As Integer
Dim strSheet As String
    On Error GoTo Handler:
    Workbooks.Open "\\msad\root\NA\NY\LIB\fid\FIDSTP\Confirm Renov\Catch All\FCO DB - all\2-2011.xlsx"
    For x = 3 To ActiveWorkbook.Worksheets.Count
        strSheet = Worksheets(x).Name
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "core", _
            "2-2011.xlsx", _
            True, _
            strSheet
    Next x
MsgBox "Core Complete."
Exit Sub
Handler:
MsgBox Err.Description
End Sub

 

[Vexorg]

^

 

[Vexorg]

bump