Place a cell value in a field of an Access Table

[PALman]

Can anyone show me how to code the following...
I wish to take the value of a cell in an Excel spreadsheet and have it placed in a field of an Access Table?
In some instances the cell would hold the word/text "closed" to indicate the excel document was complete. So here I would want this condition recorded in the Access table field.
When I know how to do this I then need to have the code converted to VB6 to achieve the same, where DLookup is not recognised.
Any help much appreciated.

 

[Remou]

Here is an example:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& CurrentProject.Path & "\Tek-Tips.xls" _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
strsql = "SELECT * FROM [sheet1$]"

rs.Open strsql, cn
Debug.Print rs.Fields(0)

More information:

http://www.connectionstrings.com/

 

[PALman]

Thanks Remou
I have tried your code but wonder if I am getting it correct.
I think I need to know where to place this code so that when an Null value cell (6:10) in the excel file "222222-LPI.xls" is changed to a text value of "closed" then this is sent to Access database file QPSatus.db where the value of field LPIcomplete = True and checkbox on a form reflects this with a tick.
Here is what code I have to date but obviosly I could do with more help...

Private Sub chkFields_Click(Index As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strsql As String
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\000-QualityControlProgram\QC5.mdb;Persist Security Info=False" \ "222222-LPI.xls"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& "C:\000-QualityControlProgram\QC5.mdb" & "C:\000-QCProgram\JobsFolder\222222-LPI.xls" _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

strsql = "SELECT * FROM [sheet1$:6,10]"

rs.Open strsql, cn
Debug.Print rs.Fields(0)
End Sub

Any further help is very much appreciated
Thanks again

 

[PALman]

Is it possible to put the above code (corrected) on or behind the excel spreadsheet? I have never programmed using VB Excel.

 

[Remou]

The code is intended to run from Access. It opens an Excel sheet as a recordset, which means that you can do all the things you would normally do with a recordset.

You need to correct this:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\000-QCProgram\JobsFolder\222222-LPI.xls" _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

strsql = "SELECT * FROM [sheet1$6:10]"

You can also open a recordset from Access in Excel.

Your first post implied that you wished to work from Access, do you now wish to work from Excel?

 

[PALman]

Sorry Remou,
Yes it would appear to be best to work from Excel.
I wasn't too sure how to go about this initially.
Thanks again.

 

[PALman]

Hi Remou,
Can you tell me how or what I need to correct when you said I need to correct this...

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\000-QCProgram\JobsFolder\222222-LPI.xls" _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

strsql = "SELECT * FROM [sheet1$6:10]"

It would be great to get this Access to Excel code working first and then to go about reversing it for Excel to Access.
I shall need both.
Many thanks.

 

[Remou]

That was the correction I supplied. Does it work?

 

[PALman]

Sorry Remou
I am getting the following errors...

strsql = "SELECT * FROM [sheet1$6:10]" this gives error "Too many fields"

strsql = "SELECT * FROM [sheet1$:6,10]" this gives error "JET db can't find object '[sheet1$:6,10]'

And I am not sure how the following last two lines will place cell value into database field named LPIcomplete.
My Access Table is called QPStatus and the field LPIcomplete is for the JobNo field when it is = "222222"...
'rs.Open strsql, cn
'Debug.Print rs.Fields(0)

Sorry if Iam not explaining very well
Many thanks once again

 

[Remou]

Lets see if you can get attached to the sheet. Try:

strsql = "SELECT * FROM [sheet1$]"

And if that doesn't work, double check that there is a sheet called Sheet1 in 222222-LPI.xls. Perhaps the sheet is called Totals, or such like.

 

[PALman]

Hi Remou,
Your correct. I didn't realise the excel sheet was named "Examination" and not "Sheet1".
I shall rename this but it will be in about 7 hours from now.
My project is at home and I am working just now.
I am sure I will now be able to connect to sheet and if so would like to move to last part in placing the cell value into the field of the correct record/JobNo.
Many thanks

 

[Remou]

You can use a standard query:

[tt]strsql = "SELECT LPIcomplete FROM [Examination$] Where JobNo=222222"[/tt]

 

[PALman]

Hi Remou,
That looks like the answer but as I said earlier it will be a few hours before I can try it.
Just one question though... Do I need to insert the cell location 6:10 (F10) into the sheet name like so... [Eamination$, 6:10]
Thanks again ( I feel a star coming on)

 

[PALman]

Remou
I had to leave project over the last week however I am back at it.
The latest I have now is when connecting to the spreadsheet I get the following error...
"No value given for one or more required parameters".
Any thoughts?
Thanks

 

[Remou]

Please post the code as modified for your application.

 

[PHV]

And what about OLE Automation ?
Dim objWB As Object, varLPI
Set objWB = GetObject("C:\000-QCProgram\JobsFolder\222222-LPI.xls")
varLPI = objWB.Worksheets("Examination").Range("F10").Value
objWB.Close False
Set objWB = Nothing
MsgBox "LPIcomplete = " & varLPI

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

Remou and PHV,
Hi guys, here is what I have to date. I have put the two pieces of code each of you have given me behind two control buttons...
Remou..
Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strsql As String
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\000-QControlProgram\JobsFolder\222222-LPI.xls" _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

'strsql = "SELECT * FROM [sheet1$:6,10]"
strsql = "SELECT LPIcomplete FROM [Examination$] Where JobNo=222222"

rs.Open strsql, cn
Debug.Print rs.Fields(0)
End Sub
rs.Open strsql, cn... Stops program with error "No value given for one or more required parameters".

PHV...
Private Sub Command2_Click()
Dim objWB As Object, varLPI
Set objWB = GetObject("C:\000-QControlProgram\JobsFolder\222222-LPI.xls")
varLPI = objWB.Worksheets("Examination").Range("F10").Value
objWB.Close False
Set objWB = Nothing
MsgBox "LPIcomplete = " & varLPI
End Sub
The line varLPI = objWB.Worksheets("Examination").Range("F10").Value stops with error "Subscript out of range"

I think we are getting the connection but the last couple of lines in both codes needs to be looked at.
Many thanks to both of you,

 

[Remou]

Please post a sample of data from the Excel sheet and the range for the sample data.

 

[PHV]

Subscript out of range
Replace Examination with the real sheet name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

Hi Remou and PHV,
Remou...
The Excel sheet holds data like Job Numbers (in cell J:6 example 222555), Part Numbers (B:4 example P123456) and a object/signature in cell J:48 to say the Test to which this document refers has been completed and is signed off which completes the document.
It is this cell's content I wish to have saved into a field in my database. If I cannot save the actual object then I could use cell underneath J:49 and have the technician enter the wording "closed" and then hopefully I could save this into the database field named DocAvailability. Then my coding would read something like...
If DocAvailability = True (or "closed") then CheckBox = True placing and saving a tick in a checkbox bound to the field.

PHV...
Your latest post has now got my program running.
The only thing I would like to do now is, as I have just said above, is instead of printing the value of varLPI in a message box, I want to save it into the DocAvailability field of my database called QPStatus.db and to show that the document has been closed by displaying a tick in a CheckBox. The full coding is as follows...
Private Sub Command2_Click()
Dim objWB As Object, varLPI
Set objWB = GetObject("C:\000-QCProgram\JobsFolder\222222-LPI.xls")
varLPI = objWB.Worksheets("Examination").Range("J6").Value
objWB.Close False
Set objWB = Nothing
MsgBox "LPIcomplete = " & varLPI
End Sub
I cannot thank both you guys enough.
I am nearing completion of my project and with a little more help that will be sooner rather than later.
Thank you both very much