Excel Import and "Object required" error

[DJ_Hardin]

My apologies in advance for inquiring about something that I see many similar post on. I am trying to import data from an Excel workbook into a single Access table using a mix of code from several threads. My Excel workbook has many sheets, but thankfully, I need the same range from each sheet (c2:Q17281), and all to be put in one Access table. This is my first project using VBA with Access (I've done a little with Excel VBA before).

I am getting an "Object required" error, regardless of whether or not I use "set", and whether or not I use the file extension (.xls). My code is below. I'd greatly appreciate any help.

Public Sub ProcessExcelFile1()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim mypath
mypath = "G:\DataFolder\"
Set objWkb = mypath & "DataFile1"
With objWkb
For Each objSht In .Worksheets
DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, "c2:q17281"
Next
End With
End Sub

 

[MarkK]

Hey there, welcome to the forum.

You are trying to assign a string to an object variable here...

Set objWkb = mypath & "DataFile1"

What you need to do is create an instance of Excel which we do here in red with New keyword. And then to get a reference to a workbook object we run the Open method of the Workbooks collection, also in red, passing in the filename. Then, in blue you are going to have a problem because you are trying to use a string operator on an object of type Excel.Workbook.

Public Sub ProcessExcelFile1()
  Dim objXL As [COLOR="Red"]New[/COLOR] Excel.Application
  Dim objWkb As Excel.Workbook
  Dim objSht As Excel.Worksheet
  Dim mypath As String

  mypath = "G:\DataFolder\"
[COLOR="Red"]  Set objWkb = Workbooks.Open(mypath & "DataFile1")[/COLOR]
  With objWkb
    For Each objSht In .Worksheets
      DoCmd.TransferSpreadsheet acImport, , "MyNewTable", [COLOR="Blue"]mypath & objWkb[/COLOR], False, "c2:q17281"
    Next
  End With
End Sub

I recommend that you look into using the Object Browser, which gives you an easy way to find out what properties and methods are exposed by various objects that your database has referenced.

1) Open the Object Browser,
2) in the dropdown that says <All Libraries> select Excel
3) scroll down in the Classes list until you find Workbooks, and click it
4) and in the Members of 'Workbooks' list you see it's properties and methods, mostly named so it's obvious what they do

hth

 

[vbaInet]

The range you're trying to import from Excel is the same but are the column headers on each tab the same? Are the data types of the columns consistent across all tabs?

 

[DJ_Hardin]

Thank you, with your help I was able to get the code to run (I dealt with your blue text by inserting the full path and filename). I also checked out the Object Browser -- definately a good tip for me. BUT, the code doesn't do what I intended. Instead of importing each of the 30 sheets into the Access table, it seemed to repeat Sheet1 many times over.

Any ideas?

 

[DJ_Hardin]

Yes, the column headers are the same (although I'm not inporting the headers). The data types of the columns are also the same across all sheets.

The range you're trying to import from Excel is the same but are the column headers on each tab the same? Are the data types of the columns consistent across all tabs?

 

[cyb3rwolf]

The sheet reference i think is supposed be in the range argument. So, it would be like: DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, "Sheet1!c2:q17281" to import sheet 1. I'm not sure if this would work 100%, i'm not used to working with xcel objects, but if it follows the same trend as everything else, try this:

DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, objSht.Name & "!c2:q17281"

 

[cyb3rwolf]

Sorry, let me do that again with my proposed change highlighted in red:

DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, objSht.Name & "!c2:q17281"

 

[DJ_Hardin]

Well, I was able to import the appropriate range without the sheet name -- but it only imported the first sheet, and I want to import all 30 sheets in the Excel file into one Access table . . .

The sheet reference i think is supposed be in the range argument. So, it would be like: DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, "Sheet1!c2:q17281" to import sheet 1. I'm not sure if this would work 100%, i'm not used to working with xcel objects, but if it follows the same trend as everything else, try this:

DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, objSht.Name & "!c2:q17281"

 

[cyb3rwolf]

Do you have the section in your code like this? If so, it should be repeating the procedure with each sheet in your workbook.

For Each objSht In .Worksheets
DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, "c2:q17281"
Next objSht

 

[cyb3rwolf]

Er... nevermind that... I posted from your code without the added corrections. Like this, exactly:

For Each objSht In .Worksheets
DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, objSht.Name & "!c2:q17281"
Next ObjSht

 

[cyb3rwolf]

What that should do is repeat the procedure with each sheet in your workbook. And, each time it does, objSht.Name should (i think) return the sheet name, so each time it runs the procedure with each sheet, the sheet name will be included in the range it's trying to import.

Editing post with Full Code, with all changes highlighted in red from your original:

Public Sub ProcessExcelFile1()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim mypath
mypath = "G:\DataFolder\"
[COLOR=red]Set objWkb = mypath & "DataFile1"[/COLOR]
With objWkb
For Each objSht In .Worksheets
DoCmd.TransferSpreadsheet acImport, , "MyNewTable", [COLOR=red]mypath & objWkb[/COLOR], False, [COLOR=red]objSht.Name & "![/COLOR]c2:q17281"
Next [COLOR=red]objSht[/COLOR]
End With
End Sub

 

[DJ_Hardin]

I tried this, but it made no difference -- Sheet1 is copied over many times. I think it is looping through for as many sheets as there are, but the import range is staying as Sheet1!c2:Q17281 even though I have never specified Sheet1.

Do you have the section in your code like this? If so, it should be repeating the procedure with each sheet in your workbook.

For Each objSht In .Worksheets
DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, "c2:q17281"
Next objSht

 

[cyb3rwolf]

Yep, i made a mistake in that particular post; i didn't add "objSht.Name" to the range argument. try that full code i posted in my last reply. Basically, the sheet name has to be referenced in the range, or else it's going to look at only the first sheet in the workbook... Try the below full code, exactly like i have it. Notice what i have in red. Each time it goes through the loop, the sheet name is included in the range.

Public Sub ProcessExcelFile1()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim mypath
mypath = "G:\DataFolder\"
Set objWkb = mypath & "DataFile1"
With objWkb
For Each objSht In .Worksheets
DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, [COLOR=red]objSht.Name & "![/COLOR]c2:q17281"
Next objSht
End With
End Sub

 

[DJ_Hardin]

I tried this, and got a run time error "expression entered is wrong for one of the arguments".

Er... nevermind that... I posted from your code without the added corrections. Like this, exactly:

For Each objSht In .Worksheets
DoCmd.TransferSpreadsheet acImport, , "MyNewTable", mypath & objWkb, False, objSht.Name & "!c2:q17281"
Next ObjSht

 

[cyb3rwolf]

Hmm... I'm assuming the .Name property is the same as anything else i've worked with, should return the name of the worksheet. Let me try it and i'll get back to you shortly.

 

[DJ_Hardin]

Based on what lagbolt warned, a "string" doesn't work for the path name, so the blue text (mypath & objWkb) was replaced with the full path and file name (G:\DataFolder\DataFile1). Before doing this replacement, I confirmed that lagbolt was correct in predicting that the blue text would cause the next error.

Perhaps a string can't be used to describe a range, just as it didn't work in describing the path and filename . . .

Hmm... I'm assuming the .Name property is the same as anything else i've worked with, should return the name of the worksheet. Let me try it and i'll get back to you shortly.

 

[cyb3rwolf]

Yep, that is the problem you're having and i just noticed that as well. I tried it and it worked perfectly. Change that line to this:

DoCmd.TransferSpreadsheet acImport, , "MyNewTable", "G:\DataFolder\DataFile1.xls", False, objSht.Name & "!c2:q17281"

 

[cyb3rwolf]

Here, here's my coded i did on a spreasheet i grabbed. modify this to what you need and you are golden.

Public Sub ProcessExcelFile1()
Dim objXL As New Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim mypath As String
mypath = "G:\DataFolder\"
Set objWkb = Workbooks.Open("\\FNPCPSP01\Home\LBRM03\tempsheet.xls")
With objWkb
For Each objSht In .Worksheets
DoCmd.TransferSpreadsheet acImport, , "MyNewTable", "\\FNPCPSP01\Home\LBRM03\tempsheet.xls", False, objSht.Name & "!a1:b12"
Next
End With
End Sub

 

[DJ_Hardin]

Thank You! This does just what I need! (My last hang-up was siimply that I accidently had a ' instead of a " !)

Here, here's my coded i did on a spreasheet i grabbed. modify this to what you need and you are golden.

 

[cyb3rwolf]

You are welcome. Sorry, i realized the entire time i was accidentally pasting from something before i had edited it, so that was probably pretty confusing.