locked
Excel crashing when saving excel sheet as .csv file via VBA with loop RRS feed

  • Question

  • Dear all,
    I have seen various questions regarding this subject, but haven't seen the exact situation.  I'm fairly new to VBA Excel but have managed to create various routines that run successfully exept this routine.
    I have created a price list with VBA code that runs through a range of customer numbers. The code creates a price list for every customer in the list until the last customer's list has been created. The code creates price lists in various formats, based on the format setting with each individual customer.  Per loop a new customer price list is created. The available formats are .xlsx, .xls, .xml and .csv   All formats run perfectly, except .csv.  After saving every first or every second . csv file excel crashes.  If I run the code only one time, the pricelist is saved correctly as .csv.
    I must add to this request that files are saved on two different servers at the same time. If I save files only on server 1, all runs well. As soon as server 2 is involved, Excel crashes.  Is it my code or is it some setting on server 2 (FTP) server?  I can't find the reason why... have been searching the internet for weeks now.... I'm getting really frustrated here.  I hope somebody has the magic answer for me.

    In the code below fExt is the file extension = ".csv"  and the fFormat=6, the enumeration for xlCSV

    To be sure, below the part of the code that saves the file as .csv.
    A big thank you VERY much for reading my post and hopefully a solution.

    Kind regards,

    Pascal

        '-->Save file with Date-Time Stamp to file server in customer folder
                Application.DisplayAlerts = False
                fPathname = Path & myfilename & "_" & format(Now, "yyyymmdd") & "_" & format(Now, "hhmmss") & fExt
                ActiveWorkbook.SaveAs FileName:=fPathname, FileFormat:=fFormat, CreateBackup:=False, local:=True
                
           '-->Save file without Date-Time Stamp to file server in customer folder - Overwrite when existing
                Application.DisplayAlerts = False
                fPathname = Path & myfilename & & fExt
                ActiveWorkbook.SaveAs FileName:=fPathname, FileFormat:=fFormat, CreateBackup:=False, local:=True
                
                
           '-->Save file without Date-Time Stamp to Digitek FTP Server in Customer folder - Overwrite when existing
                If ThisWorkbook.Worksheets("Cust.Settings").Range("C40") = "Yes" Then
      
                    If ThisWorkbook.Worksheets("Cust.Settings").Range("C41") = "SERVERNAME" Then
                        Application.DisplayAlerts = False
                        FTPPathname = FTPPath & myfilename & fExt
                        ActiveWorkbook.SaveAs FileName:=FTPPathname, FileFormat:=fFormat, CreateBackup:=False, local:=True
                        End If
                End If
                
            '-->Close price file ------------------------------------------------------------------------------------------
                
                Application.DisplayAlerts = False
                ActiveWorkbook.Close savechanges:=False
                



    • Edited by P_Willems Thursday, June 18, 2020 10:23 AM
    Thursday, June 18, 2020 10:04 AM