techtwo
05-05-2008, 08:53 AM
I am attempting to pull access data into an excel document. Of course pulling the full database or rows of data from access is simple and I have the macro to do so.
Sub pullfromaccess()
'
' PPIData1 Macro
' PPI Data 1 Pull
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MS Access Database;DBQ=C:\PPI.mdb;DefaultDir=C:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A1").End(xlToLeft).Offset(0, 1))
.CommandText = Array("SELECT PPI.`PPI ID 1`" & Chr(13) & "" & Chr(10) & "FROM `C:\PPI`.PPI PPI")
.Name = "Query from MS Access Database"
.FieldNames = False
.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
ActiveWindow.SmallScroll Down:=9
End Sub
So, this will pull one specific column from the access database that I have listed there. My issue is I need to be able to pull one cell from that column and put it in a specific locale in excel. Please excuse the coding I have broken whatever hand it is I write code in.
I have quite obviously used the excel macro recorder and then modified it slightly so I imagine to an experienced coder it's atrocious. I am not experienced in VBA so I am attempting to rectify this with some reading. Any help from you wonderful people would be greatly appreciated and I am not in love with the code I have so if you have a better idea please feel free to share.
.
~Oorang
Sub pullfromaccess()
'
' PPIData1 Macro
' PPI Data 1 Pull
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MS Access Database;DBQ=C:\PPI.mdb;DefaultDir=C:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A1").End(xlToLeft).Offset(0, 1))
.CommandText = Array("SELECT PPI.`PPI ID 1`" & Chr(13) & "" & Chr(10) & "FROM `C:\PPI`.PPI PPI")
.Name = "Query from MS Access Database"
.FieldNames = False
.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
ActiveWindow.SmallScroll Down:=9
End Sub
So, this will pull one specific column from the access database that I have listed there. My issue is I need to be able to pull one cell from that column and put it in a specific locale in excel. Please excuse the coding I have broken whatever hand it is I write code in.
I have quite obviously used the excel macro recorder and then modified it slightly so I imagine to an experienced coder it's atrocious. I am not experienced in VBA so I am attempting to rectify this with some reading. Any help from you wonderful people would be greatly appreciated and I am not in love with the code I have so if you have a better idea please feel free to share.
.
~Oorang