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.