PDA

View Full Version : Vba code for importing external data



lucpian
03-24-2008, 01:25 PM
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.

Thanks

Lucpian

Ago
03-24-2008, 02:38 PM
how are we supposed to help you with that litle information?

lucpian
03-24-2008, 02:56 PM
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

I do not know if this will provide more information for you guys to help me out. Basically, I will want to vba code to import the data from one excel workbook to a template which has some automation menus.

Thanks

Lucpian

Trevor
03-24-2008, 09:04 PM
Try changing

Do while not EOF(IfNum)

To

Do Wile Not .EOF

I don't see what freefile refers to other then a declaired value for IfNum