Import Hidden Excel Sheet

[stacem]

I have a standard excel workbook which contains 2 sheets

One of these sheets "Upload" is hidden and contains only the data I wish to import in to my db via TransferSpreadsheet.

My code reads:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, _
"tblBulkRenewalData", "H:\BulkUpload.xls", 0, "upload!"

Problem:
When the vb cmd runs it cannot locate the hidden sheet and causes an error.

Question: How I can I direct the transfer command to the hidden sheet?

All help welcomed.
Thanks

 

[lewds]

Hi, is "upload" the name of the hidden sheet or a defined name for the range on that sheet.

Your code will only work if "upload" is a named range.

 

[rbowes]

try this:


Private Sub UploadData()
Dim wks As Excel.Worksheet
Dim wkbk As Excel.Workbook

'here you set get the workbook and set the worksheet to the hidden sheet
Set wkbk = GetObject("H:\BulkUpload.xls")
Set wks = wkbk.Worksheets("upload")
'Activate the sheet and make it visible
wks.Activate
wks.Visible = xlSheetVisible

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblBulkRenewalData", _
"H:\BulkUpload.xls", 0, "upload"

'Make the sheet invisible again
wks.Visible = xlSheetHidden
Set wks = Nothing
Set wkbk = Nothing
End Sub

 

[stacem]

Lewds
The sheet is a hidden sheet and not a named range, however, I will now name the range in the hidden sheet to see if your solution works. Thanks

rbowes
Your solution takes the approach to resolve this from within the Access code module and I will also give this a try as it might be more robust.

Thanks to you both, will post results later.