Print Report Wizard - Via VBA/Macro

[Mathula Jr]

Hi there, I have a VBA/macro that is supposed to loop through a specified range of subsets that are retrieved via the Wildcard File Search
function and print a single Workbook with one instance of each sheet for every element inside a subset.

Currently the code only processes the first subset in the Wildcard File search and prints multiple single sheet workbooks for every element of that
subset and refuses to go to the next subset by throwing this error: Run-time error '5': Invalid procedure call or argument.

I've attached my code below, any help would be much appreciated.

Code: Select all

Option Explicit

Sub Print_Report()

    Dim NewName As String
    Dim ws As Worksheet
    Dim TM1Element As String
    Dim I As Integer
    Dim myDim As String
    Dim server As String
    Dim fullDim As String
    Dim folder As String
    Dim destination As String
    Dim Subset_Path As String
    Dim Subset As String
    Dim Subset_Less_Extension As String
    
    destination = "C:\Users\SK\Documents\Totem\TM1\Projects\Standard Bank\Cost Centre Reports"
    server = "gti_dev"
    myDim = "Cost Centre"
    fullDim = server & ":" & myDim
   
    Subset_Path = "C:\Users\SK\Documents\TM1\Subsets\" & "Proto*.sub" 'Subset file directory
    Subset = Dir(Subset_Path, vbNormal) 'Variable storing Subset name with .Sub file extension
    
'Check if specified dimension exists
If Run("dimix", server & ":}Dimensions", myDim) = 0 Then
        MsgBox "The dimension does not exist on this server"
        Exit Sub
    End If
    
    'Loop through all the Subsets in the Subset file directory
    While Subset <> ""
    
        'Display the current Subset
        MsgBox Subset
        
        'Remove the .Sub file extension from the Subset name so we can successfully pass it as a valid Subset of the Cost Centre dimension
        Subset_Less_Extension = Left(Subset, Len(Subset) - 4)
        
           'loop over all elements of the current Cost Centre dimension Subset
           For I = 1 To Run("subsiz", fullDim, Subset_Less_Extension)
           
           'Retrieve the name of the current Subset element
         TM1Element = Run("SUBNM", fullDim, Subset_Less_Extension, I, "Name")
      
      'Update the report title
      Range("$O$1").Value = "=SUBNM(""" & fullDim & """, "" & Subset_Less_Extension & "", """ & TM1Element & """, ""Name"")"
        
        'refresh worksheet
        Application.Run ("TM1RECALC")

With Application
                    .ScreenUpdating = False
        
                    
                    '*SET THE SHEET NAMES TO COPIED TO THE NEW WORKBOOK
                    'Sheet names go inside quotes, seperated by commas
                    'eg: Sheets(Array("Budget", "CopyMe2")).Copy
                    Sheets(Array("Budget")).Copy
                     
                     '       Paste sheets as values
                     '       Remove External Links, Hperlinks and hard-code formulas
                     '       Make sure A1 is selected on all sheets
                    For Each ws In ActiveWorkbook.Worksheets
                        ws.Cells.Copy
                        ws.[A1].PasteSpecial Paste:=xlValues
                        ws.Cells.Hyperlinks.Delete
                        Application.CutCopyMode = False
                        Cells(1, 1).Select
                        ws.Activate
                    Next ws
                    Cells(1, 1).Select
                     
                     'Name report after the current Cost Centre element name
                    NewName = Right(Range("$O$1").Value, 100)
                     
                     
                     'Save it in the Cost Centre folder of the same name
                      folder = Dir(destination & NewName & "*", vbDirectory)
   
                    ActiveWorkbook.SaveCopyAs destination & folder & "\" & NewName & ".xlsx"
                    'Skip save file confirmation
                    ActiveWorkbook.Saved = True
                    ActiveWorkbook.Close savechanges:=False
                    .ScreenUpdating = True
                End With
                  
               Next I
                   
   'Retrieve the next Subset file in the directory
   Subset = Dir
   
  Exit Sub
  
  Wend
 
End Sub

[Alan Kirk]

Hi there, I have a VBA/macro that is supposed to loop through a specified range of subsets that are retrieved via the Wildcard File Search
function and print a single Workbook with one instance of each sheet for every element inside a subset.

Interesting way of doing it. I'm not sure I'm a fan because you've got a disconnect between the files that you use to define the subset names in the user's local hard drive, and the actual subsets on the server, but in the absence of a macro function that returns a list of subsets straight from the server it's certainly an inventive way of approaching it.

But to the issue that you're facing... you're using the Dir() function to iterate the .sub file names on the local PC. I've never much liked Dir(); it's always been too flaky for my taste, and doubly so when being used to return values iteratively. The issue that you have is that you're using Dir() again on this line:

Code: Select all

folder = Dir(destination & NewName & "*", vbDirectory)

Before you make the iterative call to Dir down here:

Code: Select all

Subset = Dir

That extra call is cactusing your iteration; to get the "next file in the series" as you're trying to do, the plain Dir call has to be the very next one that you make after the initial one.

I'd suggest that you not use Dir at all but rather use the Microsoft Scripting library. For this you should set a reference (Tools => References in the VB Editor) to the Microsoft Scripting Runtime. From there you can create a FileSystemObject, the details of which you can find here, and in turn you can use that to obtain lists of files and folders far more reliably than Dir().

You should be aware of a couple of things. The first is that the FSO is considerably slower than the Dir method and vastly slower than Windows API calls, which I used to use back in the day. But for small numbers of files and folders, you won't notice the difference. (For hundreds or thousands of files or folders, you will which is why I always used WinAPI calls over the FSO for those.)

The second is that although the FSO hierarchy bears a superficial similarity to the System.IO namespace in .Net (and some of the object names in the hierarchy are the same) they have no connection to each other at all. (Except perhaps that the .Net developers looked at the FSO and decided to use it as a blueprint on which they could base a "done right" version of it for .Net.) If you're looking for information on the Web for using the FSO and its associated objects, make sure that you search for the VBA or Microsoft Scripting objects, and that you aren't looking at documentation for the .Net objects.