save to desktop macro

[asabti]

i currently have a macro that saves a file to a location specified in a ceratin cell

Sub Button989_Click()

ActiveWorkbook.SaveAs Filename:=Range("C37") & Range("C36").Value & ".xls"
End Sub

I need to do couple of things.

1. get the above macro to save to desktop without having to insert the path...
2. i want to give the option of saving it on the desktop or a folder in the c drive

the problems

how do i get excel to read the comptuer username to do the save as on the desktop

ALSO how do i get excel to make a folder if the folder on the c drive does not exist

is this even possible?

Thank you

[dominicb]

Good evening asabti

how do i get excel to read the comptuer username to do the save as on the desktop

This will return the path of the local computer's desktop :

Please Login or Register  to view this content.

ALSO how do i get excel to make a folder if the folder on the c drive does not exist

This code will check for the existence of a directory called "Test" in the path "C:\" - if one isn't found it will be created :

Please Login or Register  to view this content.

HTH

DominicB

[asabti]

this is the code i'm currently running its a hybrid between the code you provided and some other code: when i run it it says "subscript out of range" basically i have a drop down menu saying desktop or C:\Sample Spec Sheets

Sub Button989_Click()
MyDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
With Worksheets("Sheet8")
If .Range("C27").Value = "Desktop" Then
ActiveWorkbook.SaveAs Filename:=MyDesktop & Range("C36").Value & ".xls"
End If
If .Range("C27").Value = "C:\Sample Spec Sheets" Then
On Error Resume Next
Set a = CreateObject("Scripting.FileSystemObject").getfolder("C:\Sample Spec Sheets")
If Err = 0 = 0 Then
MkDir "C:\Sample Spec Sheets"
End If
ActiveWorkbook.SaveAs Filename:="C:\Sample Spec Sheets" & Range("C36").Value & ".xls"
End If
End With
End Sub

[Kenneth Hobson]

You have several things to do if you want to prevent errors. To see which line is causing the error, in the VBE, press F8 in the routine until you find the error line.

I considered a few problems here. Of course it will still error if C36 contains an illegal filename. You would need an API routine to check for a valid filename string. Same error occurs for illegal folder names in C27.

Please Login or Register  to view this content.

[asabti]

when i press F8 to see where the error is.. it highlights this:

Please Login or Register  to view this content.

what is wrong herE?

[Kenneth Hobson]

Maybe a subscript error? This will happen if you don't have a sheet named "Sheet8".

ActiveSheet should work ok if that is the sheet that you want to check.

[asabti]

amazing! thank you for all your help!!! WOW thanks!