0

I got this code from a user on here but i wasnt sure if i was allowed to post on their question as i was asking another question of that code that was posted... sorry i am new...

here was the question:

How can I use VBScript to effectively refresh the external data of several Excel spreadsheets?

here was the code: 

Sub RefreshAllExcelInFolder()
Dim fso
Dim ObjFolder
Dim ObjFiles
Dim ObjFile
Dim objExcel

'Creating File System Object
Set fso = CreateObject("Scripting.FileSystemObject")
'Getting the Folder Object
Set ObjFolder = fso.GetFolder("S:\Finance & Performance\IM&T\Information\Surgicaldir\18     Weeks\18wksPTL\validation")

'Getting the list of Files
Set ObjFiles = ObjFolder.Files
    'On Error Resume Next
    For Each ObjFile In ObjFiles
        If LCase(Right(ObjFile.Name, 5)) = ".xlsx" Or LCase(Right(ObjFile.Name, 4))     = ".xls" Then
            Workbooks.Open(ObjFile).Activate
            RefreshAllData
        End If
    Next
End Sub

Sub RefreshAllData()
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close
MsgBox ("Going back In")
End Sub

i get this message which after pressing OK works perfect - but is there any way not to get this message? this action will cancel a pending refresh data command. continue? thanks liz

exceliz
  • 1
  • 1
  • You get a message? Does it say "_Going back In_"? Just comment out the line `MsgBox ("Going back In")` in the `RefreshAllData` subroutine. – Bond Jul 21 '14 at 13:11
  • Welcome to Stack Overflow. You are right to ask a *new* question rather than posting on the old question. However, if the original question or its answers are relevant, it's useful to include a link back to them too. – drs Jul 21 '14 at 13:13
  • @Bond, it's not very clear in the post, but the message is "This action will cancel a pending refresh data command. Continue?" – Doug Glancy Jul 21 '14 at 13:42
  • @DougGlancy, ah okay. That makes more sense. =) – Bond Jul 21 '14 at 13:53

2 Answers2

1

RefreshAll runs asynchronously so your Close command is probably trying to close the workbook while it's still being refreshed. Try some of the solutions here or here.

Community
  • 1
  • 1
Bond
  • 16,071
  • 6
  • 30
  • 53
0

Go to each of your Connections, select Properties and then deselect the Background Refresh option.

I believe that disabling this will force your script to wait until all connections are refreshed before continuing.

user3023856
  • 1