opening a file with a variable name

Question

Hello I have current Problem, I have a variable called Dateiname in Module1, which is a Name of a Excel File.This variable takes values such as "04_01_2017.xlsm". It can vary depending on the User Input

    Dateiname = Ord & mNummerGanz & "_" & Name & ".xlsm"
    ThisWorkbook.SaveAs Filename:=Dateiname

I want to be able Activate this Excel file from another Excel file.This Excel file does some operations before in Module2 before activating the Excel file with variable Name.

When i type;

    Windows(Dateiname).Activate

I get an error such as; runtime error 9. Can aynyone help me ?

Answer 1

Use Option Explicit in all of your modules.

Your variables from Module1 are not in scope/available in Module2. Option Explicit will alert you to this error, as Dateiname is most likely undefined in scope of Module2, and has an empty/nullstring value.

If the Dateiname is empty/nullstring, this would always raise a subscript out of range (type 9) error, because it is equivalent to Windows("").Activate, and since there is no such named Window/Workbook, an error will raise.

IF Dateiname is a module-scoped variable, you can do:

Windows(Module1.Dateiname).Activate

Or preferably:

Workbooks(Module1.Dateiname).Activate

And as others have mentioned, you should probably be using a Workbook variable.

IF Dateiname is procedure scope

You will need to explicitly pass the variable to the procedure in Module2. E.g.,

Module1:

Sub foo()
Dim Dateiname as String
Dateiname = "some value"

Call Module2.bar(Dateiname)

End Sub

Module2:

Sub bar(Dateiname as String)

MsgBox Dateiname

End Sub