[RESOLVED] Import From Excel to Access

VB Code:

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,

Try...

VB Code:

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

Quote:

---

Originally Posted by RobDog888

Try...

VB Code:

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

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.

VB Code:

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.

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.

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

VB Code:

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

- Dosent Work
And switch it with this

VB Code:

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.

Try replacing the varible value with...

"[Facility$]"

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?

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.

Quote:

---

Originally Posted by 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.

---

no the answer was really evident "!"

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

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?

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

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