Can I export specific cell information from Excel into Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
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.
 
Where the files are is immaterial (both .MDB and .XLS). Both Access and
Excel run on the client, regardless of where the files are.
 
Thank you so much. I will try it out and hopefully I won't have any
problems. Thanks again.
 
Back
Top