Hardcoded workbook name, need to make relative code
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
PHP Code:
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
Bookmarks