Apostrophe in the Excel worksheet name

[mdo]

Hi,

I'm importing from Excel to Access using the DoCmd.Transferspreadsheet command. It works except when there is an apostrophe in the Excel worksheet name. Is there a code to automatically remove the apostrophe?

Please help.

Thanks.

 

[crosmill]

Try useing double quotes to define your file name.

DoCmd.TransferSpreadsheet "MyFile'Name.xls"

 

[mdo]

Apostrophe In The Excel Worksheet name

Hi,

I already have double quote in between the file name. My syntax is below:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Toll Free Inventory", "c:\Tiger\Marita\Sears\Test.xls", True, All, useoa

Please help.

Thanks.

 

[crosmill]

There's no apostrophie in that file name. Can you post the line thats failing?

 

[mdo]

Apostrophe In Excel Worksheet Name

This is the line that's failing:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Toll Free Inventory", "c:\Tiger\Marita\Sears\Test.xls", True, All, useoa

 

[crosmill]

But there's no apostrophie in that file name, so how is it the apostrophie thats making it fail?

 

[mdo]

Apostrophe in Excel worksheet name

It is failing because the name of the worksheet of the excel file that I'm trying to import has an apostrophe in the name. The name of the worksheet is "TFN's 3Q" instead of the usual "Sheet1".

Please help.

 

[crosmill]

My appologies I thought it was the file name

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TFN's 3Q", "c:\Tiger\Marita\Sears\Test.xls", True, All, useoa


I've had trouble with specifing worksheet names though, I've found that it doesn't really matter what worksheet name you specify it will only import the first sheet in the spreadsheet

 

[mdo]

Apostrophe in Excel worksheet name

It looks like you are replacing the name of the table to "TFN's 3Q". The actual name of the table is "Toll Free Inventory". "TFN's 3Q" is the name of the Excel worksheet.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TFN's 3Q", "c:\Tiger\Marita\Sears\Test.xls", True, All, useoa

I know it always import the first worksheet from Excel. It is doing that, but it doesn't like the name "TFN's 3Q" because of the apostrophe. It works ok if I manually remove the apostrophe.

Please help. Any idea?

 

[crosmill]

Ahh ok I didn't check the spec.

You've written "All" but in the spec it tells you to leave it blank if you want to import the whole spreadsheet.

 

[crosmill]

found this as well

Range The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25 (note that the A1..E25 syntax does not work in Access 97 or later). If you are importing from or linking to an Excel version 5.0 or later spreadsheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7.