Modify code to work with xls and xlsx files

[CJ-22]

Hello,

I have a code (probably found it on this forum) that opens up an Open Dialog Box to open another Excel Workbook. When you select the Excel Workbook you want to open, it copies data from the first workbook to the new one and saves the file. It works great except it will only open .xls files. My work is starting to use .xlsx files now and I need to update the code. Can someone help modify this code to be able to open .xls and .xlsx files? Thanks for any help and here is the code.

Private Sub CommandButton2_Click()

MsgBox "Select File to Open."

Dim wb As Workbook

' speed up by turning screenupdating off
    Application.ScreenUpdating = False ' turn off the screen updating

' set workbooks
    Set MasterWB = ActiveWorkbook

' open file browser and select XLS files only, end macro if no file is selected
    newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Select COMMENTS report for import")
    If newFN = False Then
        MsgBox "No file selected."
        Exit Sub
    End If

' open selected workbook in read only and copy all cells of worksheet 1
    Set wb = Workbooks.Open(newFN, True, False)
    
    Sheets("Setup").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "='Record'!R[-5]C[1]"
    Range("D6").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E40").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    Range("E41").Select
    Sheets("Record").Select
    Range("A1").Select
' close workbook without saving changes and free memory
    'wb.Close False
    'Set wb = Nothing
    
' turn screenupdating back on
    Application.ScreenUpdating = True ' turn on the screen updating
    
    Sheets("Setup").Select
    Range("A1:G67").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Sheets("Record").Select
    
End Sub

[Solus Rankin]

Try changing

(*.xls), *.xls", Title:="Select COMMENTS report for import")

to

(*.xlsx), *.xlsx", Title:="Select COMMENTS report for import")

first workbook to the new one and saves the file

I don't see where this code saves your workbook FYI

[CJ-22]

Thanks for the reply XeRo Solus. I tried your suggestion, but it did not change anything. Either way I can only see .xls files in the dialog box. Even if there are both .xls files and .xlsx files in the folder I am viewing, I can only see the .xls files. I don't know why. Also, you are right. It does not save the file automatically. The file is left open and I manually save it. Sorry for the confusion. Any other suggestions?

[CJ-22]

Hi Jindon,

Thanks for the suggestion. I tried it but I can still only view .xls files in the Open Dialog Box. The drop down arrow to change file types only has the .xls option, but I do not know why.

[jindon]

Change to

newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls", Title:="Select COMMENTS report for import")

[jindon]

Then your Excel is something wrong....

I can see .xls, .xlsx, xlsm etc, any files starts from .xls

[CJ-22]

Sorry Jindon, you are right. Somehow I copied and pasted incorrectly. It is now working great. Thanks for all the help.