Problem automating a macro to update external data

Duni454

New Member
Joined
Nov 7, 2011
Messages
4
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. :)
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Duni454

New Member
Joined
Nov 7, 2011
Messages
4
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

New Member
Joined
Nov 7, 2011
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,315
Members
414,053
Latest member
Dual Showman

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
Top