VBA to ask for file location and use to open DB
Brian
This is part of what I hope to be a large project...
I have created a button, that when press, will prompt the user to browse for
an .mdb file.
I then run a query on that file to bring data into a new worksheet.
When I run it, opens the browse window and I browse and select my database
file, but then I get an error that says:
Could not find file 'C:\DBLocation.mdb'.
This is what I have:
Sub DataImport()
Dim DBLocation As String
DBLocation = Application.GetOpenFilename
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=DBLocation;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `Combined Data`.`Assignee Mgr Name`, `Combined
Data`.Assignee,
`Combined Data`.`Instance#`, `Combined Data`.`SR Number`, `Combined
Data`.`SR
Reported Date`, `Combined Data`.`Task Number`, `Comb" _
, _
"ined Data`.`Task Actual Start Date`, `Combined Data`.`Task Actual
End
Date`, `Combined Data`.`Debrief Service Month`, `Combined Data`.`Debrief
Status`, `Combined Data`.`Task Type`, `Combined Data`.`Se" _
, _
"rvice Activity Code`, `Combined Data`.`Current Extended Labor
Cost`,
`Combined Data`.`Current Extended Travel Cost`, `Combined Data`.`Current
Extended Standard Cost`, `Combined Data`.`Current Extended" _
, _
" Total Cost`, `Combined Data`.LABOR, `Combined Data`.TRAVEL,
`Combined
Data`.`Ttl Hours`" & Chr(13) & "" & Chr(10) & "FROM `Combined Data`
`Combined
Data`" & Chr(13) & "" & Chr(10) & "WHERE (`Combined Data`.`Assignee Mgr
Name`='KLAVINS, EGONS E JR (ED)')" & Chr(13) & "" & Chr(10) & "" _
, "ORDER BY `Combined Data`.Assignee")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
I'm not sure what I missed...
Thanks,
Brian
Dave Peterson
I'd try:
Dim DBLocation as Variant 'not a string
DBLocation = Application.GetOpenFilename
'just another safety check:
if dblocation = false then
'user hit cancel
exit sub
end if
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & DBLocation & ";Drive...
--
Dave Peterson
Brian
I had tried " & DBLocation & ", but with DBLocation as a string, I'm
guessing that Variant made the difference?
I hope you don't mind what are likely to be additional questions as my
project progresses.
Brian
"Dave Peterson" <pete...@verizonXSPAM.net> wrote in message
news:4A0D5AA7...@verizonXSPAM.net...
Dave Peterson
Dim FName as string
fname = application.getopenfilename(...)
if fname = "False" then
vs
Dim FName as Variant
fname = application.getopenfilename(...)
if fname = False then
Dave Peterson wrote:
>
> The only time that the declaration would make a difference is if the user hit
> cancel--and then your code would have to either check that.
>
> Or the code would be trying to open a file named DBLocation (as that string)
> or a file named False if you used "...& dblocation &..."
>
> You may want to start asking programming questions in the .programming
> newsgroup. Most of the regulars read the major newsgroups, but it makes more
> sense down the hall a bit.
> --
>
> Dave Peterson
--
Dave Peterson
Dave Peterson
cancel--and then your code would have to either check that.
Or the code would be trying to open a file named DBLocation (as that string)
or a file named False if you used "...& dblocation &..."
You may want to start asking programming questions in the .programming
newsgroup. Most of the regulars read the major newsgroups, but it makes more
sense down the hall a bit.
--
Dave Peterson