Excel - Open a group of workbooks from a macro? 2

[sshowers]

Background:
In the middle of the night I have my data warehouse set to dump five .csv files to a set directory on one of my NT servers. At a pre-defined time, the NT server starts Excel and the "Auto Open" macro of the workbook (specified on the command line) copies a .csv file into the pre-formatted workbook and saves itself as a new file in a different directory before closing Excel. This process happens once for each .csv file.

Situation:
The number of .csv files is about to increase from five to fifteen (or, potentially, more). Rather than adding ten more instances of Excel to the scheduler on this NT server I would prefer a more "user friendly" solution.

Opportunity:
I see a master "automation" spreadsheet (containing a list of file names, directories, parameters, E-mail addresses, etc.) with its own "Auto Open" macro that will read and process the list -- one row at a time.

I created a workbook with the list of file names down column A and a macro that will read the list sequentially, quitting when it hits a blank line. In test I was able to put up a message box that contained the "file name" as it read each row. I then added the code to open each file and run the macro. It works perfectly on the first file in the list, but control never seems to come back to the master macro so it can move to the next file.

I'm sure you're going to want to see some of the code, but first I would just like to know if what I'm attempting is even doable. Is there a better / more obvious / readily available solution that would prevent me from "reinventing the wheel"?

Thanks, in advance, for any replies...

--sshowers

 

[Zathras]

Here is a simplified macro that does what you want. You should be able to build on it.

Option Explicit
Sub test()
Dim c As Range
Dim sCSVFileName As String
Dim sXLSFileName As String
  For Each c In ActiveSheet.Range("A2:A4")
    sCSVFileName = c.Text
    sXLSFileName = c.Offset(0, 1).Text    
    Workbooks.Open FileName:=sCSVFileName

    '
    'process the file
    '

    ActiveWorkbook.SaveAs FileName:=sXLSFileName, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close
  Next c
End Sub

Set up the worksheet this way:

A1: 'CSV File Name
A2: '1.csv
A3: '2.csv
A4: '3.csv
B1: 'XLS File Name
B2: '1.xls
B3: '2.xls
B4: '3.xls

As I said, it's very simplified (no error detection), so before running, be sure that the three .csv files exist and that the three .xls files don't.

 

[Gavona]

Certainly doable. The following works for example.

Sub openfiles()
'Select the range containing the filenames before this point
'I would use a named range
Dim c As Range
For Each c In Selection
Workbooks.Open Filename:="H:\Data\" & c.Text
'call sub routine to do your stuff
ActiveWorkbook.Close savechanges = False
Next c
End Sub

 

[sshowers]

Thank you, both, for your prompt replies (stars to be awarded). I have merged both approaches but am, however, experiencing the same as my initial test. The "automation" workbook calls the first file and runs its auto open query but control never comes back to the calling program to run the next file in the list. Here is what I have:

File: Automation.xls

Seq Directory         File
 1  C:\My Documents\  Buyer.xls
 2  C:\Temp\          SalesRep.xls

Macro: Autpen

Sub Auto_Open()

    Dim rRng As Range
    Dim sDir As String
    Dim sRun As String

    Range("A2").Select
    Range(ActiveCell.Address, ActiveCell.End(xlDown)).Select
    For Each rRng In Selection
        sDir = rRng.Offset(0, 1).Text
        sRun = rRng.Offset(0, 2).Text
        MsgBox "File: " & sRun, vbInformation + vbOKOnly, "Debug"
        Call OpenBook(sDir & sRun)
    Next rRng
End Sub

Function: OpenBook

Private Function OpenBook(RunFile As String)
On Error GoTo Err_OpenBook

    Workbooks.Open FileName:=RunFile
    ActiveWorkbook.RunAutoMacros xlAutoOpen

Exit_OpenBook:
    Exit Function

Err_OpenBook:
    If Err.Number <> 1004 Then
        MsgBox Err.Description, vbCritical + vbOKOnly, &quot;Error&quot;
        Resume Exit_OpenBook
    End If
End Function

File: Buyer.xls
Macro: Autpen

Sub Auto_Open()
On Error GoTo Err_Auto_Open

    If Range("C3").Value = "" Then
        Workbooks.Open FileName:="C:\Temp\Buyer.csv"
        Range("A2:AC64999").Select
        Selection.Copy
        Windows("Buyer.xls").Activate
        Selection.PasteSpecial _
            Paste:=xlValues, _
            Operation:=xlNone, _
            SkipBlanks:= False, _
            Transpose:=False
        Range("A3").Select
        Application.CutCopyMode = False
        ActiveWorkbook.SaveAs _
            FileName:="C:\Temp\Buyer.xls", _
            FileFormat:=xlNormal, _
            Password:="", _
            WriteResPassword:="", _
            ReadOnlyRecommended:=False, _
            CreateBackup:=False
    End If

Exit_Auto_Open:
    Windows("Buyer.csv").Close
    ActiveWorkbook.Close

Err_Auto_Open:
    If Err.Number <> 1004 Then
        MsgBox Err.Description, vbExclamation + vbOKOnly, &quot;Error&quot;
        Resume Exit_Auto_Open
    Else
        Windows(&quot;Buyer.csv&quot;).Close
        ActiveWorkbook.Close
    End If
End Sub

I get the message box that says "File: Buyer.xls" then the file opens and the macro runs, but I never get the "File: SalesRep.xls" message box.

Thanks, in advance, for any advice...
--sshowers

 

[Gavona]

A few very inexpert thoughts before I retire (I am sure Zathras or one of the other experts will beable to spot the problem):

Have you stepped through the macros to find out exactly what the code is doing?

I wonder if the problem is that your macros are in different workbooks. Is this necessary or could you have them all within the Automation.xls and merely call them.

Why is OpenBook a function rather than a Sub?

 

[sshowers]

Gavona:

Thank you for asking the questions because thinking through the answers helped me solve the problem. Since you've asked, I'll answer:

1. Yes, I had stepped through the code.
2. Answer below, but this was the problem!
3. Bad habit.

The macros were in different workbooks because, as my original description told, the files currently run as five separte Excel calls and are completely self-contained. I was, in essence, trying to test this new procedure by simply calling copies of my original files (read: laziness).

I have moved the code out of the "called" workbooks and into the "Automation" workbook (in a procedure, not a function) and it works exactly as I'd hoped!!!

Thanks, again,
sshowers

 

[Gavona]

Stars for asking questions - thats a new one!
Glad to have helped though.

Gavona