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>

 

[Norie]

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

What is the value of FilePth & thisfile?

You can see that in the Immediate Window (CTRL+G) if you add this before the SaveAs code.

Debug.Print FilePth & thisfile

PS Are you making sure FilePth ends with a '\'? If you don't then VBA will concatenate the last folder in the path with the file name and try and use that as the filename to save as.

 

[Fluff]

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

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"

The separators have gone. I just assumed that it was the board stripping them out, but maybe not.

Sub saveastest()


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


End Sub

Remove the underscores

 

[Jmoz092]

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

The separators have gone. I just assumed that it was the board stripping them out, but maybe not.

Sub saveastest()


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


End Sub

Remove the underscores

Maybe I worded that poorly. I meant that when I click the check box with this macro inserted into it, it changes the file name to:

"z:filepath1\filepath2\filepath3\datafromcellAC1datafromcellAC2datafromcellAC3"

Whatever the words associated with those files paths is transposed into the new file name. IOW, the new file name is a concatanation of the entire file path and the specified cells within the macro code.

 

[Norie]

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

What exactly do you see in the Immediate Window when you try this like I suggested earlier?

Debug.Print FilePth & thisfile

 

[Fluff]

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

Are the values in A1 & AC sub folders?
If so you are missing the path separators.
My code in post#12 shows 3 \ in red followed by an _
You need to remove the _ & ensure that the separator \ remains

 

[Jmoz092]

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

What exactly do you see in the Immediate Window when you try this like I suggested earlier?

Debug.Print FilePth & thisfile

I was just trying that...not seeing anything happen

 

[Norie]

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

Did you add that line of code into your code before the SaveAs and open the Immediate Window with CTRL+G to see what's output?

 

[Jmoz092]

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

Did you add that line of code into your code before the SaveAs and open the Immediate Window with CTRL+G to see what's output?

Debug.Print FilePth & thisfile
z:\filepath1\filepath2\filepath3\September 2017\September 20,2017\lastName, firstName September-20-2017

 

[Norie]

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

Is that a valid path and is the filename what you expected?

 

[Jmoz092]

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

Are the values in A1 & AC sub folders?
If so you are missing the path separators.
My code in post#12 shows 3 \ in red followed by an _
You need to remove the _ & ensure that the separator \ remains

The values in cells AC1 and AC2 are the names of subfolders in our network. They were named in a accordance with data that is filled into areas of our sheet...just last name, first name, day, month and year. They data is entered via a date picker in cell e4. The sheet name is changed and the last name and first name are taken from that. Then I have the AC cells pick the other cells apart and concatanate it in different ways so as to match the exact layout as the names of the networks subfolders. I did this because the form is used daily and dated differently and then stored on the network folders named in a matching manner by month (month yyyy), and day (month dd,yyyy). Then the name of the file is to become: "LastName, FirstName mm-dd-yy".

Yes, the \ are in the code that I'm using. Not sure why they're not showing in my posts, sorry.