Can I export specific cell information from Excel into Access?

[Guest]

I need to use access to store my database of serial numbers. These numbers
are generated in my MS Excel spreadsheets. Each one has a unique Serial
number in it. The spreadsheets vary and are never the same. The serial
number info is consistently in each spreadsheet. I would l like to track the
serial numbers, as well as some other data from these sheets, in ACCESS. But
all I know of the import/export aspects is that complete sheets or ranges can
be exported. shat about cells?

Also, I need to have this update be dynamically saved to the access database
once the spreadsheet is saved.

thanks so much!

 

[Douglas J. Steele]

Assuming Excel and Access are both installed on the same machine, you can
use Automation to do this.

For instance, the following assumes that there's a workbook named
"SampleWorkbook.xls" in the same folder as your Access application and that
there's a worksheet named "Sample Data" in that workbook. It checks whether
cell A1 contains the word "Data". If it does, it pops up a message box
containing the contents of cell B1. If not, it pops up a message to that
effect. (Note that an error will arise if there isn't a worksheet named
"Sample Data" in the workbook):


Sub ReadFromWorkbook()

Dim objActiveWkbk As Object
Dim objActiveWksh As Object
Dim objXL As Object
Dim strWkbkName As String

strWkbkName = CurrentDb().Name
strWkbkName = Left$(strWkbkName, _
Len(strWkbkName) - Len(Dir$(strWkbkName))) & _
"SampleWorkbook.xls"
If Len(Dir(strWkbkName)) = 0 Then
MsgBox strWkbkName & " not found."
Else

CreateObject("Excel.Application")
objXL.Application.Workbooks.Open strWkbkName
Set objActiveWkbk = _
objXL.Application.ActiveWorkbook
Set objActiveWksh = _
objActiveWkbk.Worksheets("Sample Data")

If objActiveWksh.Cells(1, 1) = "Data" Then
MsgBox "Cell A2 contains " & _
objActiveWksh.Cells(1, 2)
Else
MsgBox "Cell A1 does not contain Data"
End If

End If

objActiveWkbk.Close SaveChanges:=False
Set objActiveWkbk = Nothing
objXL.Application.Quit
Set objXL = Nothing

End Sub

 

[Guest]

You stated that if it was on the same computer. What if the files are in the
same directory on a network drive? Being accessed on various workstations?

Thanks.

 

[Douglas J. Steele]

Where the files are is immaterial (both .MDB and .XLS). Both Access and
Excel run on the client, regardless of where the files are.

 

[Guest]

Thank you so much. I will try it out and hopefully I won't have any
problems. Thanks again.