Search code examples
excelvbatype-mismatch

Use a variable filename to switch between workbooks

I have multiple Excel VBA applications running throughout my organization.

I had all employee names hard coded in the applications, but this meant that I had to change it myself. To get rid of this dependence I created one Excel file with all employee names. When new people are added to this list, a Macro in all applications can be run to update the names. This Macro works.

Problem

Filenames might change through the years (and with version changes), but I still want the application to work when a filename changes. This is a problem, because I reference to this filename because I switch between workbooks several times.

So I coded that the filename (the one that is variable) is copied to the other file. Then I Dim Filename as Range and consequently Set Filename=Range("A1"). I want to use this in Windows(FileName).Activate but I receive the Error 13 type mismatch. I don't understand why this doesn't work, because when I debug I see that it set the filename properly.

set the filename properly

Sub Engineering()
Dim FileName As Range

Sheets("Info").Select
Set FileName = Range("A1")

Windows(FileName).Activate
'Here I get error 13 type mismatch


Sheets("Engineering").Select
Range("Tabel3[[#All],[Kolom1]]").Select
Application.CutCopyMode = False
Selection.ClearContents
Application.CutCopyMode = False

Windows("Personeelsnamen.xlsx").Activate
Sheets("Engineering").Select
Range("Tabel3[[#All],[Kolom1]]").Select
Application.CutCopyMode = False
Selection.Copy

Windows(FileName).Activate
'Range("Tabel14[@Kolom1]").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste

End Sub

The following is the finalized code that goes switches between two workbooks to copy tables from one to the other.

Sub Knop17081_Klikken()
Dim myData As Workbook
Dim BestandsNaam As String

Sheets("RD & LTE").Visible = True
Sheets("CAM & LTE").Visible = True
Sheets("Engineering").Visible = True
Sheets("CAM").Visible = True
Sheets("LTE").Visible = True


Sheets("Input").Select
Range("L1").Value = ActiveWorkbook.Name

Range("L1").Select

Application.CutCopyMode = False
Selection.Copy

Set myData = Workbooks.Open("HyperlinkDirectlyToFile")

ActiveWindow.ActivatePrevious

Application.CutCopyMode = False
Selection.Copy
Windows("Personeelsnamen.xlsm").Activate
Sheets("Info").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste

Call Engineering
Call CAM
Call LTE
myData.Close
Call CombineerCAMenLTE
Call CombineerRDenLTE

Sheets("RD & LTE").Visible = False
Sheets("CAM & LTE").Visible = False
Sheets("Engineering").Visible = False
Sheets("CAM").Visible = False
Sheets("LTE").Visible = False

End Sub
Solution

  • As the error suggest, the variable isn't the right type. It's a range while the Windows() object needs a string variable.

    Please, try this way and tell me if that works

    Sub Engineering()
Dim FileName As String

FileName = Worksheets("Info").Range("A1").Value

Windows(FileName).Activate