using TransferSpreadsheet to specify a sheet

[gecko_1]

Hi,
I have looked around but still I am unable to find an answer.
How do I use doCmd.TransferSpreadsheet to import an excel sheet to access.

Here is what doesn't work:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "DrawingSheet", fileName, True, "DrawingSheet"
'fileName is a variable with the full path and filename of the excel workbook

Run-time error '3011'; The MS Jet dB engine could not find the object 'DrawingSheet'. Make sure the object exists and that you spell its name and path name correctly.

If I make "DrawingSheet" the first sheet and remove it from the statment it works, But I have to import 7 sheets to the database into different tables.

How do it do it??

 

[david.brent]

Hi there.
I think you have to put a '!' at the end of the sheet name eg

Range = "DrawingSheet!" in your Docmd.TransferSpreadsheet statement

Hope this works for you.

 

[modest]

If not, you'll have to open the sheet first.. and then transfer

    Dim App As Object                                            'Excel interface
    Dim ws As Object                                             'Your worksheet
    Dim File As Object                                           'Your file
    Dim rs As Recordset                                          'Table created from running your query

    Set App = CreateObject("Excel.Application")                  'This opens excel application windows
    Set File = App.Workbooks.Open(fileName)                      'This opens your particular excel file (or workbook)
    Set ws = File.Worksheets("DrawingSheet")                     'This opens your sheet that you want to write to
    ws.Activate
   
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Drawing Sheet", fileName, True
    
    App.Workbooks.Close                                          'This closes the workbook and asks you to save your excel sheet

...I think this should work, but I'm not completely sure. Remember that the first line of text in your spreadsheet sets the the variable of the table if the table is not yet created. If let's say you have a date in the first row of a given column and the next row for that column has text... Access will give you a type mismatch error or the transfer will stop at that row.

 

[gecko_1]

thats for that. the ! solved my problem.

I think you have to put a '!' at the end of the sheet name eg
Range = "DrawingSheet!" in your Docmd.TransferSpreadsheet statement

Now I only have to deal with key violations .

Cheers

 

[modest]

so you're done! ... have fun!

 

[mattkorguk]

Holy thread revival!! (searching does work)
Just been scratching my head on the old '!' issue, worked a treat for me too.

 

[wholesalmon]

Is it possible to have it prompt me to select which tab I want imported rather than assigning a specific tab name?