By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

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

P: 48
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
Aug 11 '08 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,660
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?
Aug 11 '08 #2

P: 48
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.
Aug 11 '08 #3

NeoPa
Expert Mod 15k+
P: 31,660
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.
Expand|Select|Wrap|Line Numbers
  1. Private Const conUpdate As String = _
  2.                   "INSERT INTO tblTable" & _
  3.                   "(Col1,Col2,Col3,Col4,Col5,Col6) " & _
  4.                   "VALUES(%A,%B,%C,%D,%E,%F)"
  5. Private dblC1 As Double
  6. Private strC2 As String
  7. Private datC3 As Date
  8. Private dblC4 As Double
  9. Private dblC5 As Double
  10. 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 (#)).
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String, strWork As String
  2.  
  3. 'Insert your code to populate the vales in dblC1 through dblC6
  4.  
  5. strSQL = conUpdate
  6. 'Formate for numeric values
  7. strSQL = Replace(strSQL, "%A", dblC1)
  8. 'Formate for string values
  9. strSQL = Replace(strSQL, "%B", "'" & strC2 & "'")
  10. 'Formate for numeric values
  11. strSQL = Replace(strSQL, "%C", Format(datC3, '\#m/d/yyyy\#'))
  12. strSQL = Replace(strSQL, "%D", dblC4)
  13. strSQL = Replace(strSQL, "%E", dblC5)
  14. strSQL = Replace(strSQL, "%F", dblC6)
  15.  
  16. Call DoCmd.RunSQL(SQLStatement:=strSQL, UseTransaction:=False)
Aug 12 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.