Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel/Access Macro
HI,
I've been trying to figure out a way to get data from access into excel. Basically I have this huge database in access with two columns with different dates and that I need to sort accordingly inorder to get the correct value in a third column. The reason the access file is so big is that we will have a cell with a date like "Jan 1 2000" and in the colum next to it the date would read "Feb 1 2001". The database runs two years forward for a certain date, so we have 24 cells with "Jan 1 2000". Essentially I want to transpose this data in excel so I have rows with the date and columns with the future date, and the intersecting cells will contain the value. I'm sorry if this sound a little confusing. I recorded a macro and here is the code: With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\e13614\Desktop\Excel File\PriceModel.mdb;DefaultDir=C:\Documents and Setti" _ ), Array( _ "ngs\e13614\Desktop\Excel File;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _ )), Destination:=Range("A2")) .CommandText = Array( _ "SELECT tblPJMForwardPrices.MarketDate, tblPJMForwardPrices.Date, tblPJMForwardPrices.EnergyOnpeak" & Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Settings\e13614\Desktop\Excel File\PriceModel`.tblPJMForwardPrices tblPJMForwa" _ , _ "rdPrices" & Chr(13) & "" & Chr(10) & "WHERE (tblPJMForwardPrices.MarketDate={ts '2000-01-13 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY tblPJMForwardPrices.Date, tblPJMForwardPrices.EnergyOnpeak" _ ) .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 Range("C3:C25").Select Selection.Copy Sheets("Sheet1").Select Range("B15").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Sheet2").Select Range("A2:C25").Select Range("C2").Activate Application.CutCopyMode = False Selection.ClearContents Selection.QueryTable.Delete I want to be able to automate this process because each time I have to go back into the macro change the date and the cell in which to paste the information. Thanks alot. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel/Access Macro
I have info here and also look a the link on the bottom of the page to Ole P. Erlandsen's
http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... HI, I've been trying to figure out a way to get data from access into excel. Basically I have this huge database in access with two columns with different dates and that I need to sort accordingly inorder to get the correct value in a third column. The reason the access file is so big is that we will have a cell with a date like "Jan 1 2000" and in the colum next to it the date would read "Feb 1 2001". The database runs two years forward for a certain date, so we have 24 cells with "Jan 1 2000". Essentially I want to transpose this data in excel so I have rows with the date and columns with the future date, and the intersecting cells will contain the value. I'm sorry if this sound a little confusing. I recorded a macro and here is the code: With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\e13614\Desktop\Excel File\PriceModel.mdb;DefaultDir=C:\Documents and Setti" _ ), Array( _ "ngs\e13614\Desktop\Excel File;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _ )), Destination:=Range("A2")) .CommandText = Array( _ "SELECT tblPJMForwardPrices.MarketDate, tblPJMForwardPrices.Date, tblPJMForwardPrices.EnergyOnpeak" & Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Settings\e13614\Desktop\Excel File\PriceModel`.tblPJMForwardPrices tblPJMForwa" _ , _ "rdPrices" & Chr(13) & "" & Chr(10) & "WHERE (tblPJMForwardPrices.MarketDate={ts '2000-01-13 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY tblPJMForwardPrices.Date, tblPJMForwardPrices.EnergyOnpeak" _ ) .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 Range("C3:C25").Select Selection.Copy Sheets("Sheet1").Select Range("B15").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Sheet2").Select Range("A2:C25").Select Range("C2").Activate Application.CutCopyMode = False Selection.ClearContents Selection.QueryTable.Delete I want to be able to automate this process because each time I have to go back into the macro change the date and the cell in which to paste the information. Thanks alot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call an Access macro from an Excel macro | Excel Discussion (Misc queries) | |||
Trigger a Macro in MS Access from an Excel Macro? | Excel Programming | |||
Excel Macro with Access | Excel Programming | |||
run a macro in excel from an access vba | Excel Programming | |||
Launch Macro in Access via Macro running in Excel??? | Excel Programming |