locked
Problem to SaveAs line RRS feed

  • Question

  • Hi,

    Further to this, what can be reason to error

    due to SaveAs line below

        Dim Time0 As Boolean, cnt0 As Integer, File0 As String, Title0 As String, Window0 As String, RowID0 As String, Var1 As String, Var2 As String, Var3 As String, Var4 As String, Var5 As String, Var6 As String, Var7 As String, Var8 As String, Var9 As String, Var10 As String, Var11 As String
        Time0 = True: cnt0 = 1: File0 = Application.ActiveWorkbook.Path & "\Departure List (of People already left).xls": Title0 = "Departure List (of People already left)": Window0 = "Departure List (of People already left).xls"
        
    Rep0:   'Windows(Window1).Activate
        'Debug.Print Window1
        'Application.Workbooks(Window1).Activate
        With ThisWorkbook.Worksheets("Main Sheet").Range("AJ1:AJ50000")
            Set c = .Find(Search0, LookIn:=xlValues)
            If Not c Is Nothing Then
            
                Var1 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 3).Value
                Var2 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 4).Value
                Var3 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 5).Value
                Var4 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 6).Value
                Var5 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 7).Value
                Var6 = CStr(ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 8).Value)
                Var7 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 9).Value
                Var8 = CStr(ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 16).Value)
                Var9 = CStr(ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 17).Value)
                Var10 = CStr(ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 19).Value)
                Var11 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 20).Value
                
                RowID0 = CStr(c.Row) & "CC"
                'Windows(Window0).Activate
                Application.Workbooks(Window0).Activate
                If cnt0 = 1 Then
                    Set HOBook = Workbooks.Add
                    With HOBook
                        .Title = Title0
                        .Subject = Title0
                        .SaveAs Filename:=File0, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled
                        .Close SaveChanges:=False
                    End With
                    


    Many Thanks & Best Regards, Hua Min

    Friday, March 2, 2018 9:03 AM

Answers

  • Hello HuaMin Chen,

    I note that there is a command  GoTo Rep0 in your code, so the code "Application.Workbooks(Window0).Close SaveChanges:=False" will be executed multiple times. However, since the file is closed in first time, it will get error to close workbook with a no-exist workbook name.

    I would suggest you move the command to below if statement so it will be only executed only once time.

    Such as 

    If cnt0 = 1 Then
                Application.Workbooks(Window0).Close SaveChanges:=False
                    Set HOBook = Workbooks.Add
                    With HOBook
                        .Title = Title0
                    ...
                    ...

    If it does not work for you, please share the excel file and detail the reproduce steps so we could reproduce your issue.

    By the way, it seems you have taken the solution in another thread Problem to ColumnWidth . If it does work for you, please mark helpful reply to close that thread.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jackson_1990 Tuesday, March 6, 2018 9:27 AM
    Tuesday, March 6, 2018 7:36 AM

All replies

  • works for me.

    just be sure what's in your variabel File0. is it a valid path? do you have write access to the dir?

    Friday, March 2, 2018 9:10 AM
  • Here is the value

    File0 = Application.ActiveWorkbook.Path & "\Departure List (of People already left).xls"

    and such file is existing and I can also write to it. Why is there an error in above?


    Many Thanks & Best Regards, Hua Min

    Friday, March 2, 2018 9:22 AM
  • Hello HuaMin Chen,

    >>Application.Workbooks(Window0).Activate

    According to this code and the Window0, I think there is a workbook named "Departure List (of People already left).xls" opened now, right? However, it seem you use the same name to SaveAs the new workbook, I think it is not a good idea.

    I would suggest you close the opened "Departure List (of People already left).xls" workbook and then save the HOBook. Or you could use another name for HOBook.

    Besides, you specific a xls extension for the file and specific a type xlOpenXMLWorkbookMacroEnabled which extension is xlsm. I would suggest you figure out which type you need and set it correctly.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 2, 2018 10:04 AM
  • Hi,

    Sorry, how to ensure that the same WorkBook would be close, after having created it like

                    Set HOBook = Workbooks.Add
                    With HOBook
                        .Title = Title0
                        .Subject = Title0
                        '.SaveAs Filename:=File0, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled
                        .SaveAs Filename:=File0, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                        .Close SaveChanges:=False
                    End With
    


    Many Thanks & Best Regards, Hua Min

    Saturday, March 3, 2018 3:36 PM
  • Hi,

    Any help?


    Many Thanks & Best Regards, Hua Min

    Monday, March 5, 2018 2:57 AM
  • Hello HuaMin Chen,

    >>Application.Workbooks(Window0).Activate

    Window0 is  "Departure List (of People already left).xls" so we know there is a  "Departure List (of People already left).xls" workbook opened now. If you manually to open a new document and then try to save it use a same name as  "Departure List (of People already left).xls", you will get error like this.

    So you have to use another name to save HOBook or you have to Close the opened "Departure List (of People already left).xls" workbook so you could save the HOBook.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 5, 2018 7:00 AM
  • >> So you have to use another name to save HOBook or you have to Close the opened "Departure List (of People already left).xls" workbook so you could save the HOBook.

    Can you share the codes to close it?


    Many Thanks & Best Regards, Hua Min

    Monday, March 5, 2018 7:39 AM
  • Hello HuaMin Chen,

    Application.Workbooks(Window0).Activate

    Application.Workbooks(Window0).Save Application.Workbooks(Window0).Close SaveChanges:=False If cnt0 = 1 Then Set HOBook = Workbooks.Add With HOBook .Title = Title0 .Subject = Title0 .SaveAs Filename:=File0, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled .Close SaveChanges:=False End With

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 5, 2018 7:54 AM
  • Sorry to that I encounter

    to 2nd line below

                ...
                Application.Workbooks(Window0).Activate
                Application.Workbooks(Window0).Save
                Application.Workbooks(Window0).Close SaveChanges:=False
                If cnt0 = 1 Then
                    Set HOBook = Workbooks.Add
                    With HOBook
                        .Title = Title0
                        .Subject = Title0
                        .SaveAs Filename:=File0, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled
                        '.SaveAs Filename:=File0, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                        .Close SaveChanges:=False
                    End With
    


    Many Thanks & Best Regards, Hua Min

    Monday, March 5, 2018 8:09 AM
  • Hello HuaMin Chen,

    Try to comment out this line and close the workbook directly. Or you could edit File0 with another name to save the HOBook.

    If they both do not work for your, I would suggest you share an excel file with your code and detail your produce steps so we could try to reproduce your issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jackson_1990 Monday, March 5, 2018 9:22 AM
    • Unmarked as answer by Jackson_1990 Tuesday, March 6, 2018 4:16 AM
    Monday, March 5, 2018 8:27 AM
  • Hello HuaMin Chen,

    I note that there is a command  GoTo Rep0 in your code, so the code "Application.Workbooks(Window0).Close SaveChanges:=False" will be executed multiple times. However, since the file is closed in first time, it will get error to close workbook with a no-exist workbook name.

    I would suggest you move the command to below if statement so it will be only executed only once time.

    Such as 

    If cnt0 = 1 Then
                Application.Workbooks(Window0).Close SaveChanges:=False
                    Set HOBook = Workbooks.Add
                    With HOBook
                        .Title = Title0
                    ...
                    ...

    If it does not work for you, please share the excel file and detail the reproduce steps so we could reproduce your issue.

    By the way, it seems you have taken the solution in another thread Problem to ColumnWidth . If it does work for you, please mark helpful reply to close that thread.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jackson_1990 Tuesday, March 6, 2018 9:27 AM
    Tuesday, March 6, 2018 7:36 AM
  • Due to If statement, the With part below would be executed only for 1st time, as cnt0 is accumulating. Do you think I should change position of repeater?

                If cnt0 = 1 Then
                    Set HOBook = Workbooks.Add
                    With HOBook
                        .Title = Title0
                        .Subject = Title0
                        .SaveAs Filename:=File0, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled
                        '.SaveAs Filename:=File0, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                        .Close SaveChanges:=False
                    End With
                    ...



    Many Thanks & Best Regards, Hua Min

    Tuesday, March 6, 2018 8:11 AM
  • Hello HuaMin Chen,

    It depends on your requirement. I would suggest you post a new thread and detail your requirement so we have a idea whether or how to change the position or repeater.

    Does my last solution work for your?If it does, for current thread, please mark helpful reply to close it.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 6, 2018 8:18 AM
  • Hello HuaMin Chen,

    >>Sorry, I still get....

    You did not change the code! "Application.Workbooks(Window0).Close SaveChanges:=False" is still outside the if statement and will be executed multiple times.

    >>Do you think I should change position of repeater?

    Yes, according to your code and data, the Rep0: and GoTo Rep0 loop will do many times and you do not need create the xls multiple times. Move the code out side the Rep0: and GoTo Rep0 loop.

    By the way, it seems you want to generate a Departure list xls. However, in your code, it was closed immediately after creating and saving. So, it is always empty. I'm wondering what's the function of other code in the macro.

    Best Regards,

    Terry



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 6, 2018 8:56 AM
  • Hi,

    >> So, it is always empty. I'm wondering what's the function of other code in the macro.

    What is missing to re-open it for further update?



    Many Thanks & Best Regards, Hua Min

    Tuesday, March 6, 2018 9:10 AM
  • Hello HuaMin Chen,

    This is another issue. Please post a new thread to let us know how would you update the workbook.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 6, 2018 9:12 AM