Retrieving data in certain cells in Excel to MS Access usign VBA

[keirnus]

hello,

been searching for a solution on this but all i get are advices for importing data from excel directly to MS Access DB.

my case here is different...the sheet in Excel file is not ready for importing...
the data are scattered from different cells from different sheets...
this is because these sheets are automated for their own role...

i just need to get data from a certain cell using VBA den save the data to MS Access DB.

Example:
the data are in Sheet 1 in A1, A2, C5, F5...
others are in Sheet 2 in B4, B5, B6, G7...

you see, i need to get each data from them...
then save them to MS Access DB...
the sheet is not ready for db import...

does anyone knows how to retrieve data in each cell?
then save data one-by-one to MS Access DB...
or are there other better ways?
(must not change the format of the excel)


hoping to hear good advices from the experts and the experienced,
keirnus

[NeoPa]

Using Application Automation it's relatively straightforward to get the data from Excel as long as you know where the data is.

Saving "it" into Access though, is just plain meaningless. Where and how do you want these disparate items of data to be saved?

[keirnus]

Using Application Automation it's relatively straightforward to get the data from Excel as long as you know where the data is.

Saving "it" into Access though, is just plain meaningless. Where and how do you want these disparate items of data to be saved?

Thanks NeoPa for the reply.
But the data in Excel are scattered in different cells.

Example:
I have one table in Access called "tblTable".
The columns are Col1, Col2, Col3, Col4, Col5, Col6.
The data to be inserted in this table are in different cells in Excel.
The locations are Sheet1 in A1, A2, C5 then Sheet2 in B4, B5, G7.

Let's say:
[tblTable].Col1 = Sheet1!A1
[tblTable].Col2 = Sheet1!A2
[tblTable].Col3 = Sheet1!C5
[tblTable].Col4 = Sheet2!B4
[tblTable].Col5 = Sheet2!B5
[tblTable].Col6 = Sheet2!G7

How do i get the data in Excel one by one?

I'm sorry but i'm kinda new on this stuff.
But I kinda liking doing this already. =)
Just got bumped on this though. =(

Hope you could help give advices.

[NeoPa]

Assuming then, that you want to add a new record into the table every time this process is run, you need to include the following logic.

NB. This is not the whole program or code, but it does provide the building blocks you will need.

Firstly, using the Application Automation linked in the earlier post, retrieve the specific cells that you need into six variables that will not lose scope. These variables (or the data in them) will be required later on to insert into the table.

I will assume, for the moment, that the first three items are numeric, string and date data respectively. I doubt your actual data is, but it illustrates how each should correctly be handled. ==> Col1=Numeric; Col2=String; Col3=Date. The remaining three columns are all Numeric. Numeric data should all be processed in a similar way, but which data type you use to store the data in may depend on the precise nature of the data you expect. I will use Double variables for all numeric items in the example.

At the top of a module (where the code to add the data into the table is to be found) you will need to define a constant which is the SQL string used to add in the data. I also include in here the Dim statements for the six variables as I've described.

Private Const conUpdate As String = _
                  "INSERT INTO tblTable" & _
                  "(Col1,Col2,Col3,Col4,Col5,Col6) " & _
                  "VALUES(%A,%B,%C,%D,%E,%F)"
Private dblC1 As Double
Private strC2 As String
Private datC3 As Date
Private dblC4 As Double
Private dblC5 As Double
Private dblC6 As Double

You will then have some code to get all the data from Excel. We can dig into this a little deeper if you find you need to, but for now I will leave you with the link and expect you to work out the basics of how this is done. I believe all the info you need is there.

After that there will be code to populate a new record with this data. Note very well, the difference in how the different fields are handled. SQL is particularly complicated where dates are concerned. This code is designed for SQL. It does NOT depend in the least on which country the code is run in. SQL date format is global and independent of local formats (See Literal DateTimes and Their Delimiters (#)).

Dim strSQL As String, strWork As String
 
'Insert your code to populate the vales in dblC1 through dblC6
 
strSQL = conUpdate
'Formate for numeric values
strSQL = Replace(strSQL, "%A", dblC1)
'Formate for string values
strSQL = Replace(strSQL, "%B", "'" & strC2 & "'")
'Formate for numeric values
strSQL = Replace(strSQL, "%C", Format(datC3, '\#m/d/yyyy\#'))
strSQL = Replace(strSQL, "%D", dblC4)
strSQL = Replace(strSQL, "%E", dblC5)
strSQL = Replace(strSQL, "%F", dblC6)
 
Call DoCmd.RunSQL(SQLStatement:=strSQL, UseTransaction:=False)