Access VBA Dynamic Excel Import

Question

I'm trying to dynamically import an Excel Spreadsheet into Access. The problem is, i need to import a specific sheet (this part sorted out), but I'm having problems importing a sheet whose name changes month to month.

Code example:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "MonthSales", FileName, 1, "Sales_Month!"

On the "Sales_Month!" is where i need it to be dynamic. I can import it directly if the sheet has always the same name. So my question is, can i import it with some sort of wildcard?

For example: "Sales_*!"

Note: This Excel Workbook has several worksheets.

In Excel VBA, you could loop through Worksheets and check if the current sheet's name is Like "Sales_*!". Something equivalent is presumably doable in Access VBA. — Jean-François Corbett
– Jean-François Corbett, Commented Oct 19, 2018 at 9:04
I totally agree with @Jean-FrançoisCorbett and I upvoted his/her comment. Loop trough all sheets checking all names until you find the one you want and then activate it. Then, once activated, you could use DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "MonthSales", FileName, 1, ActiveSheet.Name — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Oct 19, 2018 at 9:20

kiks73 · Accepted Answer · 2018-10-19 09:27:03Z

2

This should be simple a solution, looking for Excel Sheet names in a for cycle with Left operator:

Dim xls As New Excel.Application, sht As String, Wkb As Workbook, Wksh As Worksheet
Set Wkb = xls.Workbooks.Open(FileName)

For Each Wksh In Wkb.Worksheets
  if left(Wksh.Name, 6) = "Sales_" then
      DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "MonthSales", FileName, 1, Wksh.Name
      Exit For
  end if
Next Wksh

Wkb.Close
xls.Quit
Set Wkb = Nothing
Set xls = Nothing
Exit Sub

answered Oct 19, 2018 at 9:27

kiks73

3,8383 gold badges30 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Foxfire And Burns And Burns Over a year ago

For this code to work, I think user must need to add a reference to Microsoft Excel Objects Library, because it is executing it from Access. Also ¿no binding? ¿is not necessary first to do a Set xls = New Excel.Application before doing Set Wkb = xls.Workbooks.Open(FileName)?

Collectives™ on Stack Overflow

Access VBA Dynamic Excel Import

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related