Search for a record in Excel

[LadyDi]

I have recently built a database to keep track of all the inventory counts for our technicians. This process was originally in Excel and they wanted to move it to Access in order to automate it. They have also decided that they still want to maintain the original Excel schedule by adding dates each time a step is completed. I currently have code that will go to an existing spreadsheet and add data from the database (originally found at this website: http://www.btabdevelopment.com/ts/default.aspx?PageId=110). I was wondering if there is a way to modify this code so that it will look for a specific name in the spreadsheet, go to the end of that row and enter today's date. Is that possible? If so, what modifications would need to be made to the code to get it to work?

 

[ino_mart]

I would suggest to make a ADODB-connection towards the XLS and use SQL-statements.
This approach might not work for all workbooks due to some limitations. Don't use this if you want to get/write more than 255 charachters. Delete-statements neither work. You must add a $ after the sheetname

First of all, in the VBA-editor select Tools - References and add "Microsoft ActiveX Data Objects 2.8 Library".

Then create next subroutine

Private Sub XLS_Connection(ByRef x As ADODB.Connection, strFile as String)
Set x = New ADODB.Connection
With x
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & strFile & ";" & "Extended Properties=""Excel 8.0;imax=1;"""
    .Open
End With
End Sub

At the place where you want to add the date in the XLS-file use

dim cn as ADODB.Connection
dim strSQL as string
dim strExcel as string
 
set cn=New ADODB.Connection
strExcel="[I]place here the full path to your excel file including the extension"[/I]
XLS_Connection cn, strExcel  [I]'This will open the XLS as an ADODB-object[/I]
strSQL="update [[I]sheet[/I]$] set [[I]name of column where the date comes in][/I]=" & DateSerial(Year(Now), Month(Now), Day(Now)) & " where [[I]name of value to lookup[/I]]='" & [I][place here the Access fieldname or variable][/I] & "'"
cn.Execute strSQL

for e.g. In your Excel-file you have a worksheet named "Figures" with a column "Create Date" and a column with orders you have to lookup. Let's assume you want to fill in the Create Date for the record where order is 355. This order ID derives from your ms Access form from a field named txtOrder.

strSQL="update [Figures$] set [Create Date]=" & DateSerial(Year(Now), Month(Now), Day(Now)) & " where [order] = " & me.txtOrder

Beware: in this example me.txtOrder is a number field so we don't use the apostrophes. If the order ID is not a numeric value (eg Z355) the line becomes
strSQL="update [Figures$] set [Create Date]=" & DateSerial(Year(Now), Month(Now), Day(Now)) & " where [order] = '" & me.txtOrder & "'"

The reason why to use the DateSerial-command and not the Now() command has to do with Regional Settings. If you use the regular Now-command or something like format(now(),"dd-mm-yyyy"), this fails sometimes and the date and month are switched in the Excel file.