Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Importing Data from Access Query to Excel Sheet
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 32
Default Importing Data from Access Query to Excel Sheet
Hello everyone,
I have a Macros that Imports Data from an Access Table into a worksheet.
The name of the Access DB changes every month so I can not just run the same
Macros Every month. The Macros works great, I only need to add some code to
it to read the value (Where the user types the File Name) in cell F9 found in
and ad the file name to the path.

Here is the Macros(Read my comments please):

ImportDataFromAccess Macro
' Macro recorded 03/18/2009
'

'
' Here I Highlighted the Text in cell F9 of the Control Sheet
ActiveCell.FormulaR1C1 = "TestDBForExcel.mdb"
'Here I set the focus away from cell F9
Range("F11").Select
'Here I selected the Sheet were the import will take place
Sheets("PropData").Select
'Here the Import data begins, the DATA SOURCE path does not recognise
the F9 value, I believe it only copied the name from when I pasted the name.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=P:\MyDocuments\Access\TestDBForExcel.mdb;Mo de=Share Deny
Write" _
, _
";Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine " _
, _
"Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial
Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New " _
, _
"Database Password="""";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Comp" _
, "act=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("8508Table Query")
'What is this? Is this where the file name could be changed?
.Name = "TestDBForExcel"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'Or is it here that I need to change the name of the .mdb file?
.SourceDataFile = "P:\MyDocuments\Access\TestDBForExcel.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("Control").Select
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,942
Default Importing Data from Access Query to Excel Sheet
hi
try this...
dim dbname as string
dbname = range("F9").value
Source=P:\MyDocuments\Access\" & dbname & " .mdb;Mode=Share Deny
Write" _
'etc.

regards
FSt2

"Memphis" wrote:

Hello everyone,
I have a Macros that Imports Data from an Access Table into a worksheet.
The name of the Access DB changes every month so I can not just run the same
Macros Every month. The Macros works great, I only need to add some code to
it to read the value (Where the user types the File Name) in cell F9 found in
and ad the file name to the path.

Here is the Macros(Read my comments please):

ImportDataFromAccess Macro
' Macro recorded 03/18/2009
'

'
' Here I Highlighted the Text in cell F9 of the Control Sheet
ActiveCell.FormulaR1C1 = "TestDBForExcel.mdb"
'Here I set the focus away from cell F9
Range("F11").Select
'Here I selected the Sheet were the import will take place
Sheets("PropData").Select
'Here the Import data begins, the DATA SOURCE path does not recognise
the F9 value, I believe it only copied the name from when I pasted the name.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=P:\MyDocuments\Access\TestDBForExcel.mdb;Mo de=Share Deny
Write" _
, _
";Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine " _
, _
"Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial
Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New " _
, _
"Database Password="""";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Comp" _
, "act=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("8508Table Query")
'What is this? Is this where the file name could be changed?
.Name = "TestDBForExcel"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'Or is it here that I need to change the name of the .mdb file?
.SourceDataFile = "P:\MyDocuments\Access\TestDBForExcel.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("Control").Select
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 32
Default Importing Data from Access Query to Excel Sheet
Thank you for your help.
I am getting the following error:
Could Not find file P:\MyDocuments\Access\TestDBForExcel.mdb
And then the MS Jet OLEDB Initialization Info Screen is presented. On the
Data Source It has the full path highlighted in blue. I click OK and the same
Could Not find file message box appears. I click ok and I get the Debug box
and then It highlights in yellow the " .Refresh BackgroundQuery:=False" line

Here is the code i modified with your help:
Sub ImportDataFromAccess()
'

Dim dbname As String
dbname = Range("F9").Value
Sheets("PropData").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source="P:\MyDocuments\Access\"" & dbname & " .mdb;Mode=Share
Deny Write" _
, _
";Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine " _
, _
"Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial
Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New " _
, _
"Database Password="""";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Comp" _
, "act=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("8508Table Query")
'What is this? Is this where the file name could be changed?
.Name = "WHAT GOES HERE? I deleted the old info and left it blank."
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = ""P:\MyDocuments\Access\TestDBForExcel.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("Control").Select
End Sub



"FSt1" wrote:

hi
try this...
dim dbname as string
dbname = range("F9").value
Source=P:\MyDocuments\Access\" & dbname & " .mdb;Mode=Share Deny
Write" _
'etc.

regards
FSt2

"Memphis" wrote:

Hello everyone,
I have a Macros that Imports Data from an Access Table into a worksheet.
The name of the Access DB changes every month so I can not just run the same
Macros Every month. The Macros works great, I only need to add some code to
it to read the value (Where the user types the File Name) in cell F9 found in
and ad the file name to the path.

Here is the Macros(Read my comments please):

ImportDataFromAccess Macro
' Macro recorded 03/18/2009
'

'
' Here I Highlighted the Text in cell F9 of the Control Sheet
ActiveCell.FormulaR1C1 = "TestDBForExcel.mdb"
'Here I set the focus away from cell F9
Range("F11").Select
'Here I selected the Sheet were the import will take place
Sheets("PropData").Select
'Here the Import data begins, the DATA SOURCE path does not recognise
the F9 value, I believe it only copied the name from when I pasted the name.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=P:\MyDocuments\Access\TestDBForExcel.mdb;Mo de=Share Deny
Write" _
, _
";Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine " _
, _
"Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial
Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New " _
, _
"Database Password="""";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Comp" _
, "act=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("8508Table Query")
'What is this? Is this where the file name could be changed?
.Name = "TestDBForExcel"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'Or is it here that I need to change the name of the .mdb file?
.SourceDataFile = "P:\MyDocuments\Access\TestDBForExcel.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("Control").Select
End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,942
Default Importing Data from Access Query to Excel Sheet
hi.
i tested this before posting and it worked in 03 on xp so i am at a loss
now. i'll test again.
check F9 value and make sure there are no hidden spaces and that the
spellling is correct.

regards
FSt1

"Memphis" wrote:

Thank you for your help.
I am getting the following error:
Could Not find file P:\MyDocuments\Access\TestDBForExcel.mdb
And then the MS Jet OLEDB Initialization Info Screen is presented. On the
Data Source It has the full path highlighted in blue. I click OK and the same
Could Not find file message box appears. I click ok and I get the Debug box
and then It highlights in yellow the " .Refresh BackgroundQuery:=False" line

Here is the code i modified with your help:
Sub ImportDataFromAccess()
'

Dim dbname As String
dbname = Range("F9").Value
Sheets("PropData").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source="P:\MyDocuments\Access\"" & dbname & " .mdb;Mode=Share
Deny Write" _
, _
";Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine " _
, _
"Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial
Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New " _
, _
"Database Password="""";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Comp" _
, "act=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("8508Table Query")
'What is this? Is this where the file name could be changed?
.Name = "WHAT GOES HERE? I deleted the old info and left it blank."
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = ""P:\MyDocuments\Access\TestDBForExcel.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("Control").Select
End Sub



"FSt1" wrote:

hi
try this...
dim dbname as string
dbname = range("F9").value
Source=P:\MyDocuments\Access\" & dbname & " .mdb;Mode=Share Deny
Write" _
'etc.

regards
FSt2

"Memphis" wrote:

Hello everyone,
I have a Macros that Imports Data from an Access Table into a worksheet.
The name of the Access DB changes every month so I can not just run the same
Macros Every month. The Macros works great, I only need to add some code to
it to read the value (Where the user types the File Name) in cell F9 found in
and ad the file name to the path.

Here is the Macros(Read my comments please):

ImportDataFromAccess Macro
' Macro recorded 03/18/2009
'

'
' Here I Highlighted the Text in cell F9 of the Control Sheet
ActiveCell.FormulaR1C1 = "TestDBForExcel.mdb"
'Here I set the focus away from cell F9
Range("F11").Select
'Here I selected the Sheet were the import will take place
Sheets("PropData").Select
'Here the Import data begins, the DATA SOURCE path does not recognise
the F9 value, I believe it only copied the name from when I pasted the name.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=P:\MyDocuments\Access\TestDBForExcel.mdb;Mo de=Share Deny
Write" _
, _
";Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine " _
, _
"Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial
Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New " _
, _
"Database Password="""";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Comp" _
, "act=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("8508Table Query")
'What is this? Is this where the file name could be changed?
.Name = "TestDBForExcel"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'Or is it here that I need to change the name of the .mdb file?
.SourceDataFile = "P:\MyDocuments\Access\TestDBForExcel.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("Control").Select
End Sub

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 32
Default Importing Data from Access Query to Excel Sheet
Thank you so much! it worked like a charm

I may post some other questions later..

Thanks again

"Memphis" wrote:

Thank you for your help.
I am getting the following error:
Could Not find file P:\MyDocuments\Access\TestDBForExcel.mdb
And then the MS Jet OLEDB Initialization Info Screen is presented. On the
Data Source It has the full path highlighted in blue. I click OK and the same
Could Not find file message box appears. I click ok and I get the Debug box
and then It highlights in yellow the " .Refresh BackgroundQuery:=False" line

Here is the code i modified with your help:
Sub ImportDataFromAccess()
'

Dim dbname As String
dbname = Range("F9").Value
Sheets("PropData").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source="P:\MyDocuments\Access\"" & dbname & " .mdb;Mode=Share
Deny Write" _
, _
";Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine " _
, _
"Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial
Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New " _
, _
"Database Password="""";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Comp" _
, "act=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("8508Table Query")
'What is this? Is this where the file name could be changed?
.Name = "WHAT GOES HERE? I deleted the old info and left it blank."
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = ""P:\MyDocuments\Access\TestDBForExcel.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("Control").Select
End Sub



"FSt1" wrote:

hi
try this...
dim dbname as string
dbname = range("F9").value
Source=P:\MyDocuments\Access\" & dbname & " .mdb;Mode=Share Deny
Write" _
'etc.

regards
FSt2

"Memphis" wrote:

Hello everyone,
I have a Macros that Imports Data from an Access Table into a worksheet.
The name of the Access DB changes every month so I can not just run the same
Macros Every month. The Macros works great, I only need to add some code to
it to read the value (Where the user types the File Name) in cell F9 found in
and ad the file name to the path.

Here is the Macros(Read my comments please):

ImportDataFromAccess Macro
' Macro recorded 03/18/2009
'

'
' Here I Highlighted the Text in cell F9 of the Control Sheet
ActiveCell.FormulaR1C1 = "TestDBForExcel.mdb"
'Here I set the focus away from cell F9
Range("F11").Select
'Here I selected the Sheet were the import will take place
Sheets("PropData").Select
'Here the Import data begins, the DATA SOURCE path does not recognise
the F9 value, I believe it only copied the name from when I pasted the name.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=P:\MyDocuments\Access\TestDBForExcel.mdb;Mo de=Share Deny
Write" _
, _
";Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine " _
, _
"Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial
Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New " _
, _
"Database Password="""";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Comp" _
, "act=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("8508Table Query")
'What is this? Is this where the file name could be changed?
.Name = "TestDBForExcel"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'Or is it here that I need to change the name of the .mdb file?
.SourceDataFile = "P:\MyDocuments\Access\TestDBForExcel.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("Control").Select
End Sub

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
importing/linking data from an Access 2003 Query to an Excel 2003 PerryK Excel Discussion (Misc queries) 2 August 24th 09 07:06 PM
Importing Data from Access to An Excel Sheet Memphis Excel Programming 5 March 27th 09 10:25 AM
Invalid function importing data query from Access Capt James Cook Excel Programming 1 November 30th 07 07:17 PM
Can you use VBA to query an Access database without importing data JonR Excel Programming 10 September 4th 07 02:00 AM
Importing Complicated Access Query into Excel Mike Roberto Excel Programming 3 August 30th 04 04:56 PM


All times are GMT +1. The time now is 10:18 AM.

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