I have a script that loops over a folder structure and opens a file to update the linked cells and close it so the file does not always have to be open as it is a intermediate between 2 spreadsheets that contains other information that is still needed. I can get the file to open and close properly but the values do not update. If I open the file manually it automatically updates.
Here is my code if anyone can suggest any possible solutions I would appreciate it. The pause is only in there so I could verify if the cell was being updated.
<code>
Option Explicit
Dim strFolderToSearch, objFSO, objRootFolder, objFolder, colSubfolders, strOutput
Dim oXL, oFolder, aFile, FSO, parts, waitTime, PauseTime, Start
Set oXL = CreateObject("Excel.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")
strFolderToSearch = "<folder path>"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objRootFolder = objFSO.GetFolder(strFolderToSearch)
Set colSubfolders = objRootFolder.SubFolders
For Each objFolder in colSubfolders
Set oFolder = FSO.GetFolder(strFolderToSearch + objFolder.name)
For each aFile in oFolder.Files
PauseTime = 5 'Set Duration
Start = Timer 'Set start time
parts = split(aFile.name, ".")
if parts(0) = objFolder.name then
oXL.Workbooks.Open(strFolderToSearch + objFolder.name + "\" + aFile.Name)
oXL.Visible = True
oXL.ActiveWorkBook.RefreshAll
Do While Timer < Start + PauseTime
'nothing happening
Loop
oXL.ActiveWorkBook.Save
oXL.ActiveWorkBook.Close
end if
Next
Set oFolder = Nothing
Next
</code>