Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > VBA to ask for file location and use to open DB
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 38
Default VBA to ask for file location and use to open DB
Howdy All,

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(Arra y( _
"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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 35,218
Default VBA to ask for file location and use to open DB
DBLocation is a string inside those double quotes:

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(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=" & DBLocation & ";Drive...



Brian wrote:

Howdy All,

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(Arra y( _
"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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 38
Default VBA to ask for file location and use to open DB
Again Dave, Thanks a Million!

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" wrote in message
...
DBLocation is a string inside those double quotes:

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(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=" & DBLocation & ";Drive...



Brian wrote:

Howdy All,

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(Arra y( _
"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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 35,218
Default VBA to ask for file location and use to open DB
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.



Brian wrote:

Again Dave, Thanks a Million!

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" wrote in message
...
DBLocation is a string inside those double quotes:

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(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=" & DBLocation & ";Drive...



Brian wrote:

Howdy All,

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(Arra y( _
"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

--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 35,218
Default VBA to ask for file location and use to open DB
Just to clarify...

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.

Brian wrote:

Again Dave, Thanks a Million!

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" wrote in message
...
DBLocation is a string inside those double quotes:

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(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=" & DBLocation & ";Drive...



Brian wrote:

Howdy All,

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(Arra y( _
"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

--

Dave Peterson

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Open File from FTP Location Bricol Excel Discussion (Misc queries) 3 January 9th 08 02:58 PM
Open From Read-Only Location ? Blewyn Excel Discussion (Misc queries) 3 December 17th 06 04:18 PM
Get File location Jeff Excel Discussion (Misc queries) 2 September 26th 06 08:20 PM
In Excel - Use Windows Explorer instead of File Open to open file KymY Excel Discussion (Misc queries) 1 August 5th 06 09:59 PM
i couldn't open my excel files,it gives location couldn't find err Hozefa Excel Discussion (Misc queries) 1 December 21st 05 04:06 PM


All times are GMT +1. The time now is 07:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement