0

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
Community
  • 1
  • 1
Joey b
  • 25
  • 8
  • 1
    The main reason you were getting the error is becuase you declared the object as range and then you were passing the range. **All you have to do in your original code** was to use `rng.Value` Simply change `Windows(FileName).Activate` to `Windows(FileName.Value).Activate` – Siddharth Rout Jan 10 '19 at 10:17

1 Answers1

3

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
Thryn
  • 425
  • 2
  • 14
  • I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). And you cannot `Set` a variable that is not an `Object`. This throws an exception. – Pᴇʜ Jan 10 '19 at 09:57
  • Ah, you right, I just slightly modified the original code. Let me edit that. – Thryn Jan 10 '19 at 09:59
  • you cannot `Set FileName` because `FileName` is not an `Object` but a `String`! – Pᴇʜ Jan 10 '19 at 10:00
  • Ha ha, I shouldn't have been lazy and actually rewrite the whole line. – Thryn Jan 10 '19 at 10:01
  • Yeah this works brilliantly! Thanks a lot to you both! – Joey b Jan 10 '19 at 10:04