Trbodzl
11-01-2018, 01:24 PM
Hi all,
I am new to VBA and the forum but I have a problem in our business that I am trying to solve.
We have a Tab delimited text file that contains an order number that will be sent to a vendor. Before we send it, we need to append each order number with a string of text. i.e "abc18".
The current process is to import the text file into Excel, Concatenate the text into the order column and then export that back out into a text file. I was hoping to automate this with VBA.
I've been able to automate importing the text file into Excel but I know there has to be an easier way that will not require the user to activate three separate Macros to complete the task:
Option Explicit
Sub ImportTextFile()
Dim vFileName
On Error GoTo ErrorHandle
vFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If vFileName = False Or Right(vFileName, 3) <> "txt" Then
GoTo BeforeExit
End If
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=vFileName, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, Tab:=True
BeforeExit:
Application.ScreenUpdating = True
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub
I am new to VBA and the forum but I have a problem in our business that I am trying to solve.
We have a Tab delimited text file that contains an order number that will be sent to a vendor. Before we send it, we need to append each order number with a string of text. i.e "abc18".
The current process is to import the text file into Excel, Concatenate the text into the order column and then export that back out into a text file. I was hoping to automate this with VBA.
I've been able to automate importing the text file into Excel but I know there has to be an easier way that will not require the user to activate three separate Macros to complete the task:
Option Explicit
Sub ImportTextFile()
Dim vFileName
On Error GoTo ErrorHandle
vFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If vFileName = False Or Right(vFileName, 3) <> "txt" Then
GoTo BeforeExit
End If
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=vFileName, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, Tab:=True
BeforeExit:
Application.ScreenUpdating = True
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub