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?

 

[SkipVought]

hi,

I am confused. You already have a workbook that updates 15 queries when it opens, right?

So why do you need to OPEN these external files?

Skip,

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

 

[harryhoudini66]

The information actually does not update unless the CSV files are open.

 

[SkipVought]

So when you stated, " When the Excel file is opened, it updates information from 15 CSV files," that that was not correct?

Please explain the process by which this update occurs when the Excel workbook opens. Please be as detailed as necessary to make it clear.

Skip,

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

 

[harryhoudini66]

Here are the steps:

1) First go to folder that contains 15 CSV Files and open them all.
2) Next go to folder that contains Excel file and open that. Excel file reads from the opened CSV files and updates data.
3) Close out CSV Files one by one.
4) Save Excel file and close it

 

[SkipVought]

HOW does this workbook read the files?

DETAILS PLEASE!

Skip,

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

 

[harryhoudini66]

Sorry, I did not create the workbook nor do I know Excel so I am afraid I dont have much more to offer.

When the CSV files are already open, when you open the Excel file, the information updates automatically. The Excel file has links to each of these external CSV files. I believe Excel cells referance individual cells within each of the CSV files. I confirmed this by going to Data and then selecting Edit Links. It then shows each of the CSV files that are linked.

Now in case you are wondering why I open the CSV file first before the Excel file, that is because Excel will complain if done in reverse. For example, if I open Excel first, Excel says the document contains links to other sources and offers to update them. When I click OK, Excel complains that the links could not be updated and then offers the option to Edit the links. When I review the links, the status shows as Warning: Open source to update values.

Thanks in advance for your help.

 

[Geates]

I think the confusion is within for what you are asking. This statment

When the Excel file is opened, it updates information from 15 CSV files that are in another shared folder

answers what you are asking:

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?

I assume you would like help writing a script that opens the 15 csv and updates the content of the excel file. Is that correct?

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[harryhoudini66]

Yes, that is correct. I realize the first sentence quoted can be misleading since I did not explicitly state the 15 CSV files are already opened. My bad. The second sentence said exactly what I am looking to do and in which order it needs to be done.

Thanks again.

 

[SkipVought]

This is NOT standard operating protocol for updating querytables!

There are several ways that this could be accomplished in Excel VBA (this is NOT VB Script!)

1. Each Import Query Paremeter could be changed to updata on open.

2. You could loop thru each sheet in the workbook and each QueryTable in each sheet and REFRESH the querytable.

But in either case, the source data files do NOT need to be open!

Skip,

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

 

[harryhoudini66]

Thanks for the feedback. I understand this is not standard but as I mentioned, I did not create the file nor do I know Excel. I am sure there are better ways to do things but I am working with what I have. I was purposely vague in the beginning because I knew that if I included more details, people would question the "protocol" rather than answer the request.

Therefore, the request is as follows-

1) Open all CSV files in a specific folder
2) Then open Excel file which is another folder (update will happen automatically since CSV files are already open. No prompt will display)
3) Save/Close Excel file
4) Close out all open CSV files

Thank you all in advance for your help.

 

[SkipVought]

The procedure asks for the user to select ANY file in the FOLDER of interest, and then proceeds to OPEN each one IN EXCEL.

Sub testit()
    Dim oFSO As Object, oFile As Object, OpenPath, i As Integer, sPath As String
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    OpenPath = Application.GetOpenFilename("Text Files (*.csv), *.csv")

    OpenPath = Split(OpenPath, "\")
    
    For i = 0 To UBound(OpenPath) - 1
        sPath = sPath & OpenPath(i) & "\"
    Next
    
    sPath = Left(sPath, Len(sPath) - 1)
    
    For Each oFile In oFSO.GetFolder(sPath).Files
        With oFile
            If Right(.Name, 3) = "csv" Then
                Workbooks.Open .Name
            End If
        End With
    Next
End Sub

Skip,

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

 

[Geates]

I must play the Devil's Avocate. Although, this is something that is easily accomplished in VBA, it can also be accomplished, with some relative difficulty, in VBS. The OP asked for a VBS script so he correctly posted here. I do not feel as though any protocol was violated.

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[SkipVought]

Seems Rube Goldberg to me.

Skip,

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

 

[harryhoudini66]

Thank you. So this script would only open the CSV files in the specififed folder? It would not open the Excel file which is located in a different folder right? Please remember I am a newbie so be kind with me

I tried the script and get an error referencing line 2 character 12

Thanks again.

 

[harryhoudini66]

Skip, it is but please help if you can. I am a complete newbie when it comes to VBS and dont know Excel. Honestly it intimidates me. This thread is getting bigger and I still don’t have an answer.

I am self-thought and will search and search for an answer before asking for help. Beeen doing this for the last three days. I am very good at reverse engineering stuff once I see working examples. My strengths are in computer hardware, software, and telecommunications. I don’t know programming whatsoever as simple as it may be to some. I frequent many tech sites and posts assistance when I can. When doing so, I always try to answer the question asked even if the reason behind it does not always make sense to me. I try not to let my personal opinion influence the OP but will share it. All while answering the request at hand.

There is really more to the story for my request. I will share it with you but in all honesty, I feel it was irrelevant to getting the answer to my question as I asked it. Maybe it will help you to understand where I am coming from?

Using Avaya CMS Supervisor R16, I created some scripts that take VDN phone call data and export them in to CSV files (It cannot export in other format). The CSV files are automatically placed on a shared folder on one of the file servers. We then open an Excel sheet that is in a separate folder in the same fileserver . This Excel file contains formulas that read specific cells in the generated CSV files. The Excel file creates some data along with graphs based on this information. The Excel file is then e-mailed to members of management for review. This is done four times per day.

The main reason for me wanting to automate most of this is user error. We have trained key reps to run the scripts that generate the CSV files, open the Excel file, confirm data is accurate and e-mail the report with specific format in mind. No matter how much training, someone always screws up somewhere. Like you said, because it is a very Rube Goldberg method. Unfortunately, I don’t have much of the choice given the limitations. If there was an easier way and I had the knowledge and resources, I would jump on it in a heartbeat. In fact, I will be looking in to it once I "patch" things up forthe time being.

I have now scheduled the Avaya scripts to run at specific intervals. I have also found a way to automate the sending of the e-mail and attaching the said report with the necessary formatting. I am stuck with the middle piece. The part where the Excel file updates automatically with the information from the CSV files and thus, why I am here asking for help.

 

[SkipVought]

Sorry, I posted VBA that must be run IN Excel.

Someone else would have to advise you regarding running script completely OUTSIDE Excel.

Ideally, this entire process would be encapsulated in Excel, and would execute when the workbook opens

Skip,

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

 

[guitarzan]

Try something like this?

Option Explicit
Dim fso, f, sFile, sCSVPath
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)
   Dim xl
   Set xl = CreateObject("Excel.application")

   xl.Application.Workbooks.Open sFilename
   xl.Application.Visible = True

End Sub

 

[SkipVought]

This Excel file contains formulas that read specific cells in the generated CSV files.

NOT a very good way to run a railroad!

It seems that the .csv files are LINKED via cells. Not at all reliable, IMHO.

You would be much better off (no VB Script or VBA requied) to use the Excel Data IMPORT (Query Table) feature, to IMPORT the data from each file (hopefully structured as a TABLE) into a separate sheet. This would be a ONE TIME effort. There is a way to set a switch to REFRESH AUTOMATICALLY without prompts when the workbook is opened.

As I stated previously, I can't really provide any cogent help with VB Script.

Skip,

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

 

[harryhoudini66]

Thanks so much for the help. I agree about doing things differently. Lots of mistakes here and there. Will work on making things smoother.

Guitarzan, it looks like this might work. I did a quick test. I will confirm shortly. Have some fires I need to take care of right now. Thanks again.