none
VBA Language specific? RRS feed

  • Question

  • Hi,

    I am new to VBA so this may be due to this but I have created a workbook with VBA that uses a SaveAS to a specific pathway. I use the pathway name rather than a drive letter. I have no issues running the macro in the UK and the US, however with our European clients I am getting a 1004 error with SaveAs being the issue. I was hoping for some clarity on what could cause this and would it be due to the language of Excel as specific countries I am having issues with are IT, ES and FR.

    Andy

    Monday, April 30, 2018 11:01 AM

All replies

  • Post the code so we can see what it is that you are doing.

    Graham Mayor - Word MVP
    www.gmayor.com

    Monday, April 30, 2018 11:14 AM
  • Sub Documentsaving()

    Application.DisplayAlerts = False

        Dim FP As String
        Dim FN As String
        Dim Wkb1 As Workbook
        Dim Sht1 As Worksheet
        Dim sDate As String
        Dim sYear As String
        Dim Rng1 As Range
        Dim Rng2 As Range
        Dim Nme1 As Range

            Set Wkb1 = ThisWorkbook
            Set Sht1 = Wkb1.Worksheets(1)
            Set Rng1 = Wkb1.Worksheets(1).Range("G1")
            Set Rng2 = Wkb1.Worksheets(1).Range("B1")
            Set Nme1 = Wkb1.Worksheets(1).Range("B2")
           
            sDate = Format(Sheets(1).Range("B1"), "MMMM")
            sYear = Format(Sheets(1).Range("B1"), "YYYY")
            FP = "\\Drivepath as it appears as a drive name not as the letter\" & sYear & "\" & sDate & "\" & Nme1

        ActiveWorkbook.SaveAs Filename:=FP & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
       
    Application.DisplayAlerts = True

    End Sub

    Monday, April 30, 2018 11:21 AM
  • It needs to be the drive letter and not an alias path name.

    Kind Regards, Rich ... http://greatcirclelearning.com

    Monday, April 30, 2018 9:50 PM
  • There is no reason why the code should not work, provided the path exists and it is writable and that you have not mixed up ThisWorkbook (the workbook with the code) and ActiveWorkbook (the workbook being processed).

    The following certainly works on my network (and will create the folders if missing).

    It takes no account of illegal filename characters, so if those may be present, you need to add code to remove them before trying to save.

    Option Explicit
    
    Sub Documentsaving()
        Dim FP As String
        Dim FN As String
        Dim Wkb1 As Workbook
        Dim Sht1 As Worksheet
        Dim sDate As String
        Dim sYear As String
        Dim Rng1 As Range
        Dim Rng2 As Range
        Dim Nme1 As Range
        
        Application.DisplayAlerts = False
        Set Wkb1 = ActiveWorkbook
        Set Sht1 = Wkb1.Worksheets(1)
        Set Rng1 = Wkb1.Worksheets(1).Range("G1")
        Set Rng2 = Wkb1.Worksheets(1).Range("B1")
        Set Nme1 = Wkb1.Worksheets(1).Range("B2")
    
        sDate = Format(Sheets(1).Range("B1"), "MMMM")
        sYear = Format(Sheets(1).Range("B1"), "YYYY")
        FP = "\\<name>\" & sYear & "\" & sDate & "\" & Nme1 & ".xlsm"
        CreateFolders "\\<name>\" & sYear & "\" & sDate
        ActiveWorkbook.SaveAs FileName:=FP, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        Application.DisplayAlerts = True
    End Sub
    
    Private Function CreateFolders(strPath As String)
    Dim strTempPath As String
    Dim iPath As Integer
    Dim vPath As Variant
    Dim bExists As Boolean
    Dim fso As Object
    
        Set fso = CreateObject("scripting.filesystemobject")
    
        vPath = Split(strPath, "\")
        strPath = vPath(0) & "\"
        For iPath = 1 To UBound(vPath)
            strPath = strPath & vPath(iPath) & "\"
            If Not Right(strPath, 2) = "\\" Then
                bExists = fso.FolderExists(strPath)
                On Error Resume Next
                If Not bExists Then fso.CreateFolder strPath
            End If
        Next iPath
    lbl_Exit:
        Set fso = Nothing
        Exit Function
    End Function
    


    Graham Mayor - Word MVP
    www.gmayor.com


    Tuesday, May 1, 2018 6:17 AM