Add New record from excel Cell

[jjcyeshua]

Hi gurus,

I need help/ideas/suggestions. I'm trying to automate a process that copy data from excel to Access.. but not all data in excel, just specific cells. I currrently have a form that i would like this process to be added. i was able to connect to excel file using ADO, but cant lock to specific cell (or get the values of cell).. need help guys.. thanks..

 

[chergh]

First add the reference to the microsoft excel object library. Then use this code as an example

Sub blah()

Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim path As String


Set xlApp = New Excel.Application

path = "c:\trash.xls"

Set wb = xlApp.Workbooks.Open(path)

Set ws = wb.Worksheets("Sheet1")

MsgBox (ws.Range("A1").Value)

xlApp.Quit

End Sub

Thats the basics of it. You will need to either change the path or create a workbook called trash in your c drive and make sure you have a value in cell A1.

 

[jjcyeshua]

Yes!!!!

Thank you.. worked perfectly.....

 

[boblarson]

First add the reference to the microsoft excel object library.

And if you use this then you won't need to set a reference:

Sub blah()

Dim xlApp As [color=red]Object[/color]
Dim wb As [color=red]Object[/color]
Dim ws As [color=red]Object[/color]
Dim path As String


Set xlApp = [color=red]CreateObject("Excel.Application")[/color]

path = "c:\trash.xls"

Set wb = xlApp.Workbooks.Open(path)

Set ws = wb.Worksheets("Sheet1")

MsgBox (ws.Range("A1").Value)

xlApp.Quit

End Sub

 

[jjcyeshua]

that's a good one.. thanks

 

[boblarson]

I typically will use the format that chergh posted while developing so that I can have early binding (so intellisense will show up while working with the code) while I am getting things ready. Then I will usually comment out my development part and enable the Dim x as Object parts and then uncheck the Excel reference and that way my code doesn't fail if someone is on an earlier version of Excel than I am.