copy text file data to a specific excel sheet via vba

[dgehlod]

hi,
i m making a utility and want to give import and export data facility. i have made code for export but for coding import data i am in trouble.
while exporting data from excel to txt user can use any name or destination of file so it will not defined. can you help me to give a vba code that open file dialog and select text file and copy the data in txt file and paste in a specific sheet.

i use this code to open dialog but how to copy data from selected text file and paste in specific sheet.

Call Application.FileDialog(msoFileDialogOpen _
).Filters.Add("Text Files Only", "*.txt")

Waiting for your answers.

 

[ParamRay]

.
.

Try something like this:

 Sub ImportTextFile()
  Dim strDelimiter$
  Dim strLineOfData$
  Dim varFileName
  Dim varLineSplit
  Dim intFileNum%
  Dim lngLineCount&
  Dim wksDestination As Worksheet
  
  strDelimiter = Chr$(44) 'comma
  varFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
  If varFileName = False Then Exit Sub
  Set wksDestination = Worksheets.Add
  
  intFileNum = FreeFile%
  Open varFileName For Input As #intFileNum
  Do Until EOF(intFileNum)
    lngLineCount = 1 + lngLineCount
    Line Input #intFileNum, strLineOfData
    varLineSplit = Split(strLineOfData, strDelimiter)
    wksDestination.Cells(lngLineCount, 1).Resize(, 1 + UBound(varLineSplit)) = varLineSplit
  Loop
  Close #intFileNum
End Sub

 

[dgehlod]

thanks for reply but this code given data in one cell and i want it to be in tab delimited format

so i search and found this code and it works

Option ExplicitConst FILEPATH = "c:\"
 
Sub ImportData()
    Dim UserFilename As String
    Dim NewImport As Variant
     
     'ChDir FILEPATH
    ChDir "C:\"
    UserFilename = Application.GetOpenFilename '(filefilter:="Text Files(*.txt),*.txt", Title:="Text Files to Import")
    NewImport = "Text;" & UserFilename
     
    Sheets("dev").Select
    Range("A2").Select
    With ActiveSheet.QueryTables.Add(Connection:=NewImport, _
        Destination:=Range("A2"))
        .Name = "TabDelimitedFile"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileTabDelimiter = True
         ' This is for importing four columns
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
         ' it would read ...=Array(1, 1, 1, 1, 1, 1, 1, etc.....) if you imported 7 or more columns
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[dgehlod]

hi, the above code given is working but not working on hidden sheet. the code you had given can it work on hidden sheets with tab delimited format.
please give me reply