Still need help with this vba code

[lucpian]

Hi All,

I am trying to use vba code to import external data from one Excel worksheet to an excel template, but I do know how to write the code successfully. Please, can someone in this forum help me out. Iwish to state that I do know how to manually import it going through Data, but what I am suppose to do is to automate this process. The vba code below is what I wrote, but it does not work.


Sub Importdata()

Dim sFile As String
Dim sInput As String
Dim lFNum As Long
Dim vaFields As Variant
Dim i As Long
Dim lRow As Long
Dim vaStrip As Variant
Dim FileName As Variant
Dim Sep As String
Application.ScreenUpdating = False
On Error Goto EndMacro:
lFNum = FreeFile

sFile = Application.GetOpenFilename(FileFilter:="Excel File (*.xls),*.xls")
'If sFile = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
' Exit Sub
'End If
Const sDELIM = "|" 'Set the delimeter

lFNum = FreeFile
'sFile = "C:CaratDelim.txt"
vaStrip = Array(vbLf, vbTab) 'list the text to strip

'Open the file
Open sFile For Input As lFNum

'Loop through the file until the end
Do While Not EOF(lFNum)
Line Input #lFNum, sInput 'input the current line

'remove the unwanted text
For i = LBound(vaStrip) To UBound(vaStrip)
sInput = Replace(sInput, vaStrip(i), " ")
Next i

'split the text based on the delimeter
vaFields = Split(sInput, sDELIM)
lRow = lRow + 1

'Write to the worksheet
For i = 0 To UBound(vaFields)
Sheet1.Cells(lRow, i + 1).Value = vaFields(i)
Next i
Loop



EndMacro:
On Error Goto 0
Application.ScreenUpdating = True
Close lFNum

End Sub




Thanks

Lucpian

[Bob Phillips]

The vba code below is what I wrote, but it does not work.

A bit more detail. What dioesn't work, in what way, what does it do it shouldn't and vice versa.

[lucpian]

It does dialog to the excel files, and when you select any of it, it does not import the data from the selected file into the template, hence no data is display.

[mdmackillop]

Your code is designed to read from a txt file, but your changes are attemting to read an Excel File as if it were a text file. It does function, but gets meaningless results eg ???.
Why not record code to open the workbook, copy and paste the data. Your code can be further developed from there.