help please

[steve47]

I found this macro code on your site and it does every thing it says it does, but i need it to do a bit more.

Private Sub Workbook_Open()

l = 0
Do
l = l + 1
lastfile = "Work book " & LTrim(Str(l)) & ".XLS"
filetest = Dir("C:\" & lastfile)
Loop Until filetest = ""

NewFileName = "C:\Work book " & LTrim(Str(l)) & ".XLS"

ActiveWorkbook.SaveAs NewFileName

End Sub

How can i make this code look for and use a "Network Drive" called P and then a sub folder called Plant Team, on my company network

The drive i need it access is called Data on totonfs01 '(P)\plant team

Please help an urgent resolution is required at my end

much regards

 

[Andrew Poulsom]

Which line of code causes the error? Can you record a macro while saving a workbook to that folder and post the VBA code?

 

[steve47]

Hi And good morning

It is working but i have one request if possible.

Where it save's the file as work book 1,2,3 etc. Can it have the current date + workbook 1,2,3 etc

 

[Andrew Poulsom]

Please post the code that's working so that I can amend it for the date.

 

[steve47]

Public Sub SAVE()
l = 0
Do
l = l + 1
lastfile = "Work order " & LTrim(Str(l)) & ".XLS"
filetest = Dir("\\Client\P$\Toton Plant Team\Plant team work request completed\" & lastfile)
Loop Until filetest = ""
NewFileName = "\\Client\P$\Toton Plant Team\Plant team work request completed\Work order " & LTrim(Str(l)) & ".XLS"
ActiveWorkbook.SaveAs NewFileName
End Sub

this is the working code

thanks for your help

 

[Andrew Poulsom]

Try (untested):

Public Sub SAVENEW()
    l = 0
    Do
        l = l + 1
        lastfile = "*Work order " & LTrim(Str(l)) & ".XLS"
        filetest = Dir("\\Client\P$\Toton Plant Team\Plant team work request completed\" & lastfile)
        Loop Until filetest = ""
    NewFileName = "\\Client\P$\Toton Plant Team\Plant team work request completed\" & Format(Date, "yyyymmdd") & "Work order " & LTrim(Str(l)) & ".XLS"
    ActiveWorkbook.SaveAs NewFileName
End Sub

 

[steve47]

Hi
it works once then get message saying file all ready exsists, do u want to replace it NO. then u get run time error??

Debug high lights active workbook.saveas Newfilename

Public Sub SAVE()
l = 0
Do
l = l + 1
lastfile = "Work order " & LTrim(Str(l)) & ".XLS"
filetest = Dir("\\Client\P$\Toton Plant Team\Plant team work request completed\" & lastfile)
Loop Until filetest = ""
NewFileName = "\\Client\P$\Toton Plant Team\Plant team work request completed\" & Format(Date, "yyyymmdd") & "Work order " & LTrim(Str(l)) & ".XLS"
ActiveWorkbook.SaveAs NewFileName
End Sub

i have saved closed and reopened the file still the same

Talk tomorrow if possible as i am off duty now.

Much regard

Steve

 

[Andrew Poulsom]

You haven't put the * in here:

lastfile = "*Work order " & LTrim(Str(l)) & ".XLS"

 

[steve47]

Thank you for all your help, have a beer on me.

and again thank you