If activeworkbook.name = ....

[Mihael]

Hi,

I have a macro that works when Excel is opened. I need it to work when the active workbook name is "EXCEL_OMZET_SHEET1.XLS". Also this number 1 must be variable and can be 1 till 20. For each number it needs to trigger the function.

I have used the following:

If ActiveWorkbook.Name = "EXCEL_OMZET_SHEET1.XLS" Then
....
End If

But it doesnt work??.. Can someone tell me what is wrong and share an opinion about the variable number before ".XLS" So that the macro works for the following:

"EXCEL_OMZET_SHEET1.XLS"
"EXCEL_OMZET_SHEET2.XLS"
"EXCEL_OMZET_SHEET3.XLS"

etc....

 

[Eric W]

Have you traced it through to see why it doesn't work? Single step through your code (F8). When it hits that line, hover the mouse over ActiveWorkbook.Name to see what it says.

You could try something like:

    If UCase(ActiveWorkbook.Name) Like "EXCEL_OMZET_SHEET#.XLS" Or _
       UCase(ActiveWorkbook.Name) Like "EXCEL_OMZET_SHEET##.XLS" Then

This will allow any 1- or 2-digit number though.

 

[Mihael]

Hi, I have tried the code, but it gave me an error with block variable not set..

This is the full code I have:

Private WithEvents App As Application
Private Sub Workbook_Open()
    Set App = Application
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
 Dim Matnr As String
 Dim Batchnr As String
 Dim batchvolg As String
 Dim Partname As String
 Dim Year As String
 Dim teller As String
 Set Sht = ActiveSheet
 Dim FldrPth As String
 Dim Fname As String
 Dim Wbk As Workbook
 Dim SrcWbk As Workbook
 Dim Awb As String
 Dim Nwb As String
 Dim HMF As String  'Hexagon moederfile
 Dim Temp As String 'excel template
 Dim WTemp As Workbook
 Dim Macro As String
  
 
 If UCase(ActiveWorkbook.Name) Like "EXCEL_OMZET_SHEET1.XLS" Then
   
 If LCase(Wb.ActiveSheet.Range("C4").Value) = "frontside" Then
 
   Awb = ActiveWorkbook.Name
        batchvolg = Range("C12")
        Partname = Range("C3")
        Matnr = Left(Range("C3").Value, 8)
        Batchnr = Left(Range("C12").Value, 12)
        Year = Right(Range("C8").Value, 4)

   ActiveWorkbook.SaveAs "J:\Kwaliteit Helmond\FINAL INSPECTION REPORTS\" & Year & "\" & Matnr & "\pallet\" & Partname & "_" & batchvolg & ".xls", FileFormat:=xlNormal
   Application.Quit
End If
End If

 

[Mihael]

Solved it with typing Wb.name instead of activeworkbook.name

thankyou for the code!

 

[Eric W]

You're welcome!