Defining Excel Spreadsheet for import - not supported?

[Armitage2k]

Hi!

I am trying to import a specific excel spreadsheet which is to be defined in an inputbox prior import. however, when the code should select the prior defined sheet, it returns the error message that this action would not be support. Surprisingly, the code used to work before...
I first thought it might has to do with the references or something, but I could not find any clues from there.

Sub GetDataFromClosedWorkbook()
Dim xlApplication As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim db As Database, rec As Recordset
Dim Filename As Object
Dim Selection, Category, Finished As String


Set db = CurrentDb
Set Filename = Application.FileDialog(msoFileDialogFilePicker)
    With Filename
    .InitialView = msoFileDialogViewDetails:
    .InitialFileName = "C:\":
    .Filters.Clear:
    .Filters.Add "Pick .xls File", "*.xls", 1:
    .ButtonName = "Import file":
    .Title = "Search for Database_Import.xls file"

    ' If no file is selected, close the sub, else keep going
        If .Show = -1 Then
            Selection = .SelectedItems(1)
        Else:
            Exit Sub
        End If
    End With
    
Category = InputBox("Which worksheet do you want to import? - VIP - PCP - LSG - OCT - IHG", "Select Entries", "VIP")

Set xlApplication = CreateObject("Excel.Application")
Set xlWorkbook = xlApplication.Workbooks.Open(Selection, True, True)
[B][COLOR="Red"]Set xlWorksheet = xlApplication.Workbook.Worksheets(Category) [/COLOR][/B]' define which worksheet shall be imported
Set rec = db.OpenRecordset(Category)

    ' open the source workbook, read only
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, Category, Selection, True
    
    ' close the source workbook without saving any changes
    xlWorkbook.Close False
    
    Finished = MsgBox("Import of " & Category & " successfully finished", vbOKOnly, "Finished")
    
    Set xlWorkbook = Nothing ' free memory
    rec.Close
End Sub

Would appreciate if someone could point me into the right direction.
thanks a lot!

A2k

 

[ajetrumpet]

there are some ms kb articles on this that reference ISAM. i personally don't understand that kind of stuff, but it's worth a google for ya

 

[chergh]

Try:

Set xlWorksheet = xlWorkbook.Worksheets(Category)

 

[Armitage2k]

that was it. I dont really understand why the error was caused, but still thanks.
anyway, after the import is done, I get an error table, stating that the object could not be saved, the data however is complete... dont know what access wants from me :?


just one more thing, how can i add a variable into doCmd order?
I have to clear the table, which I define through the Category inputbox, before I import the data.

    DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE Category.* FROM Category;"
    DoCmd.SetWarnings True

However, the command does not accept variables as replacement for the table name... any ideas?

thanks,
A2k

 

[chergh]

DoCmd.RunSQL "DELETE " & Category & ".* FROM " & Category & ";"

 

[Armitage2k]

Did I mention that I just fell in love with you

thanks to all of you, that helped me to finish a rather tiring project!
cheers!

A2k