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????

 

[Nishant79]

if i am getting you correctly then your asking me is it my file name ????

yes if its the name of my excel .....NestXL-NishantNew i save it as this name.

 

[Jon von der Heyden]

Try this:

Sub day1()
    Dim strDate As String
    Dim strFile As String
    Dim blnProceed As Boolean
    Dim wkbNetPos As Workbook
    
    strDate = Format$(Sheets("Final").Range("A2").Value2, "ddmmm")
    strFile = "Z:\Tom\GETS_FILES\GETS_EXCEL\" & strDate & "\NetPosition.xls"

    blnProceed = CBool(Len(Dir$(strFile)) > 0) 'use a boolean to hold whether or not the file exists
    
    If Not blnProceed Then
    'it doesn't exist, so warn the user
        MsgBox Prompt:="The following file or file directory does not exist:" & vbCrLf & _
                       strFile, _
               Buttons:=vbExclamation + vbOKCancel
    Else
    'it does exist, so run the code
        Set wkbNetPos = Workbooks.Open(Filename:=strFile, UpdateLinks:=False)
        With wkbNetPos
            .Sheets(1).Range("A1:Q499").Copy Destination:=ThisWorkbook.Sheets("Copy Sheet").Range("A1")
            Application.CutCopyMode = False
            .Close SaveChanges:=False
        End With
    End With
End Sub

 

[Nishant79]

got a compile error : End with without With

 

[JackDanIce]

See correction in red, looks like a typo!

Sub day1()
    Dim strDate As String
    Dim strFile As String
    Dim blnProceed As Boolean
    Dim wkbNetPos As Workbook
 
    strDate = Format$(Sheets("Final").Range("A2").Value2, "ddmmm")
    strFile = "Z:\Tom\GETS_FILES\GETS_EXCEL\" & strDate & "\NetPosition.xls"
 
    blnProceed = CBool(Len(Dir$(strFile)) > 0) 'use a boolean to hold whether or not the file exists
 
    If Not blnProceed Then
    'it doesn't exist, so warn the user
        MsgBox Prompt:="The following file or file directory does not exist:" & vbCrLf & _
                       strFile, _
               Buttons:=vbExclamation + vbOKCancel
    Else
    'it does exist, so run the code
        Set wkbNetPos = Workbooks.Open(Filename:=strFile, UpdateLinks:=False)
        With wkbNetPos
            .Sheets(1).Range("A1:Q499").Copy Destination:=ThisWorkbook.Sheets("Copy Sheet").Range("A1")
            Application.CutCopyMode = False
            .Close SaveChanges:=False
        End With
    End IF
End Sub

(Tut tut Jon, too many shandy's... big girl's blouse!)

 

[Jon von der Heyden]

Whoops! Thanks Jack!

Too little coffee. Time for one, with a lemonade top!

 

[Nishant79]

Thanks it works perfectly thanks to everyone and jon who has helped me from the start i really feel great to join this forum

thanks to all

 

[Jon von der Heyden]

You are most welcome, and hope to see you here soon.

 

[JackDanIce]

Last night was all about sake, this morning BIG coffee!