Update filenames in multiple modules

[cadlum]

Hi all,
This is a weird and specific problem so I was having trouble finding anything on google.

I have 6 modules that do reporting on a computer inventory database (xlsx format). The reports work great but I plan on handing this stuff out to others. I would like it to work without them having to go into the code at all. I am trying to create another module that will go to each of the other 6 and update the file paths they are using.

Currently, each module start off like this (after the dim's)

FilePath = Left(ActiveWorkbook.Path, InStrRev(ActiveWorkbook.Path, "\"))
FileName1 = "Exmaple.xlsx"


Workbooks.Open FileName:=FilePath & "\" & FileName1

The code also ends with closing the open workbook.

Workbooks(FileName1).Close SaveChanges:=False

I want to update FileName1 in each module. This section of code is not on the same line numbers in the modules. So I was hoping for a more flexible approach. I would like to be able to have a module that will ask the user what file they want to use and then it will update all the modules dynamically (even if their name changes).

This is the code I have so far, but its more static than dynamic.

Sub UpdateFileLoc()
'for this macro to work, this setting needs to be set manually
'Office button > Excel Options > Trust Center > Trust Center Settings > Macro Settings, check Trust access to the VBA project


Dim sFileName As Variant, iNum As Integer, strFile As String, tests As String, FileName As String


sFileName = Application.GetOpenFilename(FileFilter:="Inventory Files (*.xls; *.xlsx),*.xls;*.xlsx", _
            Title:="Open Inventory File", MultiSelect:=True)


If IsArray(sFileName) Then
    For iNum = LBound(sFileName) To UBound(sFileName)
        strFile = sFileName(iNum)
        FileName = Mid(strFile, InStrRev(strFile, "\") + 1)
        Application.VBE.ActiveVBProject.VBComponents("test").CodeModule.ReplaceLine 9, "FileName1 = """ & FileName & """"
        'set up error handling if they havent made the settings change
    Next iNum
End If


End Sub

Thanks!

 

[FormR]

Hi; either use a public variable. How to use variables in Excel sub-procedures in Visual Basic for Applications

For example:

Public sFilename As String

Sub setsfilename()
sFilename = "Hello.xlsx"
End Sub

Sub anothermacro()
MsgBox sFilename
End Sub


Sub anotheranothermacro()
MsgBox sFilename
End Sub

Or store the file name in a spare cell in a spare sheet.

For example:

Sub setsfilename()
Sheets("Sheet1").Value = "Hello.xlsx"
End Sub

Sub anothermacro()
Dim sFilename As String
sFilename = Sheets("Sheet1").Value = "Hello.xlsx"
MsgBox sFilename
End Sub

Sub anotheranothermacro()
Dim sFilename As String
sFilename = Sheets("Sheet1").Value = "Hello.xlsx"
MsgBox sFilename
End Sub

 

[FormR]

Oops! the second example should be as below>

Sub setsfilename()
Sheets("Sheet1").Value = "Hello.xlsx"
End Sub

Sub anothermacro()
Dim sFilename As String
sFilename = Sheets("Sheet1").Value
MsgBox sFilename
End Sub

Sub anotheranothermacro()
Dim sFilename As String
sFilename = Sheets("Sheet1").Value
MsgBox sFilename
End Sub