[RESOLVED] Import From Excel to Access

[swoozie]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, WkSh, Path & SpNm, True, WkSh

I need to specify which sheets in a workbook need to be imported and it is not working. I am receieving an error saying object doenst exist. I have checked for spelling and syntax errors in the variables already, but it looks like the error is in the range section.

Thank you,

[RobDog888]

Try...

Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", "C:\Book1.xls", False, "Sheet2$"

[swoozie]

Try...

Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", "C:\Book1.xls", False, "Sheet2$"

I have attached the error. It works fine if you hand type the sheet name but using a variable it doenst work.

Thank you,


Swoozie

[RobDog888]

It was just an example, did you change the parameters to your needs? do you have a "C:\Book1.xls" file? Thats probably the issues. change to reflect your needs.

[swoozie]

Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, WkSh, Path & SpNm, True, WkSh

WKsh = the worksheet name and the name I want the table that is being imported into.

path = the location of the excel spreadsheet

spnm = the name of the spreadsheet

The worksheets have headings, so I set that to "True"

All of it works except the passing of the worksheet tab name. And that makes no sense to me.

[RobDog888]

You have your parameters wrong. See post #2 for the example.

You need to specify the Access Table name that your importing the spreadsheet into.

[swoozie]

I did, the table name is the same as the worksheet name. As I previously said, if I take out the last

Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, WkSh, Path & SpNm, True, WkSh

- Dosent Work
And switch it with this

Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, WkSh, Path & SpNm, True, "Facility" 'WkSh

It does work. But I can not have a hard keyed valule I have over 20 spreadsheets I need to import.

[RobDog888]

Try replacing the varible value with...

"[Facility$]"

[swoozie]

I have tried to replace it with

"Facility"
"[Facility]"
"[Facility$]"
Facility
"[Facility]$"

But none of that really matters because I need to use a variable and by all accounts this should work. I can not find a reason anywhere that this would not work.

Any other ideas on how to import other than the transferspreadsheet method?

[RobDog888]

They are not hidden or protected sheets?

Weird error.

You could add a reference to Excel and use the excel object model to manually code an import procedure.

Or use ADO to query the spreadsheet and perform a INSERT INTO action query statement.

[swoozie]

They are not hidden or protected sheets?

Weird error.

You could add a reference to Excel and use the excel object model to manually code an import procedure.

Or use ADO to query the spreadsheet and perform a INSERT INTO action query statement.

no the answer was really evident "!"

Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, WkSh, Path & SpNm, True, WkSh & "!"

[RobDog888]

Well I have only seen the exclamation point be used for identifying a procedure after a workbook file like this - C:\Test.xls!ImportMe
Usually the $ sign is for identifying sheets but as long as it works for you.

What version are you running anyways?

[swoozie]

I actually found the answer somewhere in Microsoft Help (cant find it again) I was going to copy the reference for you but I can not locate it.

It was in a very obscure place and had nothing to do with what I looked up, it was just shown as an example for something so I thought I would try it.

We are using MS Office 2002 on Window Prof 2000

[RobDog888]

Ok, thanks. I'll keep my eyes peeled for it whenever I am in the help file.