Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting from Access
Can anyone help with the code to perform the steps below?
Starting from an Excel file called TUV.xls... 1. Open an Access database called XYZ.mdb 2. Run a query called ABC 3. Copy the results to an Excel file called JKL.xls Is this possible? If you can help me, I won't bother anyone on this forum for at least an hour. Thanks Daniel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting from Access
Hi,
I assume that TUV.xls is where you put the code. As I use "SaveAs" to create the jkl.xls file, be aware that every copy of the file contains the vba code. HTH -- arunkhemlai ' -------------------------------------------- Option Explicit Sub ImportFromAccess() Dim dbname, dbpath, conn ' ** Here put your access filename. dbname = "Northwind.mdb" ' ** Path to your access file. dbpath = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\" conn = _ "ODBC;" + _ "DSN=MS Access Database;" + _ "DBQ=" + dbpath + dbname + ";" + _ "DefaultDir=" + dbpath + ";" + _ "DriverId=25;" + _ "FIL=MS Access;" With ActiveSheet.QueryTables.Add(Connection:=conn, Destination:=Range("A1")) ' ** Change the query to what you want. .CommandText = _ "SELECT Categories.CategoryID, Categories.Description " + _ "FROM `" + dbpath + dbname + "`.Categories Categories" .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 Dim outname, outpath ' ** Output file name and path. outname = "jkl.xls" outpath = "c:\" ActiveWorkbook.SaveAs Filename:=outpath + outname, FileFormat:=xlNormal End Sub ' ------------------------------------- "Daniel Bonallack" wrote: Can anyone help with the code to perform the steps below? Starting from an Excel file called TUV.xls... 1. Open an Access database called XYZ.mdb 2. Run a query called ABC 3. Copy the results to an Excel file called JKL.xls Is this possible? If you can help me, I won't bother anyone on this forum for at least an hour. Thanks Daniel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting from Access
Thank you so much!
But can you tell me, the line: .CommandText = _ "SELECT Categories.CategoryID, Categories.Description " + _ "FROM `" + dbpath + dbname + "`.Categories Categories" I need to alter this, is that correct? I'm not quite sure that I know how. I altered the line below: ..Name = "Query from MS Access Database" to ..Name = "LTM Coverage EMEA" But it doesn't work (I get an error on ".Refresh BackgroundQuery:=False") Can you help a little further - I'm not quite sure what to do. Thanks! Daniel "arunkhemlai" wrote: Hi, I assume that TUV.xls is where you put the code. As I use "SaveAs" to create the jkl.xls file, be aware that every copy of the file contains the vba code. HTH -- arunkhemlai ' -------------------------------------------- Option Explicit Sub ImportFromAccess() Dim dbname, dbpath, conn ' ** Here put your access filename. dbname = "Northwind.mdb" ' ** Path to your access file. dbpath = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\" conn = _ "ODBC;" + _ "DSN=MS Access Database;" + _ "DBQ=" + dbpath + dbname + ";" + _ "DefaultDir=" + dbpath + ";" + _ "DriverId=25;" + _ "FIL=MS Access;" With ActiveSheet.QueryTables.Add(Connection:=conn, Destination:=Range("A1")) ' ** Change the query to what you want. .CommandText = _ "SELECT Categories.CategoryID, Categories.Description " + _ "FROM `" + dbpath + dbname + "`.Categories Categories" .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 Dim outname, outpath ' ** Output file name and path. outname = "jkl.xls" outpath = "c:\" ActiveWorkbook.SaveAs Filename:=outpath + outname, FileFormat:=xlNormal End Sub ' ------------------------------------- "Daniel Bonallack" wrote: Can anyone help with the code to perform the steps below? Starting from an Excel file called TUV.xls... 1. Open an Access database called XYZ.mdb 2. Run a query called ABC 3. Copy the results to an Excel file called JKL.xls Is this possible? If you can help me, I won't bother anyone on this forum for at least an hour. Thanks Daniel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting from Access
.CommandText = _
"SELECT Categories.CategoryID, Categories.Description " + _ "FROM `" + dbpath + dbname + "`.Categories Categories" I need to alter this, is that correct? I'm not quite sure that I know how. The query string above was generated by the macro recorder, that's why it looks so complicated. A simpler version would be: ..CommandText = "SELECT CategoryID, Description FROM Categories"; So what you have to do is to simply replace the query string with your own. I altered the line below: .Name = "Query from MS Access Database" to .Name = "LTM Coverage EMEA" No need, just leave it as it is. But it doesn't work (I get an error on ".Refresh BackgroundQuery:=False") Can't tell. It works fine for me. Try commenting this line out and make sure that you copy the whole code. Good luck -- arunkhemlai |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting from Access
Hi: Dan
The following post may also be of some help. search: Connection using ADO (4/1/2005) this ng good luck TK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Excel Formulas into Access | Excel Programming | |||
Extracting Excel Formulas into Access | Excel Programming | |||
Access data -work in Excel- save in Access | Excel Programming | |||
Extracting Excel data and uploading to access | Excel Programming | |||
Extracting Access data | Excel Programming |