Pull Access cells into Excel

[techtwo]

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

[akanchu]

Hi.
The value of that single cell of the column that you want, how do you recognize that value, eg. if there is a particular indicator in same column or different column.
With the help of that you might be able to change your query to just pull that value.

Please provide more details.

[techtwo]

The values in this particular Dbase are simple, numerical quantities in 10 variations seperated by column with a unique identifier for each row in column 1.

UniqueID Num1 Num2 Num3 Num4 Num5 Num6 Num7 Num8 Num9 Num10


So I hadn't planned on bothering with that detail yet but I can see how that might be easier to tackle both at once. I need to identify the Unique identifier in column 1 and then take the numeric value that I want in that row from the column that I need. So as far as I can guess this is probably in a text book somewhere labeled example one but it is still not within my grasp.

Thanks for your help.

[akanchu]

yup.
you will have to find that unique value in column1 and then use the where condition with your SQL statement.

eg where column1/unique id = 'XYZ'

Hope this helps.

[techtwo]

I agree, the issue I am having is I don't know how to modify the macro to do this. For the time being I am simply attempting to find a way to pull one specific cell in from access, I think I can work my way up from that.

The grand scheme is to have the macro search the ID column in the Dbase and return the variable from the second column specified.

As an example lets say box b13 will be a box that you put the id into that you want to find, then box b14 will be the name of the cell you want to pull from in Access. Once you fill those boxes and press the button the macro will pull that data and put it in box B15.

So yes this is basic and I am miss a few parts but to start with I need to be able to pull that single access cell on command. So if anyone has any ideas if that is possible or how to go about doing it please let me know.