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

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

Yes, the file path and name of the file in that code string is the way it's supposed to be. But the problem is that the file is now named that whole code string. And, it's not actually saving or savingAs now.

The name of the file now is:
"z:\filepath1\filepath2\filepath3\September 2017\September 20,2017\lastName, firstName September-20-2017"

The name of the file should be:
"lastName, firstName September-20-2017"

Also, I want to thank you all for your time,help, and education on this issue.

 

[Norie]

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

That doesn't quite add up, can you post the exact current code?

 

[Fluff]

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

Try changing

FileFormat:=51

to

FileFormat:=52

From
https://www.rondebruin.nl/mac/mac020.htm

 

[Jmoz092]

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

That doesn't quite add up, can you post the exact current code?

in module 1, the code is (exactly):

Sub saveastest()




    Dim thisfile As String
    Dim FilePth As String
    
    FilePth = "z:\data\officeforms\ultrasoundorders\" & 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

Try changing

FileFormat:=51

to

FileFormat:=52

From
https://www.rondebruin.nl/mac/mac020.htm

When I change it to File Format 52, a box pops up that visual basic macros will be removed if I save the file in this format, etc, etc. When I cancel save, the debugger highlights the last line of the VBA as yellow.

 

[Fluff]

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

In that case use 53

 

[Jmoz092]

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

In that case use 53

I used 57, per Ron's layout (I'm using Excel Mac 2011), and then took out "Z:" and I got the file to save to the top level of our network directory, but it named the file the whole length of the save path concatenated with the intended name of the file

Sub saveastest()




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




End Sub

 

[Norie]

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

I'm using Excel Mac 2011

Think that might be where the fault lies, Mac and Windows file paths aren't the same.

Here's an example of SaveAs for Mac 2011 from Ron de Bruin's site.

ActiveWorkbook.SaveAs "Macintosh HD:Users:YourUserName:Desktop:ron.xlsm", fileformat:=53

 

[Jmoz092]

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

Think that might be where the fault lies, Mac and Windows file paths aren't the same.

Here's an example of SaveAs for Mac 2011 from Ron de Bruin's site.

ActiveWorkbook.SaveAs "Macintosh HD:Users:YourUserName:Desktop:ron.xlsm", fileformat:=53

Always something simple huh? I'll give this a go and report back.

This fileformat number applies to only the workstation that is using excel to create/edit/save the file at the moment right? We have Mac and MS workstations throughout our office and also utilize different versions of Excel at different workstations.

So I'll need to create multiple check boxes to use for each of these variants then?

 

[Jmoz092]

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

Think that might be where the fault lies, Mac and Windows file paths aren't the same.

Here's an example of SaveAs for Mac 2011 from Ron de Bruin's site.

ActiveWorkbook.SaveAs "Macintosh HD:Users:YourUserName:Desktop:ron.xlsm", fileformat:=53

With the user utilizing excel 2011 for Mac, the code should then be:??

[COLOR=#454545][FONT=Helvetica Neue][I]Sub saveastest()[/I][/FONT][/COLOR]
[COLOR=#454545][FONT=Helvetica Neue]
[/FONT][/COLOR]
[COLOR=#454545][FONT=Helvetica Neue]
[/FONT][/COLOR]
[COLOR=#454545][FONT=Helvetica Neue][I]    Dim thisfile As String[/I][/FONT][/COLOR]
[COLOR=#454545][FONT=Helvetica Neue][I]    Dim FilePth As String[/I][/FONT][/COLOR]
[COLOR=#454545][FONT=Helvetica Neue][I]    FilePth = "z:filepath1:filepath2:filepath3:” & Range("AC1").Value & “:” & Range("AC2").Value & “:”[/I][/FONT][/COLOR]
[COLOR=#454545][FONT=Helvetica Neue][I]    thisfile = Range("AC3").Value[/I][/FONT][/COLOR]
[COLOR=#454545][FONT=Helvetica Neue][I]    ActiveWorkbook.SaveAs FilePth & thisfile, FileFormat:=53[/I][/FONT][/COLOR]
[COLOR=#454545][FONT=Helvetica Neue]
[/FONT][/COLOR]
[COLOR=#454545][FONT=Helvetica Neue]
[/FONT][/COLOR]
[COLOR=#454545][FONT=Helvetica Neue][I]End Sub[/I][/FONT][/COLOR]