Excel Data from file different than display in Excel (1 Viewer)

[twcaddell]

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:

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

 

[namliam]

Why open an excel file in the first place?
It would be much simpler to import the whole spread sheet into a staging table (like a temporary table)
Then from that staging table, run some update/append queries and clear out your staging table....

 

[Mihail]

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

What you see (in Excel) is rarely what a cell contain.
First because the cell's formats, then because the cells sizes.

I think the vendor uses the same file, writes over the data and then saves the file

Don't be very sure.

 

[twcaddell]

Why open an excel file in the first place?
It would be much simpler to import the whole spread sheet into a staging table (like a temporary table)
Then from that staging table, run some update/append queries and clear out your staging table....

Namlian,
Thanks for the suggestion. Opened the file and read it because I didn't know you could do what you suggested. Will read up on it.
Again, thanks
TC

 

[twcaddell]

What you see (in Excel) is rarely what a cell contain.
First because the cell's formats, then because the cells sizes.


Don't be very sure.

The reason I say that is because the data matches a spreadsheet that was submitted in 2012.
Thanks for the info though
TC