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.
Printable View
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.
How about making a UserForm with option buttons for the choices?
that would be fine...could you tell me how to?
Take a look at the attachment.
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:
[VBA]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[/VBA]
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.
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. :)
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
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:
[VBA]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[/VBA]
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.
[vba]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[/vba]
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:
[vba]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[/vba]
Matt
For the record, I received a PM from austenr as follows:
and my reply:Quote:
Originally Posted by austenr
Quote:
Originally Posted by mvidas
The result is skipping cells instead of putting fields in consecutive cells in one row. Any help would be appreciated.
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
Since all these threads seem to do with the same task, I've combined them. :)
i will post a zip file tonight. i really appreciate everyones help.
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:
[VBA] 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
[/VBA]
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..
[vba]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
[/vba]
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...
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. :)
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
Can you explain in detail which line it bugs on and the exact error message you get. Thaks
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.