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

Slimat

New Member
Joined
Mar 27, 2014
Messages
33
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;

VBA Code:
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,224,226
Messages
6,177,277
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top