Macro in Excel

[jacc14]

Hi
I have a question about a macro I am creating in Excel.

The VB behind the button is as follows:-

Private Sub CommandButton1_Click()
 
Filename = Range("H66")
ActiveWorkbook.SaveAs Filename:=Filename
End Sub

This quite simply saves the file as the name shown in Cell h66.

What I now need to do is save it to a specific folder. Ie C drive / Estimates. How do I show this please.

Thanks
Jacc14

[Delerna]

Something like this

Filename = "c:/Estimates/" & Range("H66")
 

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question and indeed the instructions in the posting window itself).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use the tags in future.

ADMIN.

[ADezii]

Dim strFileName As String
Dim strFolderPath As String
 
strFileName = Range("H66")
strFolderPath = "C:\<Folder1>\<Folder2>\"
 
If Len(strFileName) > 0 Then
  ActiveWorkbook.SaveAs Filename:=strFolderPath & strFileName
End If

[NeoPa]

Something like this

Filename = "c:/Estimates/" & Range("H66")

I only noticed this after ADezii posted, but Excel is run mostly on Windows based machines, and for them you will need to use the backslash character (\) in place of the slash (/). It would then be (for your stated folder) :

Filename = "C:\Estimates\" & Range("H66")

[Delerna]

I only noticed this after ADezii posted, but Excel is run mostly on Windows based machines, and for them you will need to use the backslash character (\) in place of the slash (/). It would then be (for your stated folder) :

Filename = "C:\Estimates\" & Range("H66")

oops.
I use html, jscript, vbscript, vba ,c# , access, sql, db2 and various languages at home for 3D. I get syntax differences mixed sometimes. I appologise if it caused any confusion.

That'll tech me for posting before testing.

[NeoPa]

Nah. Don't be silly.

I simple little mistake that I'm sure anyone would have spotted had they tried it out. Don't worry about it :)

[ADezii]

After some serious thinking, scary isn't it, wouldn't this be a better solution for you?

Dim varFileName As Variant
Dim NewWorkbook As Workbook
 
Set NewWorkbook = Workbooks.Add
 
varFileName = Application.GetSaveAsFilename
 
If varFileName <> False Then
  'For some odd reason, Dialog includes a "." at the end
  'of a File's Name with no Extension
  If Right$(varFileName, 3) <> "xls" Then
    varFileName = varFileName & "xls"
  End If
  NewWorkbook.SaveAs Filename:=varFileName
End If
 
NewWorkbook.Close
ActiveWorkbook.Activate

[jacc14]

Thanks everyone for your help. I have succeeded in what I was after

Best regards

Jacc14

[NeoPa]

You're welcome, and thanks for letting us know :)