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
Bookmarks