Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which Excel event to call when opening a .txt file
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which Excel event to call when opening a .txt file
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. --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) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for Opening Reuters, Opening Excel File, Closing Excel File | Excel Programming | |||
Call Event as Sub | Excel Programming | |||
Opening Excel workbook via automation doesn't fire event procedures? | Excel Programming | |||
how can i call macro in powerpoint file from excel file | Excel Programming | |||
How to run a Sub while opening the xls file (what event to use)? | Excel Programming |