VBA linked to a check box to save excel file

[Jmoz092]

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>

 

[Jmoz092]

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:

[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:

[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:

[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:

[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:

[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:

[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:

[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:

[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:

[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:

[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.

 

[Fluff]

Re: Help with VBA linked to a check box to save excel file

Try this

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

 

[Jmoz092]

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:

ActiveWorkbook.SaveAs Filename:=FilePth & thisfile, FileFormat:=51

Should FilePth be different? Should it be:

 ActiveWorkbook.SaveAs Filename:=thisfile, FileFormat:=51

 

[Jmoz092]

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...

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

 

[Fluff]

Re: Help with VBA linked to a check box to save excel file

My mistake it should be

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

 

[Jmoz092]

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>

 

[Jmoz092]

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...

[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

[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]

 

[Fluff]

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

 

[Jmoz092]

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

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:

ActiveWorkbook.SaveAs FilePth & thisfile, FileFormat:=51

It's renaming the file and not saving it. Strange?