Problem automating a macro to update external data

[Duni454]

I've been searching for a solution to a problem for a little while now. I have a macro which switches between 2 workbooks, pasting in an account number to the second which then updates the data through SQL when done manually. The problem is when I do this through VBA code, the data doesn't refresh even though it at first appears to (in that the timer starts and says updating, then stops but doesn't actually change the figures). I have tried a line of code to "pause" the macro for 10 seconds, and I've tried a ".RefreshAll" line but neither work for me. Any help would be very much appreciated. Thanks.

 

[Derek Brown]

Have a look at my reply (Post #2) at:
http://www.mrexcel.com/forum/showthread.php?t=552502&highlight=refreshall
Does that help?

 

[Duni454]

Many thanks for getting back to me on this. I've unticked Enable Background Data, and used the ActiveWorkbook.RefreshAll command but this seems to just sit in limbo with an "updating data..." message that doesn't seem to end. Perhaps if I post the code below you may have a better idea, I've added in a comment in CAPS to show where I need the command.
Apologies for the lack of knowledge I'm fairly new to macros and VBA!
Thanks again for your help.



Sub LoopListMancBills()
'
' ListMancBills Macro
'
Do

Workbooks.Open Filename:= _
"G:\Administration\Finance\Database\ACCOUNTS\LSC\Manchester Work\Mark's\Manchester LSC Billing.xlsm"
Sheets("Bill Template").Select
Range("L4").Select
Windows("Accounts To Bill.xlsm").Activate
ActiveCell.Select
Selection.Copy
Windows("Manchester LSC Billing.xlsm").Activate
ActiveSheet.Paste
Application.CutCopyMode = False


Range("G16").Select


'NEED REFRESH CODE HERE, .REFRESHALL DOESN'T SEEM TO WORK. THE DATA IS FROM SQL SO PERHAPS THERE IS A DIFFERENT COMMAND?


Workbooks.Open "G:\Administration\Finance\Database\ACCOUNTS\LSC\LB Bill Numbers To Use.xls"
Range("B1").Select
Do Until ActiveCell <> "Y"
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell = "Y"
ActiveCell.Offset(0, -1).Select
ActiveWorkbook.Save
ActiveCell.Copy
ActiveWorkbook.Close

ActiveSheet.Paste
ActiveCell.Offset(-2, 0).Select
Selection.Copy

Workbooks.Open "G:\Administration\Finance\Database\ACCOUNTS\LSC\LB Bill Numbers To Use.xls"
ActiveCell.Offset(0, 2).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveCell.Offset(-1, 0).Select
Selection.Copy

Workbooks.Open "G:\Administration\Finance\Database\ACCOUNTS\LSC\LB Bill Numbers To Use.xls"
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste

ActiveCell.Offset(0, -2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select


ActiveCell.Offset(-2, 1).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select


ActiveCell.Offset(-1, 1).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select

ActiveWorkbook.Save
ActiveWorkbook.Close

Range("G16").Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Dim strYear, strMonth, strDay, strNumber, strPath, strDefpath, strAcc, strClient, strToday As String
On Error Resume Next
strYear = Range("H1").Text
strMonth = Range("I1").Text
strDay = Range("J1").Text
strNumber = Range("G16").Text
strAcc = Range("L4").Text
strClient = Range("L9").Text
strToday = Range("H2").Text
strDefpath = "G:\Administration\Finance\Database\Processed Bills\"
If IsEmpty(strYear) Then Exit Sub
If IsEmpty(strMonth) Then Exit Sub
If IsEmpty(strDay) Then Exit Sub
If IsEmpty(strNumber) Then Exit Sub
MkDir strDefpath & strYear
MkDir strDefpath & strYear & "\" & strMonth
MkDir strDefpath & strYear & "\" & strMonth & "\" & strDay
MkDir strDefpath & strYear & "\" & strMonth & "\" & strDay & "\" & strNumber
strPath = strDefpath & strYear & "\" & strMonth & "\" & strDay & "\" & strNumber & "\" & strClient & " - " & strAcc & " - " & strToday
ActiveWorkbook.SaveAs Filename:=strPath, _
FileFormat:=52, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

Windows("Accounts To Bill.xlsm").Activate

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))


End Sub

 

[Duni454]

Found it! The following works fine for this.

Thanks again for your help.



Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub LoopListMancBills()
'
' ListMancBills Macro
'
Do

Workbooks.Open Filename:= _
"G:\Administration\Finance\Database\ACCOUNTS\LSC\Manchester Work\Mark's\Manchester LSC Billing.xlsm"
Sheets("Bill Template").Select
Range("L4").Select
Windows("Accounts To Bill.xlsm").Activate
ActiveCell.Select
Selection.Copy
Windows("Manchester LSC Billing.xlsm").Activate
ActiveSheet.Paste
Application.CutCopyMode = False


Range("G16").Select

Range("Table_MarkTimeEntriesLSC[WorkDate]").ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("Table_ExternalData_1[[#Headers],[Number]]").ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("Table_ExternalData_14[[#Headers],[Column12]]").ListObject.QueryTable.Refresh BackgroundQuery:=False

DoEvents
Sleep 400