VBA linked to a check box to save excel file

J

Jmoz092

Board Regular
Joined
Sep 8, 2017
Messages
184
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
  2. MacOS
Hi , I'm having trouble creating a check box to "save as" for one of my excel projects. I want the VBA to generate the name of the file based on some cells within a sheet of the workbook. Also,some of the save path on my network can be determined by data within some cells on the same sheet. I've attempted to write a code to do this, but am not having any luck. Any help/education would be greatly appreciated.

<code>
Sub saveastest()
Dim thisfile As Range
ChDir “z:\pathfolder1\pathfolder2\pathfolder3\” & Range(“AC1”)\” & Range(“AC2”)\.Value, FileFormat:=51"
thisfile = Range("AC3").Value
ActiveWorkbook.SaveAs Filename:=thisfile, FileFormat:=51
End Sub
</code>

 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Re: Help with VBA linked to a check box to save excel file

I wanted to add a bit more clarity to were the data is coming from on the sheet and also a bit more information on the network path that the file is saved to...

On the sheet, I have a date in cell e4 formatted as mm,dd,yy.
We rename the sheet from "Sheet 1" to a name (last name, first name). It's always: Last name comma space first name. This sheet name is then transposed to cell b7 with the formula:
Code: 
[COLOR=#000000][FONT=Lucida Grande]=MID(CELL[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande]"filename",[/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A2[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande],FIND[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande]"]",CELL[/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande]"filename",[/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A2[/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande]+1,20)[/FONT][/COLOR]
Then, in cell b6 we take part of that sheet name and fill a cell with the first name using the formula:
Code: 
[COLOR=#000000][FONT=Lucida Grande]=RIGHT([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]B7[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande],LEN[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]B7[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande]-FIND[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande]", ",[/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]B7[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande]-1)[/FONT][/COLOR]
Then, in cell d6 we take the rest of that sheet name and fill a cell with the last name using the formula:
Code: 
[COLOR=#000000][FONT=Lucida Grande]=LEFT([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]B7[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande],FIND[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande]", ",[/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]B7[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande]-1)[/FONT][/COLOR]
We have 2 other date cells, I4 & I5, that are formatted the same as e4 (mm,dd,yy).

Ive used this data to concatenate it a number of different ways, in a separate area of the sheet that are identical to many of my needs....file name, file paths, etc.
In cell aa1, I have the formula:
Code: 
[COLOR=#000000][FONT=Lucida Grande]=TEXT([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]E4[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande],"mmmm")[/FONT][/COLOR]
In cell aa1, I have the formula:
Code: 
[COLOR=#000000][FONT=Lucida Grande]=TEXT([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]E4[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande],"dd")[/FONT][/COLOR]
In cell aa3, I have the formula:
Code: 
[COLOR=#000000][FONT=Lucida Grande]=TEXT([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]E4[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande],"yyyy")[/FONT][/COLOR]

In cell ac1, I have the formula:
Code: 
[COLOR=#000000][FONT=Lucida Grande]=CONCATENATE([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]AA1[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande]," ", [/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]AA3[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande])[/FONT][/COLOR]
In cell ac2, I have the formula:
Code: 
[COLOR=#000000][FONT=Lucida Grande]=CONCATENATE([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]AA1[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande], " ", [/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]AA2[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande], ",", [/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande]AA3[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande])[/FONT][/COLOR]
In cell ac3, I have the formula:
Code: 
[COLOR=#000000][FONT=Lucida Grande]=CONCATENATE([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]D6[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande], ", ", [/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]B6[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande], " ", [/FONT][/COLOR][COLOR=#AB30D6][FONT=Lucida Grande]AA1[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande], "-", [/FONT][/COLOR][COLOR=#A54A29][FONT=Lucida Grande]AA2[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande], "-", [/FONT][/COLOR][COLOR=#33AF4A][FONT=Lucida Grande]AA3[/FONT][/COLOR][COLOR=#000000][FONT=Lucida Grande])[/FONT][/COLOR]

I tried to set these cells up so they can be used in the VBA like:
Code: 
[COLOR=#454545][FONT=Helvetica Neue][SIZE=2]ChDir “z:\pathfolder1\pathfolder2\pathfolder3\” & Range(“AC1”)\” & Range(“AC2”)\.Value, FileFormat:=51"[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=Helvetica Neue][SIZE=2]    thisfile = Range("AC3").Value[/SIZE][/FONT][/COLOR]

File paths 1,2, and 3 will have constant names. File paths 4 and 5 can be determined (and are currently named as such) by cells AC1 and AC2, respectively.
The file name to go into file path 5 is determined and named by cell AC3.

I'm using excel for Mac 2011, but some on our network are using MS 365 with varying versions. So far, we're not having any access or usability issues with any of the users because of this. Also, I'm very new (< 1 month) to VBA coding and have only read what I can find here and other Google searches, as well as a lot of an VBA tutorial by Tom Urtis. So, I apologize if the questions, or my thought processes leave a lot to be desired. Thanks for any help/education in advance.
 
Upvote 0
Re: Help with VBA linked to a check box to save excel file

Try this
Code: 
Sub saveastest()

    Dim thisfile As String
    Dim FilePth As String
    
    FilePth = "z:\pathfolder1\pathfolder2\pathfolder3\" & Range("AC1").Value & "\" & Range("AC2").Value & "\"
    thisfile = Range("AC3").Value
    ActiveWorkbook.SaveAs Filename:=FilePth & thisfile, FileFormat:=51

End Sub
 
Upvote 0
Re: Help with VBA linked to a check box to save excel file

Thanks Fluff...

There is maybe a problem with this line of code:
Code: 
ActiveWorkbook.SaveAs Filename:=FilePth & thisfile, FileFormat:=51

Should FilePth be different? Should it be:

Code: 
 ActiveWorkbook.SaveAs Filename:=thisfile, FileFormat:=51
 
Upvote 0
Re: Help with VBA linked to a check box to save excel file

I got it to saveAs and it took data from the specified cells, but it save it to the folder that I opened the file from (My Documents)...on my laptop, not the network. This is the code I used...

Code: 
Sub saveastest()


    Dim thisfile As String
    Dim FilePth As String
    
    FilePth = "z:\filepath1\filepath2\filepath3\" & Range("AC1").Value & "\" & Range("AC2").Value & "\"
    thisfile = Range("AC3").Value
    ActiveWorkbook.SaveAs Filename:=thisfile, FileFormat:=51


End Sub
 
Upvote 0
Re: Help with VBA linked to a check box to save excel file

My mistake it should be
Code: 
Sub saveastest()


    Dim thisfile As String
    Dim FilePth As String
    
    FilePth = "z:\filepath1\filepath2\filepath3\" & Range("AC1").Value & "\" & Range("AC2").Value & "\"
    thisfile = Range("AC3").Value
    ActiveWorkbook.SaveAs FilePth & thisfile, FileFormat:=51


End Sub
 
Upvote 0
Re: Help with VBA linked to a check box to save excel file

That's not doing it. It's renaming the file as the entire file path plus the specified cells, but not saving it anywhere. I'm getting a run time error with that code Fluff. Do you know why??

What is the difference between the code I used that save it to the incorrect place and yours":

Sub saveastest()


Dim thisfile As String
Dim FilePth As String

FilePth = "z:\filepath1\filepath2\filepath3" & Range("AC1").Value & "" & Range("AC2").Value & ""
thisfile = Range("AC3").Value
ActiveWorkbook.SaveAs Filename:=thisfile, FileFormat:=51


End Sub


Sub saveastest()


Dim thisfile As String
Dim FilePth As String

FilePth = "z:\filepath1\filepath2\filepath3" & Range("AC1").Value & "" & Range("AC2").Value & ""
thisfile = Range("AC3").Value
ActiveWorkbook.SaveAs FilePth & thisfile, FileFormat:=51


End Sub</pre>
 
Upvote 0
Re: Help with VBA linked to a check box to save excel file

Sorry, misused the code brackets...

That's not doing it. It's renaming the file as the entire file path plus the specified cells, but not saving it anywhere. I'm getting a run time error with that code Fluff. Do you know why??

What is the difference between the code I used that save it to the incorrect place and yours":

this one works but saves it to where the file was opened from...
Code: 
[COLOR=#333333]Sub saveastest()[/COLOR]


[COLOR=#333333]Dim thisfile As String[/COLOR]
[COLOR=#333333]Dim FilePth As String[/COLOR]

[COLOR=#333333]FilePth = "z:\filepath1\filepath2\filepath3" & Range("AC1").Value & "" & Range("AC2").Value & ""[/COLOR]
[COLOR=#333333]thisfile = Range("AC3").Value[/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs Filename:=thisfile, FileFormat:=51[/COLOR]


[COLOR=#333333]End Sub[/COLOR]


this one is giving me the runtime error
Code: 
[COLOR=#333333]Sub saveastest()[/COLOR]


[COLOR=#333333]Dim thisfile As String[/COLOR]
[COLOR=#333333]Dim FilePth As String[/COLOR]

[COLOR=#333333]FilePth = "z:\filepath1\filepath2\filepath3" & Range("AC1").Value & "" & Range("AC2").Value & ""[/COLOR]
[COLOR=#333333]thisfile = Range("AC3").Value[/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs FilePth & thisfile, FileFormat:=51[/COLOR]


[COLOR=#333333]End Sub[/COLOR]
 
Last edited:
Upvote 0
Re: Help with VBA linked to a check box to save excel file

Your code is only specifying the file, so the file will be saved in the active directory.
My code is specifying both path name & file name, so the file should be saved in the relevant directory.

As to why it's not working, what error message do you get & on what line?
Also check that the Values in AC1 & AC2 are correct & that the relevant folders exist.
Also check that the filename in AC3 is a valid filename
 
Upvote 0
Re: Help with VBA linked to a check box to save excel file

Fluff said:
Your code is only specifying the file, so the file will be saved in the active directory.
My code is specifying both path name & file name, so the file should be saved in the relevant directory.

As to why it's not working, what error message do you get & on what line?
Also check that the Values in AC1 & AC2 are correct & that the relevant folders exist.
Also check that the filename in AC3 is a valid filename
Click to expand...


Run-time error '1004:
Microsoft excel cannot access the file 'z987b100'.There are several possible reasons....etc,etc

Also, it changes the file name to:
"\filepath1\filepath2\filepath3(no space or separator)AC1(no space or separator)AC2(no space or separator)AC3"

Upon debug, it's highlighting this line of code as yellow:
Code: 
ActiveWorkbook.SaveAs FilePth & thisfile, FileFormat:=51

It's renaming the file and not saving it. Strange?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Conditional Formatting changes from XLSM to XLSX when I use VBA code to save a new file
Replies
1
Views
471
JerrExcel
JerrExcel
T
  • Question Question
Save As Dialog Box with File Name Predetermined for Ppt file through Excel
2
Replies
10
Views
132
tbakbradley
T
K0st4din
  • Question Question
Saving a file taken from cells in a workbook and asking where to save it and in what format
Replies
1
Views
568
K0st4din
K0st4din
C
  • Question Question
VBA to save file to SharePoint and attach to outlook template saved in SharePoint
Replies
1
Views
73
Micron
M
L
  • Question Question
VBA to save newly created workbook failing - creates a "file" document
Replies
2
Views
212
Sunjinsak
S

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top