G
gregory.emerson
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(Array( _
"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.
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(Array( _
"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.