Import xls file from a specific folder

Josue

To open a known (existing) file, you can use;

Set srcWorkbook = Workbooks.Open("E:\folder\subfolder\YourFile.xlsx")

but it will produce an error if you make a mistake in typing the full path and file name (including the extension), most likely Runtime Error 1004.

Revision 03 August 2023:

My previous answer is modified (in the light of your comments below and recently- attached two files), as follows:

In the attached revised file, I've made changes to your code (in bold below in the code) which:

1. moves the ChDir line befoe it atteampts to launch the OpenFile dialog
2. comments out  the On Error Resume Next and  ... GoTo 0 lines (which is meaningless without a line starting 0:)
3. opens the user-selected file (if any) and copies its first sheet to two new sheets in your macro workbook (given your discussion point mentioned "two sheets in a new workbook"). First is "Full SoundPro data" and second "SoundPro Timestamp data"
4. closes that file after the data copy then continues with your processing of the second new sheet (removing extraneous data etc.) but also...
5. uses the With (object) / End With approach to save the dozen or so repeats of "copyWorksheet"

Here's the code, with key new comments following ###

Sub ImportCopySheetAndAddFormulas()
    Dim selectedFile As Variant
    Dim srcWorkbook As Workbook
    Dim srcWorksheet As Worksheet
    Dim copyWorksheet As Worksheet
    Dim timestampCell As Range
    Dim timestampRow As Long
    
    ' ### first change to desired folder
    ChDir "C:\Users\jcriollo\Documents"
    
    ' Allow the user to select the XLS file from that folder
    selectedFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
     On Error GoTo ErrorMessage 'error message if not found path

'    ChDir "C:\Users\jcriollo\Documents"
    
    If selectedFile = "False" Then
        MsgBox "You did not select a file.", vbExclamation
        Exit Sub
ErrorMessage:
        MsgBox "Josue`s Folder not found - change File Path" 'change this as needed
        Exit Sub
    End If
    
    ' ### ========Import to THIS workbook
    ' ### Open the selected XLS file
    Set srcWorkbook = Workbooks.Open(selectedFile)
    Set srcWorksheet = srcWorkbook.Sheets(1)
    
    ' ### Create first new sheet in this workbook...
'    Set copyWorksheet = srcWorkbook.Sheets.Add(After:=srcWorkbook.Sheets(srcWorkbook.Sheets.Count))
    Set copyWorksheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    copyWorksheet.Name = "Full SoundPro data"
    ' ### ...and copy the data to it from the opened workbook
    srcWorksheet.UsedRange.Copy copyWorksheet.Range("A1")
    
    ' ### Create a second new sheet in this workbook...
    Set copyWorksheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    copyWorksheet.Name = "SoundPro Timestamp data"
    ' ### ...and copy the data to it from the opened workbook
    srcWorksheet.UsedRange.Copy copyWorksheet.Range("A1")
    
    ' ### Close that open workbook, without saving any changes
    srcWorkbook.Close SaveChanges:=False
    
    ' Find the row with the word "timestamp"
    On Error Resume Next
    Set timestampCell = copyWorksheet.Cells.Find(What:="timestamp", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    'On Error GoTo 0
    
    If timestampCell Is Nothing Then
        MsgBox "The word 'timestamp' was not found in the copied sheet.", vbExclamation
        Exit Sub
    Else
        timestampRow = timestampCell.Row
    End If
    
    '### Use "With/ End With" construction to avoid repetition
    With copyWorksheet
        ' Delete rows above the timestamp row
        If timestampRow > 1 Then
            .Rows("1:" & timestampRow - 1).Delete Shift:=xlUp
        End If
        
        ' Delete all columns after the second column
        .Columns("C:O").Delete Shift:=xlToRight
        
        ' Add the specified formulas
        .Range("E1").Value = "Time"
        .Range("F1").Value = "Average"
        .Range("I1").Value = "Time"
        .Range("J1").Value = "Average"
        .Range("L1").Value = "10 min avg"
        .Range("E2").Formula = "=AVERAGE(INDEX(A:A,1+60*(ROW()-ROW($E$2))):INDEX(A:A,60*(ROW()-ROW($E$2)+1)))"
        .Range("F2").Formula = "=AVERAGE(INDEX(B:B,1+60*(ROW()-ROW($F$2))):INDEX(B:B,60*(ROW()-ROW($F$2)+1)))"
        .Range("I2").Formula = "=OFFSET($E$2,(ROW(E1)-1)*10,0)"
        .Range("J2").Formula = "=OFFSET($F$2,(ROW(F1)-1)*10,0)"
        .Range("L3").Formula = "=AVERAGE(INDEX(F:F,1+10*(ROW()-ROW($L$3))):INDEX(F:F,10*(ROW()-ROW($L$3)+1)))"
        
         ' Format columns E and I to display time as HH:mm:ss AM/PM
        .Columns("E:E").NumberFormat = "HH:mm AM/PM"
        .Columns("I:I").NumberFormat = "HH:mm AM/PM"
        
        'Format columns F, J, L to 2 decimal places"
        .Columns("F:F").NumberFormat = "0.00"
        .Columns("J:J").NumberFormat = "0.00"
        .Columns("L:L").NumberFormat = "0.00"
        
        'Adjust column A to autofill
        .Columns("A").AutoFit
    
    End With
    
    MsgBox "Data imported from the selected XLS file. A copy is created in the same workbook with rows above 'timestamp' removed and the specified formulas added.", vbInformation
End Sub

It seemed to import the data succerssfully from your Sample.xls file. You know how to avoid seeing the momentary opening and closing of that file, right?

I understand you're using .xls files (Excel97 format) since SoundPro exports to that but please note that they were superseded by .xlsx and xlsm (for macro-enabled) formats a long time ago..

Hope this fixes your problem (once you add the correct, full path). If so, please remember to mark this Answer as Selected.