read cell data from Excel and write to Access

[ActualHealth]

I need to read one cell data on one specific worksheet from each of all the Excel workbooks saved in one same folder, and write the data into a table in the Access file. The Access file table has only two columns: the first column has the string to use as indicator on which workbook to read the data from, and the second column stores the data retrieved from that correspponding workbook.

I saw that there happens to have one thread on similar question posted today. And I think the section '

Write Data From an EXCEL Worksheet into a Recordset using Automation (VBA)​

' from the suggested link might be a potential solution with some modifications. However, I also noticed that it's actually openning each workbook while I only need to read just one cell from it, which I assume will cause some overhead when running the code.

Are there any better way to do it for my case? Thank you very much for any suggestions.

 

[ActualHealth]

My original post actually has the links to both the other thread in this forum with similar question and also to the website which has the article on Excel importing topic. However, I was told that I can not post any link here yet. Sorry about any inconvenience.

I need to read one cell data on one specific worksheet from each of all the Excel workbooks saved in one same folder, and write the data into a table in the Access file. The Access file table has only two columns: the first column has the string to use as indicator on which workbook to read the data from, and the second column stores the data retrieved from that correspponding workbook.

I saw that there happens to have one thread on similar question posted today. And I think the section '

Write Data From an EXCEL Worksheet into a Recordset using Automation (VBA)​

' from the suggested link might be a potential solution with some modifications. However, I also noticed that it's actually openning each workbook while I only need to read just one cell from it, which I assume will cause some overhead when running the code.

Are there any better way to do it for my case? Thank you very much for any suggestions.

 

[Pat Hartman]

Welcome Aboard
You need to open the workbook to read it. Which is kinda like Nancy Pelosi's remark that the House needed to pass Obamacare to know what was in it.

Sorry the "health" in your name made me do it

 

[ActualHealth]

haha, thanks! I'm happy that I had the keyword in my id, and so am able to get reply this soon.

I was looking at the 'DoCmd.TransferSpreadsheet acImport' method, and it seems that this one does not require opening the workbook prior to get data from it. Is it true?

Welcome Aboard
You need to open the workbook to read it. Which is kinda like Nancy Pelosi's remark that the House needed to pass Obamacare to know what was in it.

Sorry the "health" in your name made me do it

 

[boblarson]

I was looking at the 'DoCmd.TransferSpreadsheet acImport' method, and it seems that this one does not require opening the workbook prior to get data from it. Is it true?

It is still opening it behind the scenes. You can't get something out of something without it technically being opened.

 

[ActualHealth]

Thanks for confirming it. It looks like that Ken's example could be a good solution for this case then.

It is still opening it behind the scenes. You can't get something out of something without it technically being opened.

 

[ActualHealth]

One other question, if the range for the data varies between different workbook and I need to search for the string data field which should always be right next to the data field I need to get with certain keyword, and then I'll be able to locate the cell reference. Is there anyway to do it with VB code in Access?

Although I have some experience with VB programming in Excel, however I'm completely new to VB programming in Access. Thanks for the help.

 

[Pat Hartman]

If you could write the code in Excel VBA to find the correct cell, you can copy that code and use it in Access. You will probably have to change the way objects are referenced since you are outside looking in.

 

[ActualHealth]

Thanks for the reply.

There are actually about 40 Excel workbooks which I need to read the value from, and each workbook has different owner. So it's probably not possible to make any change to the Excel workbooks for this purpose.

However, the cell I need to read the value from is always in one specific worksheet (the name of this worksheet is the same for all workbooks). And even that the cell might shift a couple of rows/columns, the cell two columns to the left of it always contains the unique string which I think can be used for lookup.

Is this something that can be done in Access by code? Thank you...

If you could write the code in Excel VBA to
find the correct cell, you can copy that code and use it in Access. You will probably have to change the way objects are referenced since you are outside looking in.

 

[Pat Hartman]

Yes it can. I just don't know how to do it. If you know how to do it in Excel VBA, that same code will work from Access with minor modifications. If you don't get a specific code sample here (assuming that's what you need), post the same question in an Excel forum and we'll help you modify the code to work from Access.

 

[ActualHealth]

This is great. I'll try to get the code which works in Excel first, and ask for help here. Thanks so much!

Yes it can. I just don't know how to do it. If you know how to do it in Excel VBA, that same code will work from Access with minor modifications. If you don't get a specific code sample here (assuming that's what you need), post the same question in an Excel forum and we'll help you modify the code to work from Access.

 

[DavidAtWork]

If you're only trying to read the value in one cell, you should be able to extract that value and use a SQL insert statement to append a record to your Access table.
If the cell can vary in each workbook by only a few columns/rows then you can use the Find function
Dim searchVal, searchString as String
searchString= "myStringValue"

Open the workbook, activate the relevant worksheet, select cell A1

Cells.Find(What:=searchString, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

searchVal = Activecell.Offset(0,-2).Value 'assuming the value you're trying to extract is 2 cells to the left
Now insert the record into your Access table with:
Docmd.RunSQL("INSERT INTO myTable(myField, myWbook) VALUES(""" & searchVal & """, """ & thisWbookName & """)
thisWbookName will be another variable which it's value can be set when each workbook is opened. You'll need to use a file system object to loop through all the workbooks in a specified folder, opening each in turn, activate the relevant worksheet, select cell A1, use the Find code above and insert. Close the workbook, go on to the next.
There are plenty of posts on how to iterate through a folder of workbooks, opening each, perform some actions like the code above and close, next. The value for thisWbookName can be set from each file opened by extracting part of the file name string

David

 

[ActualHealth]

I'm able to make it work with the code from you and Ken's examples.

I noticed that the cell I want to get value from actually locates in the same range for all the Excel workbook right now, so I was able to simplyfly the code a little bit which probably also made it easier for me. I'll still need to use the Find function to polish the codes later.

Thanks so much for the help.

If you're only trying to read the value in one cell, you should be able to extract that value and use a SQL insert statement to append a record to your Access table.
If the cell can vary in each workbook by only a few columns/rows then you can use the Find function
Dim searchVal, searchString as String
searchString= "myStringValue"

Open the workbook, activate the relevant worksheet, select cell A1

Cells.Find(What:=searchString, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

searchVal = Activecell.Offset(0,-2).Value 'assuming the value you're trying to extract is 2 cells to the left
Now insert the record into your Access table with:
Docmd.RunSQL("INSERT INTO myTable(myField, myWbook) VALUES(""" & searchVal & """, """ & thisWbookName & """)
thisWbookName will be another variable which it's value can be set when each workbook is opened. You'll need to use a file system object to loop through all the workbooks in a specified folder, opening each in turn, activate the relevant worksheet, select cell A1, use the Find code above and insert. Close the workbook, go on to the next.
There are plenty of posts on how to iterate through a folder of workbooks, opening each, perform some actions like the code above and close, next. The value for thisWbookName can be set from each file opened by extracting part of the file name string

David