Open Excel File and Update Data From External CSV Files and Save/Close

[harryhoudini66]

I am a complete newbie when it comes to VBS but have been reading up on it for the last few days. I have found it very helpful in making things easier and minimizing user error. I have searched and searched but could not find the answer I was looking for so I hope someone here can help.

I have an Excel File (xlsx) in a shared location. When the Excel file is opened, it updates information from 15 CSV files that are in another shared folder.

Can someone assist me with a VBS script that would open all the CSV files in a specific folder, then open the Excel file so that it can update? Finally Save and Close the Excel File as well as the CSV files?

 

[harryhoudini66]

Guitarzan, it does work. Thank you so much. The only thing is it did not save and then close the Excel document. I would also need it to close the csv files.

Thanks again.

 

[harryhoudini66]

Looks like I spoke too soon. While the script does open the CSV files and then the Excel file, the file does not update. It is like Excel does not know the CSV files are opened. I tested this by closing only the Excel file while the CVS remained open. Excel complained that in order to update the information, the CSV files had to be opened.

For whatever reason, it looks like the script openinging the CSV files made them invisible to Excel. Very weird. Any idea?

 

[SkipVought]

While the script does open the CSV files and then the Excel file, the file does not update

YES! TRUE!

Each workbook is in a DIFFERNT INSTANCE OF EXCEL!

That is what

Set xl = CreateObject("Excel.application")

does.

Different instance -- completely blind.

I'd try this way...

Option Explicit
Dim fso, f, sFile, sCSVPath[b], xl

Set xl = CreateObject("Excel.application")
[/b]
sCSVPath = "C:\FolderWhereCSVFilesAre"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(sCSVPath)
For Each sFile in f.Files
   If UCase(fso.GetExtensionName(sFile.Name)) = "CSV" Then
      OpenFileInExcel sFile.Path
   End If
Next

OpenFileInExcel "C:\Path\Myspreadsheet.xls"

Sub OpenFileInExcel(sFilename)
   xl.Application.Workbooks.Open sFilename
   xl.Application.Visible = True

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[harryhoudini66]

Thanks Skip. The change did it. Now when the Excel file opens, it will update. I appreciate this so much. Now the only thing pending is to have the script save the change to the Excel file and then close it out and the CSV files.

Thanks again!

 

[SkipVought]

Please post the code that includes where you OPEN the Excel workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[harryhoudini66]

Here is the code with the info included. I need the MSCInteReportvWindows_7.xlsx changes to save and then close. Then also close out all the CSV files that were open as well.

Option Explicit
Dim fso, f, sFile, sCSVPath, xl

Set xl = CreateObject("Excel.application")

sCSVPath = "\\fileserver2\MSC\MSC_Reports\Interval\Output"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(sCSVPath)
For Each sFile in f.Files
If UCase(fso.GetExtensionName(sFile.Name)) = "CSV" Then
OpenFileInExcel sFile.Path
End If
Next

OpenFileInExcel "\\fileserver2\MSC\MSC_Reports\Interval\MSCInteReportvWindows_7.xlsx"

Sub OpenFileInExcel(sFilename)
xl.Application.Workbooks.Open sFilename
xl.Application.Visible = True

End Sub

 

[SkipVought]

Option Explicit
Dim fso, f, sFile, sCSVPath, xl
'[b]
Dim wb
'[/b]
Set xl = CreateObject("Excel.application")

sCSVPath = "\\fileserver2\MSC\MSC_Reports\Interval\Output"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(sCSVPath)
For Each sFile In f.Files
    If UCase(fso.GetExtensionName(sFile.Name)) = "CSV" Then
        OpenFileInExcel sFile.Path
    End If
Next

OpenFileInExcel "\\fileserver2\MSC\MSC_Reports\Interval\MSCInteReportvWindows_7.xlsx"

Sub OpenFileInExcel(sFilename)
'[b]
Set wb = xl.Application.Workbooks.Open(sFilename)
'[/b]
xl.Application.Visible = True

End Sub

'[b]
Sub SaveAndCloseWkbk(sFilename)
    xl.DisplayAlerts = False
    wb.Save
    wb.Close
    Set wb = nothing
End Sub
'[/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[harryhoudini66]

Thanks Skip. It did not work. The Excel file remained open as well as the CSV files. I dont think the save took either because when I closed the Excel sheet manually, it asked if I wanted to save.

 

[harryhoudini66]

I think I used the wrong term and that is why this is not working. The Excel file contains a "worksheet" rather than a "workbook". Not sure how much of the differance if any, it makes with the script. The worksheet is named "Totals". Hope this helps.

 

[SkipVought]

No, its a WORKBOOK that need to be CLOSED.

You must CALL this subroutine from your main

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[harryhoudini66]

Thanks Skip. Any idea why it is not saving and closing?

 

[SkipVought]

Where did you call SaveAndCloseWkBk

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[harryhoudini66]

Sorry, you lost me. Newbie remember. I have the code just like I listed it above. Was I supposed to enter additional information elsewhere? The workbook just needs to save in the same location.

 

[SkipVought]

Option Explicit
Dim fso, f, sFile, sCSVPath, xl
'
Dim wb
'
Set xl = CreateObject("Excel.application")

sCSVPath = "\\fileserver2\MSC\MSC_Reports\Interval\Output"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(sCSVPath)
For Each sFile In f.Files
    If UCase(fso.GetExtensionName(sFile.Name)) = "CSV" Then
        OpenFileInExcel sFile.Path
    End If
Next

OpenFileInExcel "\\fileserver2\MSC\MSC_Reports\Interval\MSCInteReportvWindows_7.xlsx"
'[b]
'sorry that I missed this...
xl.DisplayAlerts = False
For Each wb In xl.Workbooks
    With wb
        Select Case UCase(Right(.Name, 3))
            Case "CSV"
            Case Else
                wb.Save
        End Sub
        wb.Close
    End With
Next
'[/b]
Sub OpenFileInExcel(sFilename)
'
Set wb = xl.Application.Workbooks.Open(sFilename)
'
xl.Application.Visible = True

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[harryhoudini66]

When I run the script, it generates an error:

Line: 28
Char: 13
Error: 'Select'
Code: 800A03FE
Source: Microsoft VBScript compilation error

Thanks again.

 

[SkipVought]

then try this

    With wb
        If UCase(Right(.Name, 3)) <> "CSV" then
           .Save
        End if
        .Close
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[harryhoudini66]

Wow, we are alsmot there. The Excel file did save and the CSV files closed as well. Only thing is Excel remained open. Do we need to kill the Excel process? Not that I would know how to do it via VBS script.

 

[SkipVought]

xl.quit

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[harryhoudini66]

You are the man. Thank you so so so so so much. I really appreciate your help and patiance.

 

[SkipVought]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}