By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Macro in Excel

100+
P: 116
Hi
I have a question about a macro I am creating in Excel.

The VB behind the button is as follows:-
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()
  2.  
  3. Filename = Range("H66")
  4. ActiveWorkbook.SaveAs Filename:=Filename
  5. 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
Aug 10 '08 #1
Share this Question
Share on Google+
9 Replies


Delerna
Expert 100+
P: 1,134
Something like this
Expand|Select|Wrap|Line Numbers
  1. Filename = "c:/Estimates/" & Range("H66")
  2.  
Aug 10 '08 #2

NeoPa
Expert Mod 15k+
P: 31,656
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.
Aug 10 '08 #3

ADezii
Expert 5K+
P: 8,669
Expand|Select|Wrap|Line Numbers
  1. Dim strFileName As String
  2. Dim strFolderPath As String
  3.  
  4. strFileName = Range("H66")
  5. strFolderPath = "C:\<Folder1>\<Folder2>\"
  6.  
  7. If Len(strFileName) > 0 Then
  8.   ActiveWorkbook.SaveAs Filename:=strFolderPath & strFileName
  9. End If
Aug 10 '08 #4

NeoPa
Expert Mod 15k+
P: 31,656
Something like this
Expand|Select|Wrap|Line Numbers
  1. 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) :
Expand|Select|Wrap|Line Numbers
  1. Filename = "C:\Estimates\" & Range("H66")
Aug 10 '08 #5

Delerna
Expert 100+
P: 1,134
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) :
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 10 '08 #6

NeoPa
Expert Mod 15k+
P: 31,656
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 :)
Aug 10 '08 #7

ADezii
Expert 5K+
P: 8,669
After some serious thinking, scary isn't it, wouldn't this be a better solution for you?
Expand|Select|Wrap|Line Numbers
  1. Dim varFileName As Variant
  2. Dim NewWorkbook As Workbook
  3.  
  4. Set NewWorkbook = Workbooks.Add
  5.  
  6. varFileName = Application.GetSaveAsFilename
  7.  
  8. If varFileName <> False Then
  9.   'For some odd reason, Dialog includes a "." at the end
  10.   'of a File's Name with no Extension
  11.   If Right$(varFileName, 3) <> "xls" Then
  12.     varFileName = varFileName & "xls"
  13.   End If
  14.   NewWorkbook.SaveAs Filename:=varFileName
  15. End If
  16.  
  17. NewWorkbook.Close
  18. ActiveWorkbook.Activate
Aug 10 '08 #8

100+
P: 116
Thanks everyone for your help. I have succeeded in what I was after

Best regards

Jacc14
Aug 12 '08 #9

NeoPa
Expert Mod 15k+
P: 31,656
You're welcome, and thanks for letting us know :)
Aug 12 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.