lucpian
04-08-2008, 08:54 AM
Hi All,
I am trying to import data from one Excel sheet to another which is a template, but the problem is that though it dialogs to the folder containing the Excel files, it does not import the data. What might be wrong with my code. Here is the code:
Sub ImportExceldataFile()
Dim sFile 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 delimiter
lFNum = FreeFile
'sFile = "C:CaratDelim.txt"
vaStrip = Array(vbLf, vbTab) 'list the text to strip
'Open the file
Open sFile For Input As lFNum
vaFields = GetExcelData(lFNum, vaStrip, sDELIM)
i = LBound(vaFields)
'Loop through the file until the end
Do While Not EOF(lFNum)
vaFields = GetExcelData(lFNum, vaStrip, 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
Private Function GetExcelData(ByVal FileNum As Long, _
ByVal Redundant As Variant, _
ByVal Delimiter As String) As Variant
Dim sInput As String
Dim i As Long
Line Input #FileNum, sInput 'input the current line
'remove the unwanted text
For i = LBound(Redundant) To UBound(Redundant)
sInput = Replace(sInput, Redundant(i), " ")
Next i
'split the text based on the delimeter
GetExcelData = Split(sInput, Delimiter)
End Function
Thanks
Lucpian
I am trying to import data from one Excel sheet to another which is a template, but the problem is that though it dialogs to the folder containing the Excel files, it does not import the data. What might be wrong with my code. Here is the code:
Sub ImportExceldataFile()
Dim sFile 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 delimiter
lFNum = FreeFile
'sFile = "C:CaratDelim.txt"
vaStrip = Array(vbLf, vbTab) 'list the text to strip
'Open the file
Open sFile For Input As lFNum
vaFields = GetExcelData(lFNum, vaStrip, sDELIM)
i = LBound(vaFields)
'Loop through the file until the end
Do While Not EOF(lFNum)
vaFields = GetExcelData(lFNum, vaStrip, 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
Private Function GetExcelData(ByVal FileNum As Long, _
ByVal Redundant As Variant, _
ByVal Delimiter As String) As Variant
Dim sInput As String
Dim i As Long
Line Input #FileNum, sInput 'input the current line
'remove the unwanted text
For i = LBound(Redundant) To UBound(Redundant)
sInput = Replace(sInput, Redundant(i), " ")
Next i
'split the text based on the delimeter
GetExcelData = Split(sInput, Delimiter)
End Function
Thanks
Lucpian