lucpian
03-25-2008, 07:43 AM
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
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