I am trying to use the below code on my Mac running Excel 2016 V15.33.
Code is to import data from a text file into a new worksheet.
The code always fails on line "RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt",,True)"
with error :
Run-time error '1004':
'' could not be found.Check the spelling of the file name, and verify that the file location is correct.
If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.
Sub Import() Dim DestBook As Workbook, SourceBook As Workbook Dim DestCell As Range Dim RetVal As Boolean ' Set object variables for the active book and active cell. Set DestBook = ActiveWorkbook Set DestCell = ActiveCell ' Show the Open dialog box. RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt", , True) ' If Retval is false (Open dialog canceled), exit the procedure. If RetVal = False Then Exit Sub ' Set an object variable for the workbook containing the text file. Set SourceBook = ActiveWorkbook ' Copy the contents of the entire sheet containing the text file. Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy ' Activate the destination workbook and paste special the values ' from the text file. DestBook.Activate DestCell.PasteSpecial Paste:=xlValues ' Close the book containing the text file. SourceBook.Close False End Sub
I appreciate any help solving the issue.