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

[keirnus]

hello,

been searching for a solution on this but
all i get are advices for importing the
whole worksheet directly to MS Access DB.

my case here is different...the data are scattered
in different cells in a worksheet...the rest of the cells
are not needed for importing...

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

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

is this possible?
if so, how?


badly need advices from the experts and the experienced,

keirnus

 

[ajetrumpet]

Maybe you could declare an application variable, and open Excel that way? One you've opened the workbook, I wonder if you could extract the data simply by reference:

dim obj as object, Mpath as string

set obj = createobject("excel.application")
  Mpath = "Path of the File Here"

obj.visible = true
obj.workbooks.open(Mpath)

   'DO WHAT YOU NEED TO DO HERE by referencing the cells in the open workbook...

This suggestion was actually taken from the second hit here.

 

[keirnus]

Maybe you could declare an application variable, and open Excel that way? One you've opened the workbook, I wonder if you could extract the data simply by reference:

dim obj as object, Mpath as string

set obj = createobject("excel.application")
  Mpath = "Path of the File Here"

obj.visible = true
obj.workbooks.open(Mpath)

   'DO WHAT YOU NEED TO DO HERE by referencing the cells in the open workbook...

This suggestion was actually taken from the second hit here.

Cool!

I'm now able to open the workbook.
Thanks, ajetrumpet.

Now, I need to get data per worksheet per cell.

 

[keirnus]

Got it already!

----------------------------------------

Dim oXL As Object
Dim sFullPath As String

Set oXL = CreateObject("Excel.Application")

sFullPath = CurrentProject.Path & "\ExcelFile.xls"

With oXL
.Visible = True
.Workbooks.Open (sFullPath)

' Get value per cell
.Worksheets("Sheet1").Range("A1").Value
.Worksheets("Sheet1").Range("A2").Value
.Worksheets("Sheet1").Range("C5").Value
.Worksheets("Sheet2").Range("B4").Value
.Worksheets("Sheet2").Range("B5").Value
.Worksheets("Sheet2").Range("G7").Value

End With
----------------------------------------
Note:
I'm using Access VBA.

Thanks a lot to ajetrumpet for a great help!

By the way, the way of saving data to Access DB
is up to you...hee!

 

[ajetrumpet]

i'm glad you got it. Google works well, doesn't it? I actually had no idea how to do this. I learned something here too. Congrats to both of us.

 

[keirnus]

i'm glad you got it. Google works well, doesn't it? I actually had no idea how to do this. I learned something here too. Congrats to both of us.

hehehe!

i also wanna thank bytes.com for this forum.
it helped me a lot today.

i hope the likes of ajetrumpet will continue helping the likes of me.