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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.