Cannot save 2003 file in 2010

[techstyle20]

Hi,

I am hoping someone can help. I have a macro that saves a file prefectly in 2003 but the same macro does not work in 2010. In my office we have users who are on both 2010 and 2003 so i need this to work on each version.

In essence I want this macro to save a new workbook as .xls so that all users can view and use the workbook. I have scoured this looking for an answer but nothing seems to work.

Can anyone help?

Sub CreateWorkbooks()
'creates new workbook in a specific location
Application.ScreenUpdating = False
Dim FilePath As String
Dim FName As String
Dim TodaysDate
TodaysDate = Format(Date, "dd-mm-yyyy")
FName = Range("P8").Value
Workbooks.Add
FilePath = "Child"
ActiveWorkbook.SaveAs FileName:=FilePath & "\" & FName & "" & TodaysDate & ".xls"

ActiveWorkbook.Close

End Sub

 

[Joe4]

Not sure how that would work. The Filepath does not look valid to me:

 FilePath = "Child"
 ActiveWorkbook.SaveAs Filename:=FilePath & "\" & FName & "" & TodaysDate & ".xls"

Most filepaths I have seen begin with a letter, colon, and slash, or use UNC.

What exactly does "Child" represent?

 

[techstyle20]

Hi Joe,

"Child" is the name of the folder I want to save it too. I cannot name a specific path as this will be run on different shared drives.

Is there a more elegant way of doing this?

Thanks for your time!

BTW, how do I get the code into one of the windows you use?

 

[Joe4]

"Child" is the name of the folder I want to save it too. I cannot name a specific path as this will be run on different shared drives.

I think you are going to need to tell it where the "Child" folder resides though. Is there some sort of logic, perhaps by user, to determine where this folder resides? I am curious about how it worked on 2003 like that.

BTW, how do I get the code into one of the windows you use?

If you go to the Advanced reply window, there is an icon for code. Just highlight your code, and click on the code icon (which looks like a "hash-tag" or "pound" icon. Alternatively, you can surround your code by the words CODE and /CODE in square brackets. Feel free to play around with it in the "Test Here" forum, if you like.

 

[techstyle20]

Hi Joe,

This works fine in 2003. If this can just be amended to also work in 2010 and SaveAs a 2003 workbook it would be great.

TIm.

Workbooks.Add
FilePath = "Child"
ActiveWorkbook.SaveAs Filename:=FilePath & "\" & FName & "" & TodaysDate

 

[xenou]

Hi Joe,

This works fine in 2003. If this can just be amended to also work in 2010 and SaveAs a 2003 workbook it would be great.

TIm.

Code:

Workbooks.Add
FilePath = "Child"
ActiveWorkbook.SaveAs Filename:=FilePath & "\" & FName & "" & TodaysDate

Are you sure it works fine in 2003? It's not a valid filepath. In any case, you need to provide a valid filepath now if it is going to work.

 

[Wookiee]

If your scenario permits each user to save their copies to their respective desktops, I have some code that I think might help.

'Define Variables
strUserName = Environ("USERNAME")

'Save Report To Desktop
Application.DisplayAlerts = False
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
    "C:\Documents and Settings\" & strUserName & "\Desktop\" & FName, FileFormat:=xlExcel8
Application.DisplayAlerts = True

 

[RoryA]

That code will only work if Child is a folder in the current directory - a dangerous thing to assume in my opinion. However it will work in 2010 as it did in 2003 except that in 2010 you need to specify a file type if you want it saved as an xls file:

ActiveWorkbook.SaveAs Filename:=FilePath & "\" & FName & "" & TodaysDate & ".xls", Fileformat:=56

 

[Scott Huish]

If your scenario permits each user to save their copies to their respective desktops, I have some code that I think might help.

'Define Variables
strUserName = Environ("USERNAME")

'Save Report To Desktop
Application.DisplayAlerts = False
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
    "C:\Documents and Settings\" & strUserName & "\Desktop\" & FName, FileFormat:=xlExcel8
Application.DisplayAlerts = True

Not every version of Windows stores the desktop files in that location. Windows 7 doesn't.

 

[rollis13]

You can use USERPROFILE instead of just USERNAME to retrieve the entire path.
USERPROFILE in WinXP would be: C:\Documents and Settings\Username
and in Win7: C:\Users\Username