Not sure whether to post to this forum or excel, so I posted to both.
Background
We receive from our vendor data in an excel file that lists PO number, Date and value. I have outlook (via a rule) save the file to a network drive. In access, I have a button that is to start a process that opens the excel, reads each line, finds the record in access and writes the vendor data to the invoice table.
Problem
When I read a line and assign the values to variables, the data stored in the variables are different from what's displayed in the Excel Window. I think the vendor uses the same file, writes over the data and then saves the file.
I then wrote the code to copy the data from the vendor file, open a new file and paste the data into the new file; however, the data is not the same.
Any help would be greatly appreciated on how to get paste this hurdle.
My code is:
Background
We receive from our vendor data in an excel file that lists PO number, Date and value. I have outlook (via a rule) save the file to a network drive. In access, I have a button that is to start a process that opens the excel, reads each line, finds the record in access and writes the vendor data to the invoice table.
Problem
When I read a line and assign the values to variables, the data stored in the variables are different from what's displayed in the Excel Window. I think the vendor uses the same file, writes over the data and then saves the file.
I then wrote the code to copy the data from the vendor file, open a new file and paste the data into the new file; however, the data is not the same.
Any help would be greatly appreciated on how to get paste this hurdle.
My code is:
Code:
Set xlApp = New Excel.Application
Set xlWrkBk = xlApp.Workbooks.Open(thePath & FileName)
Set xlsht = xlWrkBk.Worksheets(1)
xlApp.Visible = True
lastRow = ActiveSheet.Cells(Rows.count, "B").End(xlUp).Row
xlWrkBk.Worksheets(1).Range("A4:G" & lastRow).Copy
'xxxx OPEN New WorkBook and paste data --> needed to do this because when reading original workbook, got screwy results- believe metadata is messed up
Set xlWrkBkNew = xlApp.Workbooks.Add
Set xlshtNew = xlWrkBkNew.Worksheets(1)
xlApp.Visible = True
xlshtNew.Cells(1, 1).Select
xlshtNew.Paste
start = 1
lastRow = ActiveSheet.Cells(Rows.count, "B").End(xlUp).Row
For i = start To lastRow
[INDENT]PONum = xlshtNew.Cells(i, 5).value
InvAmt = xlshtNew.Cells(i, 6).value
PODate = xlshtNew.Cells(i, 7).value
WMIInv = xlshtNew.Cells(i, 2)
<Code to find and post to access extracted>
[/INDENT]Next
'xxxx close first file
xlWrkBk.Close SaveChanges:=False
'xxxx close working file
xlWrkBkNew.Close SaveChanges:=False
xlApp.Quit
Set xlApp = Nothing
Set xlshtNew = Nothing
Set xlWrkBkNew = Nothing
Set xlApp = Nothing
Set xlsht = Nothing
Set xlWrkBk = Nothing
rst.Update
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Exit Sub