Hardcoded workbook name, need to make relative code

[Pasha81]

I have this code in a workbk(ThisWkbk), that is used to open a dialog box, after which I select a file called 'ALP.xls', after which the macro runs, copies a sheet called 'FUTURES' from it and saves that sheet in a new Workbook and closes that wkbk. After that point I need it to close the workbook called 'ALP.xls' that I had selected without saving changes....

It works, but I dont want it hardcoded as teh name of the file I open could change slightly ALP1, ALP2, ALP3.......
How can I use a relative code here? I just cant figure out how to refer to that workbook,

thanks a lot

Sub ALP() 
 
 
Dim Filename As Variant 
Dim wkbThis As Workbook 
Dim wkbClient As Workbook 
Dim wsClient As Worksheet 
Dim wsInstrumentMapping As Worksheet 
Dim wkbOMS As Workbook 
Dim wsOMS As Worksheet 
 
Dim strDate As String 
     strDate = Format(Date, "dd-mm-yy") & "." & Format(Time, "hh-mm-ss") 
      
      
Filename = Application.GetOpenFilename("Excel Files (*.xls), *.xls") 
If Filename <> False Then 
      
    Set wkbThis = ThisWorkbook 
    Set wkbClient = Workbooks.Open(Filename) 
 
 
Set wsClient = wkbClient.Worksheets("Futures") 
Set wsInstrumentMapping = wkbThis.Worksheets("InstrumentMapping") 
  
     
      
  
      
  wsClient.Copy 
      
  
     'your code 
    Dim i As Long 
    Dim rng As Range 
      
    With Application 
          
        .ScreenUpdating = False 
        .Calculation = xlCalculationManual 
    End With 
      
     
         With Sheets("Futures") 
          
 
'Deleting unwanted Rows 
 
Range("J:AF").ClearContents 
Dim cell As Range 
    Dim t As Long 
    Dim Ranger As Range 
    Dim Row As Range 
    Dim Rangos As Range 
     Dim u As Integer 
     
      
    t = Sheets("Futures").Range("A" & Rows.Count).End(xlUp).Row 
     
     
     
    Set Ranger = Sheets("Futures").Range("A3:A" & t) 
     
    For Each cell In Ranger 
        If cell.Value = "Test" Then 
           
            cell.EntireRow.Delete 
        
         
        End If 
         
    Next cell 
 
For Each cell In Ranger 
        If cell.Value = "Bar" Then 
           
            cell.EntireRow.Delete 
        
         
        End If 
         
    Next cell 
 
         
       'rearranging data 
        i = 2 
        Do 
              
            If .Cells(i, "A").Value = "" Or ( _ 
            .Cells(i, "F").Value = "" And .Cells(i, "G").Value = "") Then 
                  
                If rng Is Nothing Then 
                      
                    Set rng = .Rows(i) 
                Else 
                      
                    Set rng = Union(rng, .Rows(i)) 
                End If 
            End If 
              
            .Cells(i, "C").Value = .Cells(i, "F").Value + .Cells(i, "G").Value 
            .Cells(i, "A").Value = IIf(.Cells(i, "C").Value < 0, "sell", "buy") 
            .Cells(i, "C").Value = Abs(.Cells(i, "C").Value) 
            If .Cells(i, "F").Value Then .Cells(i, "H").Value = 3 
            If .Cells(i, "G").Value Then .Cells(i, "H").Value = 5 
            .Cells(i, "D").Resize(, 4).Value = Array("date", "time", "", "") 
            i = i + 1 
             
        Loop Until .Cells(i, "A").Value = "ARM" 
          
        If rng Is Nothing Then 
              
            Set rng = .Rows(i) 
        Else 
              
            Set rng = Union(rng, .Rows(i)) 
        End If 
          
        rng.Delete 
          
          
          
          
         'Instrument Mapping 
         Dim lngLastRow As Long 
    Dim rngDest As Range 
     
     
    lngLastRow = Sheets("Futures").Cells(Rows.Count, 2).End(xlUp).Row 
    Set rngDest = Sheets("Futures").Range("B2:B" & lngLastRow) 
    rngDest = Application.VLookup(rngDest, wsInstrumentMapping.Range("A:B"), 2, 0) 
          
         'Formatting cells 
          
         Range("A:J").Select 
         Selection.Font.Size = 18 
         Selection.Font.Name = "Times New Roman" 
         Selection.Font.Bold = False 
         Selection.Font.ColorIndex = 1 
         Cells.EntireColumn.AutoFit 
         Cells.EntireRow.AutoFit 
          
          
         Range("A:BZ").Select 
         With Selection 
                   
 
         Rows("1").Delete 
         u = Sheets("Futures").Range("C" & Rows.Count).End(xlUp).Row 
         Set Rangos = Sheets("Futures").Range("C1:C" & u) 
          
         For Each cell In Rangos 
        If cell.Value = "0" Then 
          cell.EntireRow.Delete 
       End If 
         
    Next cell 
          
          
         End With 
          
                
         
         
    End With 
      
 
 
        Sheets("Futures").SaveAs "OMSAltoposterx" & "" & strDate & ".xls" 
         ActiveWorkbook.Close 
         
        MsgBox "The OMS import file was saved in:" & "OMSAltoposterx.xls" & "" & strDate 
        Workbooks("ALTOPoster2.xls").Close SaveChanges:=False 
        wkbThis.Activate 
         
    End If 
With Application 
          
        .Calculation = xlCalculationAutomatic 
        .ScreenUpdating = True 
    End With 
End Sub  

[Pasha81]

This is the part that needs to be adjusted - its one of teh last lines of the code




Workbooks("ALTOPoster2.xls").Close SaveChanges:=False

[Pasha81]

btw please treat Altoposter2 as ALP
thats the one im trying to close without hardcoding

thanks

[shg]

Declare a workbook variable

Please Login or Register  to view this content.

... and then immediately after you open that workbook, set the variable

Please Login or Register  to view this content.

... then when you're ready to close it

Please Login or Register  to view this content.

Please change the PHP tags to CODE tags in your original post.

[Pasha81]

thanks man, that works!

[shg]

You're welcome.

Please change the PHP tags to CODE tags in your original post.