sindrefm
02-19-2015, 01:59 AM
Hi,
I have the following code for import from Access to Excel:
Sub Import_AccessData()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim stDB As String
Dim wsSheet As Worksheet
Dim lnNumberOfField As Long, lnCount As Integer
Set wsSheet = ThisWorkbook.Worksheets("Sheet1")
stDB = ThisWorkbook.Path & "\" & "AccessFile.mdb"
wsSheet.Range("A1").CurrentRegion.Clear
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"
rst.Open "SELECT * FROM Hull", cnt
lnNumberOfField = rst.Fields.Count
For lnCount = 0 To lnNumberOfField - 1
wsSheet.Cells(1, lnCount + 1).Value = rst.Fields(lnCount).Name
Next lnCount
wsSheet.Cells(2, 1).CopyFromRecordset rst
Set rst = Nothing
Set cnt = Nothing
End If
End Sub
What I want:
stDB = ThisWorkbook.Path & "\" & "AccessFile.mdb"
I want to have a code that auto select the .mdb file on ThisWorkbook.Path, and pop-up a MsgBox if the folder don't contain a .mdb file.
Can someone help on this?
Thanks.
I have the following code for import from Access to Excel:
Sub Import_AccessData()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim stDB As String
Dim wsSheet As Worksheet
Dim lnNumberOfField As Long, lnCount As Integer
Set wsSheet = ThisWorkbook.Worksheets("Sheet1")
stDB = ThisWorkbook.Path & "\" & "AccessFile.mdb"
wsSheet.Range("A1").CurrentRegion.Clear
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"
rst.Open "SELECT * FROM Hull", cnt
lnNumberOfField = rst.Fields.Count
For lnCount = 0 To lnNumberOfField - 1
wsSheet.Cells(1, lnCount + 1).Value = rst.Fields(lnCount).Name
Next lnCount
wsSheet.Cells(2, 1).CopyFromRecordset rst
Set rst = Nothing
Set cnt = Nothing
End If
End Sub
What I want:
stDB = ThisWorkbook.Path & "\" & "AccessFile.mdb"
I want to have a code that auto select the .mdb file on ThisWorkbook.Path, and pop-up a MsgBox if the folder don't contain a .mdb file.
Can someone help on this?
Thanks.