Excel copied & pasted text in VBA is different !?!

[Slimat]

Hi All

This is slightly convoluted - but I'll do my best to explain whats happening...

I am using Excel Macro's to extract data from our Sage database using an ODBC connection, then manipulating the data and saving it as a CSV file to be uploaded into our online SQL database.

So, the spreadsheet opens (hourly) and the VBA code is executed. After an Office upgrade & a Sage update everything stopped working. So I started to recreate the sheet and it seems to be working. Except I notices that the Sage data which is being copied and pasted is different and this worries me that there may be more serious errors which I havent noticed yet.

The data is product codes, part numbers and quantities in stock... I have proved that the generated CSV is not an exact copy of the Sage data by changing a product descripion from "Product" to "Productxxx"... when I run the script the CSV file still shows the description as "Product"... but the data in the Excel file is being refreshed to show "Productxxx" - so it is as though the script is running on and saving the CSV file before the refresh has completed. During my research into this I saw suggestions of inserting "DoEvents" after the "ActiveWorkbook.RefreshAll" command - but this didnt seem to change anything. I even added "ActiveWorkbook.Save" in the hope that the correct data would pull through every second time the VBA ran... but no, this didnt work either

The code I am using is as follows;

Sub Sort()
'
' Sort Macro
'
' Keyboard Shortcut: Ctrl+s
' Macro to ignore prompt boxed
    Application.DisplayAlerts = False
'
' Refresh workbook
    ActiveWorkbook.RefreshAll
'    DoEvents
'    ActiveWorkbook.Save
'
'
' Select Last Sale & Last Purchase Date Columns and set to UK format
    ActiveSheet.Range("A" & Rows.Count - 1).Offset(0, 7).End(xlUp).Select
    Range(Selection, "G2").Select
    Selection.NumberFormat = "dd/mm/yyyy;@"
'
'
' Find out number of populated rows
    Dim Last_Row As Long
    Last_Row = Cells(Rows.Count, 1).End(xlUp).Row
'   MsgBox Last_Row
'
'
' Add todays date & time updated
    Range("M1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Date Checked"
    Range("M2").Select
    ActiveCell.Value = Now()
'
'
' Copy Date Cell
    ActiveCell.Copy
'
'
' Select M2 to bottom of sheet and paste date in
    Range("M2:M" & Last_Row).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
'
'
' Set Date column to UK format
    Selection.NumberFormat = "dd/mm/yy - h:mm AM/PM"
'
'
' Save as CSV file
    Application.CutCopyMode = False
    ChDir "T:\FRED\SQL_Database\Sage\Data To Upload"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        "T:\FRED\SQL_Database\Sage\Data To Upload\Sage_Stock.csv", FileFormat:= _
        xlCSVMSDOS, CreateBackup:=False
'
' Close workbook without saving
Application.DisplayAlerts = False
Application.Quit

End Sub

As you can see my code is not particularly slick but if anyone can offer any advice I would be very grateful.

 

[Slimat]

Oh, the data refresh takes about 10-15 seconds... just in case