Save a File to a Specific location Using a Macro in Excel
Вставка
- Опубліковано 2 жов 2024
- Save a File Using a Macro in Excel, Save a file to a particular location using a macro. Save a file as a particular type using a macro. Check out my online courses www.easyexcelan...
Become an Affiliate and earn 25% on Course Sales
barb-s-school-...
For more help visit my website www.easyexcelan... or email me at easyexcelanswers@gmail.com.
code
Private Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String
path = "C:\Users\Barb\Desktop\demo\"
filename1 = Range("C4")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbookFileFormat:=xlOpenXMLWorkbook
FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
Activeworkbook.close
End Sub
For more help visit my website www.easyexcelan... or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
Excel one-on-one on-line training available. Email me to arrange.
I am able to provide online help on your computer at a reasonable rate.
www.amazon.com...
Check out my next one-hour Excel Webinar
www.crowdcast....
I use a Blue condensor Microphone to record my videos, here is the link
amzn.to/37gyyGa
Check out Crowdcast for creating your webinars
app.linkmink.c...
If you need to buy Office 2019 follow
amzn.to/2VX5dv8
I use Tube Buddy to help promote my videos
Check them out
www.Tubebuddy....
Follow me on Facebook
/ easyexcel.answers
TWEET THIS VIDEO • Save a File to a Speci...
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
www.youtube.com...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
Bundle of thanks. It turn my hour work to minutes. thanks again
You deserve a place in heaven , many thanks for you video
Just noticed that the coding in description section is missing the term ".Text" from line "filename1 = Range("C4")" and two extra pasted in text "FileFormat:=xlOpenXMLWorkbook"
Thank you so much, it was easy to understand and implement
Awesome breakdown! You should be a teacher! Nice video!😀
I have taught software for many years. Check out my free webinar tomorrow. www.crowdcast.io/e/excel-beyond-the-basics
Beautiful Thank you 🔥🔥🔥😎
Thank you too!
Hi Barb,
I have used the same code as u mentioned, however the code / step for Saveas is getting debugged. Can you please advise why
did you include the "/" at the end of your path
@@BarbHendersonconsulting Thanks Barb. I wasn't including that. This should work now... thanks again :)
no problem!
Really helpful and interesting! Is it possible to save in 2 differents path according to a specific cell value (path1 or path2)?
I have a video for that! ua-cam.com/video/j2Ttlq3Bqzg/v-deo.html
Hi Barb! I've played with your teachings but I couldn't figure out a way to combine them. I'd like to have a master PO file that increases the number by 1 when I open it, then, after I input the data, I'd like to have a button to save the workbook to a specific location, then clear the contents of the master PO and have it ready for next time. Any way to do it?
The code to increase the number would be located on the worksheet.
You would then have to write code to copy and save the worksheet to a specific location and once it is save you would have to clearcontents to the worksheet but the worksheet would have to closed and opened again to achieve a new number.
@@BarbHendersonconsulting Thank you! Makes sense.
What if Madam !!!! if i want to save a file into sub folder of Active workbook path as pdf. I dont care where the file is being saved , the macro should save the file where the active workbook located and make subfolder. For example Macro Enabled Workbook is Saved into C:\My Folder\Vouchers.xlsm. Now i want to save a PDF file into C:\My Folder\Payment Voucher\Voucher_001.pdf without giving the address. just want to save where the activeworkbook is being saved. please madam guide me.
Hello I Wana make that path name dynamic is that possible? For example I want to save that file in different folders based on the path I enter in excel
Have you seen this video, I think it might be the answer ua-cam.com/video/VbYvSALwejE/v-deo.html
Hi,
Can U pls Help me..?? 1.I have Many Sheets In One Work Book. So, I want to Save A Range Instead Of Entire WorkBook. 2.After Saving The New Saved File Opens And The Old One Closes. Pls Help me.
Nowfal Beary
I am away from my computer for a few days.
I can look at your problem on Wednesday,
Barb
What I would do is copy the range to a new file and save it and then delete the old file
what if I'm using an EXCEL Userform and I want to drag/drop a Word doc on the same location/folder that the EXCEL is on and rename the WORD as the name on the header of the Word? The drag/drop is working but I just don't know how to code to save the WORD and rename it as the name on the header of the Word. I need help pls.
sorry, I specialize in VBA for Excel
Very useful thanks. When I use this code it's work properly. but my saved macro file macros is not working. what kind of changes need to that. Kindly replay as soon as possible.
when you save a file with a macro in it, the file extension changes to .xlsm If you save it as a .xlsx the macro will be lost
Hi Barb,
Its perfectly working on my PC
however error is showing when other users of my company try to use the same. it might be because of username because all of them having different usernames.
is there any way to fix this, so all users can save the file at assigned path.
Where are you saving the file? If it is on your drive then it will not work for other users but if it is on a shared network, it should work
Are you able to export an Excel sheet to save as a XML file via macro?
I have a macro for copying and pasting data from one sheet into another sheet where I have columns that are XML Sourced set up how an application I'm using will accept them. I currently run the macro to copy and paste one batch of records at a time into the sheet then have to hit Export and name the files (1, 2, 3 etc.). My macro removes the batch of entries and copies in the next set. If a macro could export and name the file the next number in sequence that would be great.
I tried copying this format, not on a new Invoice but a method statement. I got the XL sheet to save to a random file, but the file was called General and not the File name that I assigned to it. Any ideas what I have done wrong
it names the file what ever is located in the cell specified
Can we save the worksheet to SharePoint using Macro. I have a macro which sends out an email with the content of the sheet but I also want it to save a copy on Sharepoint site. Is that possible?
You will be able to send to SharePoint but you will not be able to run on SharePoint
That's great...
It works perfectly but the only thing is I also want to break all the links after save as the file. Is it possible ?
Not sure how you could do that automatically
Please make a video for VBA to Save a specific Excel sheet using data from cells & single click
I will see what I can do
how to save as file with format macro... please
I have a video for that ua-cam.com/video/pGokAz2iUbk/v-deo.html
Hi Barb,
nice tutorial, and thanks for uploading this video, and i have a doubt for save invoice data to next sheet using macro, i.e i want to create invoice 10 invoices per day, i want to bake up the data to next sheet, below codes are am using but not working properly, i would like to request if you upload any video what am ask, kindly share the link to me
Sub transfer()
'Copy the data
Sheets("Sheet2").Range("E22:M22").Copy
'Activate the destination worksheet
Sheets("SOA").Activate
'Select the target range
Range("E6").Select
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
thanks,
Arunkumar K.M
I really like how straightforward this is. So much so that I’ve subscribed for more. However I have a problem with the macro. When I run it I finish up with a line highlighted in yellow. The line in question starts “ActiveWorkbook.SaveAs” and the message on screen is “Run-time error ‘1004’ Method ‘SaveAs’ of object ‘_Workbook’ failed. I am using Excel 2016. Can I please have a solution as the macro is perfect for the sheet I’m preparing.
on your path check that you have a closing "\" That is the normal cause of this error
Thank you for the quick response. I placed the back slash at the end of the line after the expression “FileFormat:=xlOpenXMLWorkbook”and upon debugging I receive the message “Compile Error: Syntax error”. Have I put it in the wrong place?
Sorry I misread your reply. I do have a back slash at the end of the line beginning path =
@@alanjmcgibbon did you end up figureing this out? I have the same problem
Hello Barb
Video is really helpful..thanks for sharing it.
Can you please share the code if the file in the designated path is already there...how to display the option to save it again with different name???
Pls help me with this.
Thanks
Super madam very nice bt I need agine one more question how to automatically open file my friend send one excel file to email me download then use hear save to pdf button have me click the button automatically open this workbook location one ( pdf folder) open then inside this pdf file have when I delete this pdf Fila not possible this he how to make please explain madam
Nice video but to bad when i close my excel and reopen the button disappears, help me
Try it again, that should not happen
Hi Madam i am facing this error filename1 = Range("F4:AJ4").Text , where is fault? please help me
This code is used to save one file name not a range of file names
Thank you for prompt reply, so which code i have o type???
Hi Barb, I’m having an issue with my macro and I’m getting the error “method ‘SaveAs’ of object’_workbook’ failed” when I debug it’s the ActiveWorkbook.SaveAs that is highlighted. Any ideas?
ActiveWorkbook.SaveAs Filename:=path & filename 1 & “.xlxs”, FileFormat:=xlOpenXMLworkbook
did you place the final "/" on your path?
Hi Barb,
My path reads as:
Path= “s:\public\FOLDER\FOLDER\”
Barb:
Excellent video. I noticed the date submitted was 2017, so I was wondering if there are any changes for the newer Excel for Microsoft 365. he reason I ask is that I have a file that I use at work that is set up almost exactly as you played out, except it saves the file to the desktop. Since we migrated to 365 and all our computers are set up to save to a personal One Drive which then mirrors the files to the cloud one drive for that user. The problem now is the file still saves to the desktop in c:\desktop, so you don't see the files on your real desktop. The path for this newer one is C:\Users\Users Name\OneDrive - Government of Ontario\Desktop
I can't for the life of me find the proper syntax to change it over. Can you offer any suggestions? Thanks
The only thing I can think of is to go into the folder where you would lik to save the file and select another file. Go to the home tab of the folder. In the clipboard section of the menu bar there is a "Copy Path" double click this and place it in the code. Replace the file name with the desired file name and do not forget the closing "\" Here is an example of the path I copied using this method "C:\Users\screa\Downloads\onlineStatement.pdf"
Thanks!! @@BarbHendersonconsulting
@@paulcote4257 your welcome
I need your guidance here to copy the latest file in a folder and paste it in another path. Can you please help me on this..
Hello, how would you make a macro to request the user to attach an PDF document, and save that document with a different name in another folder for it's consult.
that would be quite a bit different than this macro
@@BarbHendersonconsulting do you think you can help me please? At the momento I have only achieve to attach the document as an object. But that doesn't really work.
Madam how to capy sheet2 in specific folder and path with cells name using userform..??
i write ".xlsx", and get a error message .Anyone have any idea how i can solve it.Thanks in advance.
Hi. What do I need to change in this code that the original excel stays active? Right now it automatically opens the saved excel and I dont want it to become active. I mean if I want to continue editing the original excel file.
What if the paths are from a list that you need to loop from? Different files names into different paths that will loop from a list?
From long time I want to find the way how to save as file with a cell reference
I mean
I copy and paste the path in Sheet 2, R1C1
And I want to save my Sheet 1 content as PDF.
How I can make this ?
I have a video for that ua-cam.com/video/SvtfY_LqCtM/v-deo.html
@@BarbHendersonconsulting And just like that going through the comment I find the next one I need! :D
I am having a hard time writing a save button macro for the data in a worksheet and saving it to different files based on the value in one cell. If the value changes it saves the data into another file. I have 23 different files the data could be saved to based on the value of that one cell.
Hi Barb just joined to your channel please also make video on power bi eagerly waiting for power bi series video
The information I need for a file name is in 2 cells, name and invoice number, how do I put the 2 cell ranges together for a file name? Good video very clear. Thank you
happy to help
Guys - watch out! There is one bad thing with that - I made the script and when I tested it well - it closed the file I was maing the code and I lost the code, because the file it saves does not have the VBA codes... so yeah, watch out!
dEJEN DE PONER TITULOS EN ESPAÑOL Y EL VIDEO ESTA GRAVADO EN INGLES.
no soy yo, google traduce los titulos
Hi mam. I have a question.. how to save as workbook from another workbook.
In a same way how to create a folder?
No but I have a video for that ua-cam.com/video/uOEN5OipAsY/v-deo.html
Barb hello!!
I'm trying to use your macro but I have an error here:
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook it's marker with yellow, can you please let me know what I'm doing wrong?
Carlos, Same reply as below, I find if you click in the address bar and copy the file location it always works for me. If you email me at easyexcelanswers@gmail.com I will send you the file that I made the video with and you can work from that if you like.
Great. Thanks for making this video, very helpful. But I don't want to close the active workbook after saving the file. Can anyone please help me out?
just delete "Activeworkbook.close" or comment out
Thankyou this is really helpful but what if I wanted the file format to be csv?
I am not sure, have never tried CSV
Hello. I believe you just replace
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
with
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".csv", FileFormat:=xlCSV
Hi Barb,
I’m able to convert an excel sheet, save it & email it. But, my issue is I don’t know how to send it to someone else for someone to input information, use the macro to send it back. It has my C drive on it. Is there a way to put in a drive that converts when someone else opens it?
Excellent
I have some issues kindly help me to resolve
1: how can i save the new file without micro without micro button
You can go to the developer tab, macros and select the macro and run
Thank you so much for uploading this video.. It helped me a lot...
Hi Barb
Thank you again for streamlining information
I need to do something similar I'm not sure if it is possible to do
Saving a .docx file instead with a specified name (file name=cell value)
A trillion thanks
you should be able to but I have never tried it
@@BarbHendersonconsulting I figured it out, thank you barb
Hey Barb great video thank you so much, is there any way to have the save as file name from two cells instead of one?
you could combine to parts of the name by using "&" symbol ?
I am your subscriber I support your channel thank you
Hi Barb,Thank you very much for your video! Could you help me trouble shoot?I have an Error called Run-time error '1004':Method 'SaveAs' of object '_Workbook' failI have gone over the code several times and have looked through other comments but couldn't find this. I included the "/" at the end of the file name.I appreciate your time,Tyler
if you search Google for this error there are a number of solutions
How can I have the file revert to the original file after hitting the save button?
If you have saved the original file as some other name then you can always go back to it. But there is no "undo"
@@BarbHendersonconsulting I guess what I mean is after saving the file I would like the workbook to clear or reopen the original file
Worked for me. I gt stuck on the difference between xlsm and xlsx. But i'm all good now. Than you for creating this video.
Could not get this code to work today.
what happened?
Great work barb your videos are awesome
I have doubt in the code line active workbook saveas file name
So here my doubts if its possible how we can save in PDF format or any other format
Is there any changes in the code???
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
path + filename, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Loved the video, it worked for me. I’ve been trying to figure another thing out. Is it possible to add conditions to a cell that determine what the save path is? For example, when you click “save file” as in your video. If I wanted a condition that sets a different path based on the value of a cell. Is that possible? For instance, if I wanted to sort out dated forms. If cell “D5” contained the words “mar”(for March) the save path goes to a “March” folder. If cell “D5” contained word “Jan” (for January) then the save path goes to a “January” folder.
I made a video for you ua-cam.com/video/VbYvSALwejE/v-deo.html
Great tutorial very helpful, I'm new to the world of Marcos in excel.
I am trying to stream line my job by learning how to write amazing little programs like this and stop carry over mistakes.
How would one write a code to do the same job essentially, but go one step further to populate information from the original document.
Scenario, there is a "client registry" which has various bits of information regarding the client, and I need to execute a function to take those bits of information and populate them into a template sheet and save as a new file. let me know if you have any advice on this one or links to similar vids.
Love it!really very helpful👍thanx!
Hi Barb! Thanks for the tutorial, I am trying to use it but there is an issue with my code, something to do with the fileformat. I tried to set to code so it saves as a marco enabled one, then a non-macro one, i keep getting an error. see below
Private Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String
path = "S:\Datastore\30. Crude\8. Kapow\"
filename1 = "Exchange Close"
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub
Thanks for your help
Sophie,
Try this
Private Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String
path = "S:\Datastore\30. Crude\8.Kapow\"
filename1 = "Exchange Close"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename1, fileformat:=51, createbackup:= False
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub
51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Hi it worked thank you very much!
Barb,
Awesome videos. I tried using this information on a Trivia Night Score Sheet I created. Only issue I have is that a blank Excel window opens after the save and my original file closes. I'll send an email with more background information. Again, thanks for the very informative videos.
which is the code for the word document?
This code is set up for Excel files
I've made a folder in D drive & mentioned that folder's address in macro, but file is saving in C:\Document instead of this specified location.please help me
Did u get solution for ts.. I m facing the same
i need your help!! the Save command macro works. that's the good part. the bad part is that each saved file overrides the other. we are going to use this to keep a daily report, and each report has to be looked at from time to time.
Adam,
Try this
filename1 =Range ("C4").text & Date
This should add the date to your filename before the xlsx. This should keep them separate unless you save the same file on the same day
Barb
can you please copy paste this code
the code is already in the description of the video.
mere isme error aa raha hai
Hi Barb,
I am getting error in below line
ActiveWorkbook.SaveAs Filename:"path & filename1 & ".xlsx",
Do you have the final "/" on the pathname. It is a common error
Excellent instructive video! I followed the steps and I got the Save button working in a matter of minutes. Thank you.
I also can use this on a button?
Yes, definitely
Thank you! The key for me was adding the .txt after the file name.
Glad it helped!
this not work in win 10 64 bit
Try again, I just tested on my Windows 10 64 bit system and it worked fine!
Great Madam.. Thanks...!
Most welcome 😊
Dear Barb,
Thanks for the macro video. I have a doubt regarding with file save location. This file showing error other user system saving . Because the file location given as my PC. How to solve?
If you click on the address bar and copy the file location it always works for me.
Hi Barb, How can I make it save to an FTP location?
I am not sure. Can you copy the path to the FTP location on your computer?
thanks Barb. at the end after pressing button a blank screen appears. i see it in your vid as well. how can that be deleted along with hitting save button ?
recent subscriber
Tom
I suppose you can totally delete at the end of the code: "Application.DisplayAlerts = True
ActiveWorkbook.Close"
AMAZING video. You earned a subscriber and a thumbs up.
Hi barb,
I have a issue. I am automating a webpage to download a document from it using a vba script. I can click the download option but upon clicking i get a pop below the window like, { open, save as and close}. Alt + S works fine for saving the document. Can you provide me a solution for how to go about it! I have tried using send keys but in vain
The only thing that I would suggest is "Application.DisplayAlerts = False" at the beginning and "Application.DisplayAlerts = True " at the end of the code. Good luck
Thanks Barb
Application.Sendkeys"(% s)",true
Is this correct for saving a file? I am searching it in many forum, but cant find a solution
I have used previously for saving a large number of files, so that you do not get the prompt to save for each one
Can you please share the code as to save it? Because i enter some 10 values in excel and for each value it has to download the document! If you can share as to how to proceed further. It would be really helpful
Regards,
nelzy
Private Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String
path = "C:\Users\Barb\Documents\excel\test saving xlsm\"
filename1 = Range("D3")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsm", FileFormat:=52, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub
Thanks a lot Barbara for this video. I used it. perfect lob.
Glad it helped!
thank you for the efforts it was very clear example
Glad you liked it
Hi Barb, is there a way to save a file without any name parameter to a particular directory. I already have the filename but different each time.
I hope this is what you need, I have attached the video
ua-cam.com/video/GgTQRF7cf94/v-deo.html
When I hover over the button, it doesn’t turn over for me to click on it. It has the symbol that allows me to reshape
You're still in "Design Mode", under "Developer" area. Press that button again to exit and you'll be fine. A lot of time since your comment, and I hope you've found the solution already. So perhaps useful to other viewers....
Thank you so much
Any time
Say I have a value in A1 called Test and I want it to create a folder called Test where it saves itself as Test.xlsx in the defined path, is there a possibility to do this? I've been trying to look for a way to accomplish this for like 3 days.
I have a video for that ua-cam.com/video/uOEN5OipAsY/v-deo.html
You're an absolute hero!
My next step is to my sheet to save itself with a name from a cell inside the folder it just created. Currently it's save both the folder and the file in the same folder.
How can I change the naming convention. I don't want it to pickup from excel sheet. Please suggest
You could add a message box asking the user to enter a name
Barb Henderson hey, this will not work for 2000 files which has different naming convention. Besides I am not good at macro. Do u have tutorial? I been to your website it was more of Excel. I really like the way I do. I want to learn to write VBA
Thank you .
Welcome 😊
Excellent - very clear instructions given at a good pace.
greattt
thanks!
Thnak you
Welcome!
thank you so much,it helped me alot...
does this work with a multiple worksheet workbook?
do you have to define which worksheet your using or is it associated with the command button ?
to be safe I would specify the worksheet.
Mat - i did mine on 5 sheets of a workbook and it saved the whole workbook
@@BarbHendersonconsulting Please advise how to select a particular worksheet to save. Currently it's saving the whole workbook which has 4 sheets. I want it to save only a particular sheet.
Thank You lady!!!
This is the one I've been looking for!
Glad it was helpful!
@@BarbHendersonconsulting do you have a one click macro how to save as PDF somewhere laying around maybe? 👀😁
Thanks
How to save as PDF file ??
Thanks! Very nice!
You've made my year. Thank you!
Glad I could help!
Thanks Barb!
what if I want to save it to onedrive?
Select the path to the one drive
Do not
Tks!
thanks