PDA

View Full Version : Create Import Wizard for Text Files



austenr
09-15-2004, 04:10 PM
I have a VBA macro that I want to use input as to how the text file is to be delimited before importing to EXCEL. Can anyone tell me how to enter space, comma, tab ect. as an input value in a message box.

Thanks in advance for your help.

Jacob Hilderbrand
09-15-2004, 04:50 PM
How about making a UserForm with option buttons for the choices?

austenr
09-15-2004, 06:56 PM
that would be fine...could you tell me how to?

Jacob Hilderbrand
09-15-2004, 07:59 PM
Take a look at the attachment.

austenr
09-17-2004, 06:21 AM
Here is what I have. I have a macro that has an input box. The user enters a single character to tell the macro how to delimite the text file. I cannot get it to work. Could someone look at the code below and tell me why when entering the single character delimiter, (space, comma, tab) it will not work.
Also, what would the user enter in the input form for a single character?


Thanks in advance for your help. I pasted the macro below:

Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sub ImportTextLines()
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim WholeLine As String
Dim FName As String
Application.ScreenUpdating = False
ColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
FName = "D:\test.txt"
Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
ColNdx = ColNdx + 1
Wend
Close #1
End Sub

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep
End Sub

CBrine
09-17-2004, 06:37 AM
austenr,

Can you provide us with the code for the "ImportTextFile" function. If you don't have the code for this function, then that is what your problem is. ImportTextFile is not a native Excel Function, but a user defined function. Let me know.

Zack Barresse
09-17-2004, 06:44 AM
Hey Austenr,

Check out the [ VBA ] tags we have for your code. I changed your post to reflect that, it makes it easier for others to read. :)

mvidas
09-17-2004, 06:47 AM
Hi austenr,

I made an add-in for dealing with text files like this. The import portion should do what you need, and can span multiple pages if the text file is huge.
The only reason I haven't publicized it here yet is because when the text files are over 100,000 or so lines, it takes about 2 minutes to import (too long in my opinion). But it can use multiple delimiters if desired as well. Take a look, see if it fits your needs. Put it in your
C:\Documents and Settings\%USERNAME%\Application Data\Microsoft\AddIns
directory, or wherever your addin directory is located. Then go to Tools, AddIns, then check the Text Files checkbox. It adds a menu option for it automatically.
Matt

austenr
09-17-2004, 09:17 AM
I was asked by someone who helped me earlier today to repost this question because I did not have all the code necessary. Hope someone can help. Thanks for all that are willing to make a suggestion.

Here is what I have. I have a macro that has an input box. The user enters a single character to tell the macro how to delimite the text file. I cannot get it to work. Could someone look at the code below and tell me why when entering the single character delimiter, (space, comma, tab) it will not work.
Also, what would the user enter in the input form for a single character?


Thanks in advance for your help. I pasted the macro below:


Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If
Sub ImportTextLines()
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim WholeLine As String
Dim FName As String
Application.ScreenUpdating = False
ColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
FName = "D:\test.txt"
Open FName For Input Access Read #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
ColNdx = ColNdx + 1
Wend
Close #1
End Sub
Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile Str(FName), Sep End Sub

mvidas
09-17-2004, 10:00 AM
To make it easier for everyone else, I've edited your code above to be more readable.
*** I DID NOT MAKE ANY CHANGES TO YOUR SUBROUTINE IN THIS POST ***
It actually looks like you put the ImportTextLines subroutine in the middle of your DoTheImport subroutine, and the DoTheImport subroutine calls ImportTextFile sub, not ImportTextLines. But either way here is your code, formatted.

Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
Msgbox "You didn't select a file"
Exit Sub
End If
Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep
End Sub
Sub ImportTextLines()
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim WholeLine As String
Dim FName As String
Application.ScreenUpdating = False
ColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
FName = "D:\test.txt"
Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
ColNdx = ColNdx + 1
Wend
Close #1
End Sub

mvidas
09-17-2004, 10:11 AM
OK, I changed your ImportTextLines sub to ImportTextFile. Also, I added (and commented out) a line which will restrict the Sep variable to be only 1 character long. I also changed the file number to be a variable, in the rare instance that file number 1 is already being used.
Give these a try:

Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
Msgbox "You didn't select a file"
Exit Sub
End If
Redo:
Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
' If Len(Sep) <> 1 Then msgbox "Invalid delimiter length.": GoTo Redo
If Sep = "" Then Exit Sub

ImportTextfile CStr(FName), Sep
End Sub
Sub ImportTextfile(FName As String, Sep As String)
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim WholeLine As String
Dim FileNum As Integer
Application.ScreenUpdating = False
ColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
FileNum = FreeFile()
Open FName For Input Access Read As #FileNum
While Not EOF(FileNum)
Line Input #FileNum, WholeLine
While WholeLine Like "*" & Sep & "*"
Cells(RowNdx, ColNdx) = Left(WholeLine, InStr(1, WholeLine, Sep, 1) - 1)
WholeLine = Mid(WholeLine, InStr(1, WholeLine, Sep, 1) + Len(Sep))
ColNdx = ColNdx + 1
Wend
Cells(RowNdx, ColNdx) = WholeLine
RowNdx = RowNdx + 1
ColNdx = 1
Wend
Close #FileNum
End Sub

Matt

mvidas
09-17-2004, 10:19 AM
For the record, I received a PM from austenr as follows:



So the way you rearranged the code will work? Just need to know when the box comes up to ask what you want to delimit it with what you type in and the correct format.

ex. comma , or "comma" or ",". Currently it is putting every cell in one cell instead of breaking up the name in one, SS# in another etc.

Thanks in advance for your help.and my reply:

Hi austenr,

In the fix I posted (my original post was just to make your code readable), you would enter "," (no quotes) in the input box.

Also, in the message itself is a reply box at the bottom. Please put questions like this in there so people can read your responses/questions. Theres no need to add extra questions for things like this, when you can just simply reply to existing questions.

Should you have any questions about how to use this forum, please don't hesitate to ask!
Matt

austenr
09-17-2004, 10:42 AM
The result is skipping cells instead of putting fields in consecutive cells in one row. Any help would be appreciated.

mvidas
09-17-2004, 12:10 PM
It shouldn't be skipping anything, could you zip up a sample text file you're using?

What the macro does is it takes the string (ex: "qwerty,uiop,lkjhg,fd,sazx,cvb,nm"), looks for the delimiter (ex: ","), and parses it by reviewing the string
It looks for the first instance of the delimiter (position seven in the example above), and puts 1-6 ("qwerty") in the first column. It then chops the string to after the first delimiter ("uiop,lkjhg,fd,sazx,cvb,nm"), and repeats. The only way it wouldn't work the way you want it to is if you have two delimiters in a row, it should produce a blank cell. Also, if you have the delimiter within a cell you want (if you want "last, first" to be one cell) if will divide them up as it strictly looks at the text itself.
As I said, if you're not getting the desired result, can you please post a sample text file and tell what delimiter you want to use?

Matt

Anne Troy
09-17-2004, 12:14 PM
Since all these threads seem to do with the same task, I've combined them. :)

austenr
09-17-2004, 12:41 PM
i will post a zip file tonight. i really appreciate everyones help.

austenr
09-19-2004, 06:56 PM
Thanks to everyone who has tried to help in the past but perhaps I need to make myself more clear.

I need a VBA macro to read a .rpt file and load it into EXCEL:

1. Let the user select the file at time of input.

2. Format the input file correctly so that each field goes in a separate cell not everything in one cell.

3. Would also like to sort the file after it is imported by a column determined by the user.

4. Print out the worksheet after it has been loaded and sorted.

5. Close the worksheet automatically after printing.

I know that is a lot but if someone could point me in the right direction I would be appreciative. I am really under the gun on this one.

Here is the code I have so far:

Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep
End Sub

Sub ImportTextLines()
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim WholeLine As String
Dim FName As String
Application.ScreenUpdating = False
ColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
FName = "D:\test.txt"
Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
ColNdx = ColNdx + 1
Wend
Close #1
End Sub

WillR
09-20-2004, 08:35 AM
Looks a bit like an attempt to mess with Chip Pearson's code.... ;)

You're telling VBA to ImportTextFile but you do not appear to have a procedure called that...

here is an example..

Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetOpenFilename _
(filefilter:="RPT Files(*.rpt),*.rpt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep
End Sub

Public Sub ImportTextFile(FName As String, Sep As String)
' This code imports a "text" file
' It takes a full path filename and a text separator
' character as inputs... The macro is called from the
' "Do the Import" macro further down this code pane

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
On Error GoTo EndMacro:
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1
'open the text file
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub


I just tested this... I am assuming your .rpt files are from something like Query analyser ?? (Well, that's what I tested it on anyway...

Anne Troy
09-20-2004, 09:30 AM
Hi, austen. Once again--I merged the threads because they all have to do with the same issue. Creating a new thread isn't going to get us anywhere, since experts won't know what's been tried. :)

austenr
09-20-2004, 09:48 AM
Will, When I run the code above, it abends with an ambigious compile error on the line that defines the Input text file. I didn't see any non defined names or anything. Could you please tell me why it abends? Thanks

WillR
09-20-2004, 03:02 PM
Can you explain in detail which line it bugs on and the exact error message you get. Thaks

ocsicnar
12-28-2004, 11:52 AM
New user here. Can the code be changed to accept the TAB character? Nothing happens when I hit the tab key. My attempts to modify the code has been disasterous (merely because I don't know enough). Any help is appreciated.