DoCmd.TransferSpreadsheet from Excel to Access using sheet codename - NOT tabname

[shanesuzanne]

Table name is employees
File name is varFile (used with a file picker)

This works:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "employees", varFile, False, "January!A4:AD10000"


The problem is the tab names change each month. For the life of me, I can't get the sheet codename to work. This is sheet(1). I've googled for HOURS. Can anyone point me in the right direction?

The code is in Access 2010, importing from Excel 2013. Thanks!

 

[Norie]

Where are you running this code from?

 

[LockeGarmin]

From what you are describing it sounds like the spreadsheet you are wanting to transfer from is the first (or only) worksheet in your target workbook. If that's the case then you should be able to successfully transfer without including the name of the worksheet like below:

[COLOR=#333333]DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "employees", varFile, False, "A4:AD10000"[/COLOR]

Otherwise, if you are actually needing to use a Code Name your only option is to do the following:

1. Create a new excel instance from your code in Access.
2. Open the workbook with your Excel application.
3. Loop through your worksheets until you find the one with the code name you are searching for.
4. Return the worksheet (tab) name from the worksheet object you have.
5. Close the workbook and instance of Excel.
6. Use the worksheet (tab) name in your DoCmd procedure.

 

[shanesuzanne]

Hi Norie, I'm running this code from Access.

 

[xenou]

If you can wing it a good solution is to not have changing tab names. The current period data could be on a tab named "CurrentPeriod".

This is of course a losing battle - too many people don't understand data.

 

[shanesuzanne]

Unfortunately, I don't have control over the tab names, and they change every month. I've made a simplified version of my code if someone would be able to assist - I still can't get this to work. I've tried setting up Excel instances to no avail.

A file picker opens, you select several files, then it imports certain tabs based on the file names.

Sub mrexceltester()
'selects files, then import into temp tables
 
 Dim fDialog As Office.FileDialog
 Dim varFile As Variant
 Dim fso As New FileSystemObject
 Dim fileName As String
 
 On Error GoTo ErrorHandler
 
 Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 With fDialog
  .AllowMultiSelect = True
  .Title = "Choose FILES to Import"
  .Filters.Clear
  
  If .Show = True Then
   For Each varFile In .SelectedItems
    fileName = fso.GetFileName(varFile)
    
    If fileName Like "*employee*" Then
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmp_hireUS", varFile, False, "june us hires!A3:K8000"    'Sheet(1)
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmp_termUS", varFile, False, "june us terms!A3:K8000"    'Sheet(2)
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmp_hireINT", varFile, False, "june intl hires!A3:K8000" 'Sheet(3)
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmp_termINT", varFile, False, "june intl terms!A3:K8000" 'Sheet(4)
     
    ElseIf fileName Like "*expenses*" Then
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmp_exp", varFile, False, "June!A4:AD10"     'Sheet(1)
     
    ElseIf fileName Like "*credit*" Then
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmp_cre", varFile, False, "June17activityUS!A6:K100"   'Sheet(1)
     
    End If
   Next
  Else
   MsgBox "You have cancelled the import."
  End If
  
 End With
 
 Exit Sub
 
 ErrorHandler:
 MsgBox "There was an Error:  " & Error(Err) & vbCrLf & vbCrLf & "The following file will NOT be imported:" & vbCrLf & fileName & vbCrLf & vbCrLf & "Click OK to continue processing the other files. "
 Resume Next
 
End Sub

 

[Norie]

If this code is being run from Access then there's no easy way (to be honest I'm not sure there is a way) to work with the codename in an Excel workbook.

Have you considered turning things round and having code in Excel that imports from Access?

 

[LockeGarmin]

Otherwise, if you are actually needing to use a Code Name your only option is to do the following:

1. Create a new excel instance from your code in Access.
2. Open the workbook with your Excel application.
3. Loop through your worksheets until you find the one with the code name you are searching for.
4. Return the worksheet (tab) name from the worksheet object you have.
5. Close the workbook and instance of Excel.
6. Use the worksheet (tab) name in your DoCmd procedure.

Here is numbers 1 - 5 in my description in code:

Function GetWorksheetNameByCodeName(WorkbookPath As String, CodeName As String) As String

Dim xlApp As Object 'Excel.Application
Dim WB As Object 'Excel.Workbook
Dim WS As Object 'Excel.Worksheet

Dim Success As Boolean

  Set xlApp = CreateObject("Excel.Application")
  
  xlApp.ScreenUpdating = False
  xlApp.DisplayAlerts = False
  xlApp.InterActive = False
  
  Set WB = xlApp.Workbooks.Open(WorkbookPath, ReadOnly:=True)
  
  For Each WS In WB.Worksheets
    If VBA.StrComp(WS.CodeName, CodeName, vbTextCompare) = 0 Then
      GetWorksheetNameByCodeName = WS.Name
      Success = True
      Exit For
    End If
  Next WS
  
  xlApp.Quit
  
  'Subscript out of range
  If Not Success Then Call Err.Raise(9)

End Function

And number 6:

[COLOR=#333333]DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "employees", varFile, False, [/COLOR]GetWorksheetNameByCodeName([COLOR=#333333]varFile[/COLOR], "[COLOR=#ff0000]ExcelWorksheetCodeNameHere[/COLOR]") & [COLOR=#333333]"!A4:AD10000"[/COLOR]

 

[james_lankford]

yeah, but he can't do that

he left out an important piece of information in the first post
but mentioned it in post #6

some workbooks have multiple sheets with data
and he wants to import each sheet into a different table

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmp_hireUS", varFile, False, "june us hires!A3:K8000" 'Sheet(1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmp_termUS", varFile, False, "june us terms!A3:K8000" 'Sheet(2)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmp_hireINT", varFile, False, "june intl hires!A3:K8000" 'Sheet(3)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmp_termINT", varFile, False, "june intl terms!A3:K8000" 'Sheet(4)

LOL, I don't see how he's going to do that
how can he possibly know which sheet goes to which table ?
how can he even be be sure that Sheet(1) contains us hires and not intl hires ?

 

[LockeGarmin]

@james_lankford I completely understand what you're saying but you're making too many assumptions about the OP's Excel file. Excel assigns code names to worksheets automatically and predictably so while there would be circumstances where you can't be sure which code name will go with worksheet, it is definitely not outside the realm of possibility (for instance, if it is generated by another application in the same way every month, or if it is based off of an Excel Template). You may as well add the question "how he can even be sure that the range is "A5:K8000" instead of "A3:8000"" to your list of questions. At this point I'm assuming the OP knows which code name goes with which sheet based on the request, so the function I wrote will allow them to capitalize on that.