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>

 

[Fluff]

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

Rather than having mutiple checkboxes you could have just the one but use an if statement

If Application.PathSeparator = ":" Then
  Mac Code
Else
  PC code

End If

 

[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

Well, it's renaming the file, as intended, but it is not saving anywhere now. And, I'm getting the error below:

Microsoft Visual Basic 400

 

[Jmoz092]

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

Rather than having mutiple checkboxes you could have just the one but use an if statement

If Application.PathSeparator = ":" Then
  Mac Code
Else
  PC code

End If

Can that just go at the bottom of the code, before end sub, or does it need to be just below the dims?

 

[Fluff]

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

It needs to be like this

Sub saveastest()

    Dim thisfile As String
    Dim FilePth As String
    
    If Application.PathSeparator = ":" Then
        [COLOR=#ff0000]FilePth = "data:officeforms:ultrasoundorders:" & Range("AC1").Value & ":" & Range("AC2").Value & ":"[/COLOR]
        thisfile = Range("AC3").Value
        ActiveWorkbook.SaveAs FilePth & thisfile, FileFormat:=53
    Else
        [COLOR=#0000ff]FilePth = "z:\data\officeforms\ultrasoundorders\" & Range("AC1").Value & "\" & Range("AC2").Value & "\"[/COLOR]
        thisfile = Range("AC3").Value
        ActiveWorkbook.SaveAs FilePth & thisfile, FileFormat:=52
    End If

End Sub

Where the red item is a valid Mac address/path & the blue is a valid Pc address/path

 

[Jmoz092]

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

It needs to be like this

Sub saveastest()

    Dim thisfile As String
    Dim FilePth As String
    
    If Application.PathSeparator = ":" Then
        [COLOR=#ff0000]FilePth = "data:officeforms:ultrasoundorders:" & Range("AC1").Value & ":" & Range("AC2").Value & ":"[/COLOR]
        thisfile = Range("AC3").Value
        ActiveWorkbook.SaveAs FilePth & thisfile, FileFormat:=53
    Else
        [COLOR=#0000ff]FilePth = "z:\data\officeforms\ultrasoundorders\" & Range("AC1").Value & "\" & Range("AC2").Value & "\"[/COLOR]
        thisfile = Range("AC3").Value
        ActiveWorkbook.SaveAs FilePth & thisfile, FileFormat:=52
    End If

End Sub

Where the red item is a valid Mac address/path & the blue is a valid Pc address/path

It works perfectly when saving from a Mac but I'm getting a 400 error code when I move to a windows workstation and try to click the save as button with this macro assigned.