PDA

View Full Version : [SOLVED] Import from Access to Excel, auto select .mdb file from same folder path as Excel



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.

Bob Phillips
02-19-2015, 04:23 AM
Don't you just mean


stDB = ThisWorkbook.Path & "\" & "AccessFile.mdb"
If Dir(stDB) = "" Then

MsgBox stDB & " not found", vbOKOnly
Exit Sub
End If

sindrefm
02-19-2015, 07:14 AM
Thank for answer.

What I really ment was that the .mdb file could have any name. The code look after a .mdb file in the same folder. If it is more than one .mdb file, open a pop up so that the user can select which .mdb file to import from.

Bob Phillips
02-19-2015, 07:55 AM
stDB = ThisWorkbook.Path & "\" & "*.mdb"
filename = Dir(stDB)
If filename = "" Then

MsgBox filename& " not found", vbOKOnly
Exit Sub
Else

stDB = ThisWorkbook.Path & "\" & filename
End If

sindrefm
03-05-2015, 01:13 AM
Hi, sorry for late answer.

Thanks.

Is it possible to add some code so that if it is more than one .mdb file in ThisWorkbook.path, a pop up opens up so that the user can select which .mdb file to import from?

snb
03-05-2015, 02:08 AM
Sub M_snb()
c00 = Dir(ThisWorkbook.Path & "\*.mdb")
c01 = Dir

If c01 <> "" Then
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Add "Database", "*.mdb"
.Show
c00 = .SelectedItems(1)
End With
End If

With CreateObject("ADODB.Recordset")
.Open "SELECT * from Blad1", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & c00 & ";"
For j = 1 To .Fields.Count - 1
ThisWorkbook.Sheets("Sheet1").Cells(1, j) = .Fields(j).Name
Next
ThisWorkbook.Sheets("Sheet1").Cells(2, 1).CopyFromRecordset .DataSource
End With
End Sub

sindrefm
03-09-2015, 12:19 AM
Thanks for the solutions!