Editting Macro

[Nishant79]

Hi there

I have this macro and in one of it there is this line in it where i have to change the date daily (02JUN),its a path where some data is saved each day,So is there anything that the excel always updates the date of today without me changing it.


Workbooks.Open Filename:= _
"Z:\Tom\GETS_FILES\GETS_EXCEL\02Jun\NetPosition.xls"

So can anyone help????

 

[Jon von der Heyden]

Welcome to the board.

Workbooks.Open Filename:= _
        "Z:\Tom\GETS_FILES\GETS_EXCEL\" [B]& Format$(Date,"ddmmm") &[/B] "\NetPosition.xls"

 

[Nishant79]

Thanks a lot ...That works

 

[Jon von der Heyden]

I'll throw in a bit more.

When a path is variable / likely to change, I believe it is best to check that the file full name exits first so to avoid any runtime error. I suggest storing the path in a string variable:

Dim strFile as String

strFile="Z:\Tom\GETS_FILES\GETS_EXCEL\" & Format$(Date,"ddmmm") & "\NetPosition.xls"

If Len(Dir$(strFile)) Then
    Workbooks.Open FileName:=strFile
    'rest of routine goes here
End If

Dir will return a null string (and therefore zero length) if the file does not exist.

 

[Nishant79]

Hi jon

Thanks for the help ....would like a little more help on this one if u can.

suppose in cell A:1 i have 3\06\2011,
cell B:1 i have 4\06\2011
cell C:1 i have 5\06\2011

now in the formula given below

"Z:\Tom\GETS_FILES\GETS_EXCEL\" & Format$(Date,"ddmmm") & "\NetPosition.xls"

</pre>
where we have written in bold can it be possible that it takes the date of cell A:1 which i have specified????

 

[Jon von der Heyden]

Sure.

I think you should probably qualify the sheet too.

"Z:\Tom\GETS_FILES\GETS_EXCEL\" & Format$(Sheet1.Range("A1").Value,"ddmmm") & "\NetPosition.xls"

 

[Nishant79]

Hi jon

This is my whole macro i have changed the name of the sheet1 to my sheet name "Final" but i get a debug ....in A2 cell was the date of yesterday and the path has a yesterdays file but still i get a debug error and could u add the path where if the file isnt there of that date then it doesnt give a debug error.

Below is the macro:

Sub day1()
'
' day1 Macro
'

'
Range("E7").Select
Sheets("Copy Sheet").Select
Range("A1").Select
Application.WindowState = xlMinimized
Workbooks.Open Filename:= _
"Z:\Tom\GETS_FILES\GETS_EXCEL\" & Format$(Final.Range("A2").Value, "ddmmm") & "\NetPosition.xls"
Range("A1:Q499").Select
Selection.Copy
Windows("NestXL-NishantNew.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("B12").Select
Windows("NetPosition.xls").Activate
Range("J16").Select
Application.CutCopyMode = False
ActiveWindow.Close
Sheets("Final").Select
Range("G8").Select
End Sub


Thanks for the great help and your time

 

[Jon von der Heyden]

I was using the sheet CodeName, perhaps you need:

Sheets("Final").Range("A2").Value

 

[Nishant79]

hey cool that worked i just am linking my new sheet now thanks to you.
Now i just need a little help because i am not a coder so as u know i have given my macro .....now where should i put the codes which u gave before that if the files are not there of that date then it shouldnt give me any error (ie debug shouldnt come ) so where can i place it.

 

[Jon von der Heyden]

Is NestXL-NishantNew the calling workbook (i.e. he one you are writing this code in)?