VBA to hardcode directory and import text file

[rn119]

I need a way to have VBA handle two things.

- Hardcode a file directory (e.g.,C:\Users\Public\abc123) for the user to select only text files.
- One the file is selected, import it into Excel. The file would be a comma delimited text file. The code will only allow imports for the text file and no other filetype. If another file type is accidentally chosen, to throw a validation error to select the correct filetype.

Thanks.

 

[vicx2ww]

this should help

Sub LoopAllExcelFilesInFolder()'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them

Dim wb As Workbook
Dim thiswb As Workbook: Set thiswb = ActiveWorkbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog




'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  
  Set thiswb = ActiveWorkbook


'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)


    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With


'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings


[SIZE=4][B]'Target File Extension (must include wildcard "*")[/B][/SIZE]
[SIZE=4][B]  myExtension = "*.txt*"[/B][/SIZE]


'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)


'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
    
    'Ensure Workbook has opened before moving on to next line of code
    
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.Copy
      thiswb.Activate
      
      Range("a100000").Select
      Selection.End(xlUp).Select
      lstrow = ActiveCell.Row
      Range("a" & lstrow).Select
          Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
         Application.CutCopyMode = False
    
    'Change First Worksheet's Background Fill Blue
      wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)
    
    'Save and Close Workbook
      wb.Close SaveChanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents


    'Get next file name
      myFile = Dir
  Loop


'Message Box when tasks are completed
  MsgBox "Task Complete!"


ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True


End Sub

 

[rn119]

Thanks for this vicx2ww but instead of a user defined path prompt, I actually want to hardcode the filepath into the code. Can you help with that?

 

[vicx2ww]

just comment below block and write myPath = " your adress " With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & ""
End With