Import text file in VBA

Hello Everyone, I have written the code, after run the code it works well. But it always start from A1 I want to show start from D1. So, what should i add in VBA code? Please help...

Book3.xlsm29 KB

Macros and VBA

HansVogelaar

Jan 02, 2022

Excel

Public Sub ImportTextFile()
    Dim CurFile As Workbook
    Dim NewSheet As Worksheet
    Dim TextFile As Workbook
    Dim OpenFiles() As Variant
    Dim i As Integer
    Set CurFile = ActiveWorkbook
    OpenFiles = GetFiles()
    Application.ScreenUpdating = False
    For i = 1 To UBound(OpenFiles)
        Set NewSheet = CurFile.Worksheets.Add
        Set TextFile = Workbooks.Open(OpenFiles(i))
        TextFile.Sheets(1).Range("A1").CurrentRegion.Copy Destination:=NewSheet.Range("D1")
        NewSheet.Name = TextFile.Name
        Application.CutCopyMode = False
        TextFile.Close
    Next i
    Application.ScreenUpdating = True
End Sub

Public Function GetFiles() As Variant
    GetFiles = Application.GetOpenFilename(Title:="Select File(s) to Import", MultiSelect:=True)
End Function

Public Sub ImportTextFile() Dim CurFile As Workbook Dim NewSheet As Worksheet Dim TextFile As Workbook Dim OpenFiles() As Variant Dim i As Integer Set CurFile = ActiveWorkbook OpenFiles = GetFiles() Application.ScreenUpdating = False For i = 1 To UBound(OpenFiles) Set NewSheet = CurFile.Worksheets.Add Set TextFile = Workbooks.Open(OpenFiles(i)) TextFile.Sheets(1).Range("A1").CurrentRegion.Copy Destination:=NewSheet.Range("D1") NewSheet.Name = TextFile.Name Application.CutCopyMode = False TextFile.Close Next i Application.ScreenUpdating = True End Sub Public Function GetFiles() As Variant GetFiles = Application.GetOpenFilename(Title:="Select File(s) to Import", MultiSelect:=True) End Function

Forum Discussion

Import text file in VBA

Resources