Sub OpenFile4Processing()
' open file to load their data sheet
Dim vFile
Const kStartDIR = Environ$("USERPROFILE") & "\Documents\"
Dim wbMain As Workbook, wbSrc As Workbook
On Error GoTo EndItAll
Set wbMain = ActiveWorkbook
vFile = UserPick1File(kStartDIR)
If vFile = "" Then Exit Sub
Range("A1").Select
Workbooks.Open Filename:=vFile
Set wbSrc = ActiveWorkbook
wbSrc.Sheets(1).Select
wbSrc.Sheets(1).Copy Before:=wbMain.Sheets(1)
wbSrc.Close False
wbMain.Save
EndItAll:
Set wbSrc = Nothing
Set wbMain = Nothing
End Sub
private Function UserPick1File(Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialogMsg As String, sDecr As String, sExt As String
Const msoFileDialogViewList = 1
Const msoFileDialogSaveAs = 2
Const msoFileDialogFilePicker = 3
Dim lFilterIndex As Long
'getFilterTxt pvFilter, sDecr, sExt, sDialog
If IsMissing(pvPath) Then pvPath = "c:\"
''SetFileFilter pvFilter, sDecr, sExt, sDialogMsg
'Application.FileDialog(msoFileDialogSaveAs) =2 'SAVE AS
'Application.FileDialog(msoFileDialogFilePicker) =3 'file OPEN
With Application.FileDialog(3) 'REFERENCE not needed now : Microsoft Office XX.0 Object Library
.AllowMultiSelect = True
.Title = sDialogMsg ' "Locate a file to Import"
.ButtonName = "Import"
.Filters.Clear
'.Filters.Add sDecr, sExt
'.Filters.Add "Access Files", "*.accdb;*.mdb"
.Filters.Add "Excel Files", "*.xlsx"
'.Filters.Add "_All Files", "*.*"
'.Filters.Add "Text Files", "*.txt"
For lFilterIndex = 1 To .Filters.Count
'Debug.Print lFilterIndex, .Filters(lFilterIndex).Description
'get pdf format from type filter
If InStr(.Filters(lFilterIndex).Description, "PDF") > 0 Then
.FilterIndex = lFilterIndex
Exit For
End If
Next
.InitialFileName = pvPath
.InitialView = msoFileDialogViewList 'msoFileDialogViewThumbnail
If .Show = 0 Then
'There is a problem
Exit Function
End If
'Save the first file selected
UserPick1File = Trim(.SelectedItems(1))
End With
End Function