Vba error 438

ONIWE

New Member
Joined
Jun 29, 2010
Messages
25
Hello Good People,

Please help as my code is return an error 438: Object does not support this method or property.
I'm trying to import data from an access spreadsheet to excel. I'm not sure of what to do regarding the object error.Please see the code below:



Option Explicit
Sub Import_Data_Access_2007()

Const stDB As String = "c:\users\dedo\documents\Northwind 2007.accdb"
Const stWholeTable As String = "Shippers"
Const stSQL As String = "SELECT Company,City FROM Shippers" & _
"WHERE [Country Region] ='USA'"

Const stStoredReport As String = "Order Summary"


On Error GoTo Error_Handling

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset

Dim wbTarget As Workbook
Dim wsOrders As Worksheet
Dim wsShippers As Worksheet
Dim wsProducts As Worksheet
Dim rnOrders As Range
Dim rnShippers As Range
Dim rnProducts As Range
Dim lnCounter As Long

Set wbTarget = ActiveWorkbook

With wbTarget
Set wsOrders = .Worksheet(1)
Set wsShippers = .Worksheet(2)
Set wsProducts = .Worksheet(3)
End With

Set rnOrders = wsOrders.Range("A2")
Set rnShippers = wsShippers.Range("A2")
Set rnProducts = wsProducts.Range("A2")

Set db = OpenDatabase(stDB)
Set tdf = db.TableDefs(stWholeTable)
Set rs = tdf.OpenRecordset(dbOpenTable)

Application.ScreenUpdating = False
For lnCounter = 0 To rs.Fields.Count - 1
wsShippers.Cells(1, lnCounter + 1).Value = rs.Fields(lnCounter).Name
Next lnCounter
rnShippers.CopyFromRecordset rs
Set rs = Nothing
Set rs = db.OpenRecordset(stSQL, dbOpenSnapshot)

For lnCounter = 0 To rs.Fields.Count - 1
wsProducts.Cells(1, lnCounter + 1).Value = rs.Fields(lnCounter).Name
Next lnCounter
rnProducts.CopyFromRecordset rs

Set rs = Nothing
Set rs = db.OpenRecordset(stStoredReport, dbOpenForwardOnly)

For lnCounter = 0 To rs.Fields.Count - 1
wsOrders.Cells(1, lnCounter + 1).Value = rs.Fields(lnCounter).Name
Next lnCounter


rnOrders.CopyFromRecordset rs

rs.Close
db.Close

MsgBox "All data has been successfully transfered.", vbOKOnly

ExitSub:
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub

Error_Handling:
MsgBox "Error number: " & Err.Number & vbNewLine & _
"Description: " & Err.Description, vbOKOnly

Resume ExitSub



End Sub



ANY SUGGESTIONS PLEASE:LOL:
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Remove On Error Resume Next, rerun the code and post back to tell us which line of code the error is happening on.

The error handler in your code isn't giving that information.
 

ONIWE

New Member
Joined
Jun 29, 2010
Messages
25
Thanks. I have removed the "On Error GoTo" and rerun it. The first error is on:

Set wsOrders = .worksheet(1)

I'm not sure why this is showing as an error though. Any further suggestions?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Try Worksheets instead of Worksheet.:)

By the way have you considered using a loop to import the data.

You seem to be repeating the same steps for each table/recordset/worksheet, all that appears to be changing are the names.
 

ONIWE

New Member
Joined
Jun 29, 2010
Messages
25
Norie,

Thanks. Its all working perfectly now. For some reason I believed the code was quite long for the task it accomplished. I'm not sure which other loop to use as I could only populate with the for....next loop. Could you please send me the code using a loop that will make the code shorter.


Thanks.


ONIWE
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,906
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top