locked
Skipping dialog box questions in Worksheet merge code RRS feed

  • Question

  • Hi,

    I am using a simple merge code as below. The code works well, but every time it is opening and closing the source data, i prompts 2 dialog boxes, both of which I skip. When I am merging multiple sheetings, this is highly time consuming and annoying.

    The two dialog box questions which I skip, are:

    "This workbook contins links to other data sources" Update / Don't Update.

    "Do you want tosave the changes you made to "File.xlsb"

     

    Can I prevent these from popping up so that the code can run without my intervention ?  

    The current code...

    Sub simpleXlsMerger()
    Dim bookList As Workbook
    Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
    Application.ScreenUpdating = False
    Set mergeObj = CreateObject("Scripting.FileSystemObject")

    'change folder path of excel files here
    Set dirObj = mergeObj.Getfolder("C:\Users\kent.liu\Desktop\Merge\Data")
    Set filesObj = dirObj.Files
    For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)

    '*****Select sheet*****
    Sheets("Summary").Select




    '*****To unhide all colums and rows*****
        Columns.EntireColumn.Hidden = False
        Rows.EntireRow.Hidden = False

    'change "A2" with cell reference of start point for every files here
    'for example "B3:IV" to merge all files start from columns B and rows 3
    'If you're files using more than IV column, change it to the latest column
    'Also change "A" column on "A65536" to the same column as start point


    '*****Select Start of data range to copy*****
    Range("A3:IV" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate

    'Do not change the following column. It's not the same column as above
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    bookList.Close
    Next
    End Sub

    Tuesday, April 24, 2018 2:42 AM

All replies

  • Change

    Set bookList = Workbooks.Open(everyObj)

    to

    Set bookList = Workbooks.Open(Filename:=everyObj, UpdateLinks:=False)

    and

    bookList.Close

    to

    bookList.Close SaveChanges:=False


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 24, 2018 6:52 AM
  • Thank you for the reply.

    It is great that the dialog box for Update / Dont Update is no longer appearing. 

    The dialog box to save the file is still appearing though

    Anyway this can be avoided ?

    Wednesday, May 2, 2018 7:46 AM
  • If you use

    bookList.Close SaveChanges:=False

    you shouldn't be prompted to save the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, May 2, 2018 8:14 AM
  • I fully agree,

    But for some reason it is still coming up with the message that reads : Do you want to save the changes you made to "XYZ.xlsb'?

    Wednesday, May 2, 2018 9:27 AM
  • I suspect this is caused by other code than the simpleXlsMerger macro. Do you have code in the ThisWorkbook module of XYZ.xlsb?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, May 2, 2018 10:47 AM
  • Ah yes, the XYZ.xlsb is filled with code. 

    So I guess that might be the source of the issue.

    Thanks

    Thursday, May 3, 2018 12:55 AM
  • Hello TBSKent,

    Where did you store the simpleXlsMerger macro? Is it stored in XYZ.xlsb? Or did you use the macro to open XYZ.xlsb 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.

    Thursday, May 3, 2018 7:08 AM
  • The simpleXLsMerger macro is in a workbook which then opens up the XYZ.xlsb workbook. 

    XYZ.xlsb itself is a Macro generated file. 

    Thursday, May 3, 2018 8:42 AM
  • Hello TBSKent,

    Please check if it is related to the code in XYZ.xlsb. You could us a xlsb workbook without for testing or you could try to comment out the code in XYZ.xlsx part by part to find which part may cause the issue, especially the code in event, such as BeforeClose event.

    Best Regard,

    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, May 4, 2018 8:34 AM