Which Excel event to call when opening a .txt file

  • Thread starter Thread starter zigzagdna
  • Start date Start date
Z

zigzagdna

Subject: Which Excel event to call when opening a .txt file
I am opening a materialcodeconflictid.txt file in Excel. When I open
this .txt file, Excel opens a “Text Import Wizard” where I can specify
format of various columns etc. I want to automate opening of Text and
setting various formats. So I have developed a macro by recording all
of these actions, matcodeconflict_leadzero , shown below. This macro
however has hardcoded file name so I want to make it more flexible. I
can modify this macro to take file name as a parameter.
Sub matcodeconflict_leadzero()
Workbooks.OpenText Filename:="C:\TEMP\matcodeconflictid.txt",
Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1), Array(7, 1), Array(8, 1), _
Array(9, 2), Array(10, 1), Array(11, 1), Array(12, 1),
Array(13, 1), Array(14, 1)), _
TrailingMinusNumbers:=True
Application.Left = 68.5
Application.Top = 43
End Sub
To this end, I created application events. I have setup an application
procedure x1APP_WorkbookOpen and inside this procedure I print name of
the workbook just for debugging.
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)

MsgBox "Welcome..." & Wb.Name
‘ call matcodeconflict_leadzero (Wb.Name)
End Sub

I was hoping that inside this application procedure after printing
name of workbook, I can call my macro
matcodeconflict_leadzero
Problem I am finding is that “Text Import Wizard” is shown before
x1APP_WorkbookOpen is called.
What I want to do is not to open “Text Import Wizard” at all instead
call a macro which will do this formatting for me. How can I do this?
What application event I have to set so all of above can be done
automatically.
 
zigzagdna pretended :
Subject: Which Excel event to call when opening a .txt file
I am opening a materialcodeconflictid.txt file in Excel. When I open
this .txt file, Excel opens a “Text Import Wizard†where I can specify
format of various columns etc. I want to automate opening of Text and
setting various formats. So I have developed a macro by recording all
of these actions, matcodeconflict_leadzero , shown below. This macro
however has hardcoded file name so I want to make it more flexible. I
can modify this macro to take file name as a parameter.
Sub matcodeconflict_leadzero()
Workbooks.OpenText Filename:="C:\TEMP\matcodeconflictid.txt",
Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1), Array(7, 1), Array(8, 1), _
Array(9, 2), Array(10, 1), Array(11, 1), Array(12, 1),
Array(13, 1), Array(14, 1)), _
TrailingMinusNumbers:=True
Application.Left = 68.5
Application.Top = 43
End Sub
To this end, I created application events. I have setup an application
procedure x1APP_WorkbookOpen and inside this procedure I print name of
the workbook just for debugging.
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)

MsgBox "Welcome..." & Wb.Name
‘ call matcodeconflict_leadzero (Wb.Name)
End Sub

I was hoping that inside this application procedure after printing
name of workbook, I can call my macro
matcodeconflict_leadzero
Problem I am finding is that “Text Import Wizard†is shown before
x1APP_WorkbookOpen is called.
What I want to do is not to open “Text Import Wizard†at all instead
call a macro which will do this formatting for me. How can I do this?
What application event I have to set so all of above can be done
automatically.
Click to expand...

--Easiest way--
You can do this using VBA's standard file I/O procedures to dump the
file contents into any worksheet of any workbook. You should format the
columns before dumping the data into their cells. This might be done as
a separate procedure that your main procedure calls just before it
dumps the data.

--OR--
You can use ADODB to work the file as a recordset. This is somewhat
more complicated and also a bit slower performance-wise. Column
formatting is still required. One big advantage is you can filter the
data before writing it to the worksheet using standard SQL.


--??--
Setting up an events handler is usually done via a class module for a
different purpose that what you describe here. I don't understand why
you need to duplicate event handling to accomplish your task. (It just
makes for extraneous resources overhead in your project)
 
Back
Top