Question

Locked

Excel VBA - Macro to Import data from Access to Excel

By RajKhemka ·
I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.

The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:

1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description

The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.


Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2

bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If

Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop


End Sub


Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Application.ActiveSheet
iRow = 2
Do Until WS.Cells(iRow, 1) = ""
TargetWS.Cells(sRow, 7) = WS.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.Close
Application.DisplayAlerts = True

End Sub


The "ImportAcessData" sub procedure above works just fine. However, the "Workbooks.OpenDatabase fl, strSQL, xlCmdTable" line in "GetData" sub procedure seems to be incompatible with excel 2000. Could someone help debug this?? Thanks!

This conversation is currently closed to new comments.

0 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Share your knowledge
Back to Software Forum
0 total posts (Page 1 of 1)  

Related Discussions

Related Forums