Read Excel Worksheet Names 1

[mmogul]

I have a program that is processing multiple worksheets in an excel spreadsheet. It appears that an excel user can use the "#" sign in the worksheet name. But when access tries to read it,

The microsoft database jet engine could not read the object ... it then gives the object name leaving out the # sign.

Does anyone have any ideas to get around this issue, other than telling the user not to use the # sign in the worksheet name?

The code is essentially this:

strFileName = filePath & fileName

Set objWorkbook = objExcel.Workbooks.Open(strFileName)
Set colWorksheets = objWorkbook.Worksheets

For Each objWorksheet In colWorksheets....

DoCmd.TransferSpreadsheet acImport, 8, "tblTempMfgProductData", strFileName, False, strWorkSheetName


Thanks

 

[Remou]

How about a work-around?

    For Each objWorksheet In objWorkbook.Worksheets
        If InStr(objWorksheet.Name, "#") > 0 Then
            a = objWorksheet.Name
            objWorksheet.Name = Replace(a, "#", "x")
        End If
        
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, objWorksheet.Name, strFileName, False, _
             objWorksheet.Name & "$"
            
        If a <> "" Then
           objWorksheet.Name = a
           a = ""
        End If
   
    Next

 

[mmogul]

Sounds good. I'll give it a try. I had thought about changing the name -- but I thought if I changed the name, the program wouldn't find the spreadsheet.

Thanks.

 

[mmogul]

Why do you append the "$" to the objWorksheet.Name?

Thanks.

 

[Remou]

Because if the dollar sign is not included, the following error will be encountered:

The Microsoft Jet database engine could not find the object 'Sheet1' ...

See also:

http://www.tek-tips.com/viewthread.cfm?qid=1483277&page=10