I HAVE A WORKBOOK THAT IS CONNECTED WITH AN ODBC. I RECORDED A MACRO THAT REFRESHES THE SHEET, FILTERS THE SHEET, COPIES THE RESULTING DATA TO A TEMPLATE, AND SAVES IT AS A CSV. THE MACRO GOES THROUGH EVERYTHING FINE AND SAVES AS A CSV. BUT, WHEN I GO TO THE DBMS AND CHANGE SOME OF THE DATA TO TEST IF THE EXCEL SHEET PICKS UP ON IT, THE CHANGE IS MISSING FROM THE RESULTING DATA THAT IS SAVED AS A CSV. BUT, WHEN THE MACRO IS DONE RUNNING, THE SHEET THAT IS CONNECTED TO THE OBDC GETS THE CHANGE I MADE IN THE DBMS. SO, IT SEEMS AS IF WHEN RUNNING THE MACRO, I DO NOT GET THE DATA IN THE SAVED CSV. BUT ONCE THE MACRO IS DONE, IT IS ON THE EXCEL SHEET THAT IS CONNECTED TO THE DBMS.


I AM NOT TOO SURE WHY THIS IS. BUT I THOUGHT SOMEONE MAY BE ABLE TO HELP ME OUT.

BELOW IS THE VBA CODE.


Sub RefreshTest4()
'
' RefreshTest4 Macro
'

'
Range("Table_Query_from_PFWCW[[#Headers],[VM_STA]]").Select

Application.Wait Now + TimeSerial(0, 0, 5)

ActiveWorkbook.RefreshAll

Application.Wait Now + TimeSerial(0, 0, 30)

ActiveSheet.ListObjects("Table_Query_from_PFWCW").Range.AutoFilter Field:=1, _
Criteria1:="A"

Application.Wait Now + TimeSerial(0, 0, 10)

ActiveSheet.ListObjects("Table_Query_from_PFWCW").Range.AutoFilter Field:=2, _
Criteria1:="01"

Application.Wait Now + TimeSerial(0, 0, 10)

ActiveSheet.ListObjects("Table_Query_from_PFWCW").Range.AutoFilter Field:=12 _
, Criteria1:="<>#VALUE!", Operator:=xlAnd

Application.Wait Now + TimeSerial(0, 0, 10)

Range("A3").Select

Application.Wait Now + TimeSerial(0, 0, 5)

Range(Selection, Selection.End(xlToRight)).Select

Application.Wait Now + TimeSerial(0, 0, 5)

Range(Selection, Selection.End(xlDown)).Select

Application.Wait Now + TimeSerial(0, 0, 5)

Selection.Copy

Application.Wait Now + TimeSerial(0, 0, 5)

Workbooks.Open Filename:= _
"\\fps2\Scratch\PFW to SalesForce\Salesforce Vendor Upload\Vendor Update Template.xlsx"

Application.Wait Now + TimeSerial(0, 0, 10)

ActiveSheet.Paste

Application.Wait Now + TimeSerial(0, 0, 5)

Application.CutCopyMode = False

Application.Wait Now + TimeSerial(0, 0, 5)

ActiveWorkbook.SaveAs Filename:= _
"\\fps2\Scratch\PFW to SalesForce\Salesforce Vendor Upload\Vendor Teplate to Dataloader\Vendor Update Template.csv" _
, FileFormat:=xlCSV, CreateBackup:=False
End Sub