Macro to open a delimited excel sheet

[jupops]

Good Morning All

Could anybody help. I am trying to write a macro that will open a text file (spool998), using the delimited option with tab and comma selected. the code looks like:

ChDir "R:\Annexe"
Workbooks.OpenText FileName:="R:\Annexe\spool.998", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), _
Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array( _
36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), _
Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array( _
49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), _
Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array( _
62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array(67, 1), Array(68, 1), _
Array(69, 1), Array(70, 1), Array(71, 1))

Is there coding that I can use to tidy this up.

Regards

Jupops

 

[ETID]

You would not need to use code, (too many steps)...

Create a live link to the text file from within excel,

From excel click Data>Get External Data>New Database Query>....text files* from the list...follow the wizard from there (to select your text file)
then return your data to excel.

You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
after your comfy with this...I would recommend the MsQuery option, there you can set criteria about the data that is returned from your text file.


when data is returned,...you can right click in the data and select properties to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current / up to date text file data comes in (if you set the refresh on open option)

 

[jupops]

Good Evening Etid

Thank you for your help, but I am slightly stuck. The text file I am using has no headings, so when i chose the columns it looked like I was choosing values that may change when I update the 998 spool file. Is there a way to specify by the column, eg C or AC and so on, instead of column heading?

Regards

Jupops

 

[SkipVought]

jupops,

What is the problem that you are having? The code that you recorded is about a good as it gets.

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ETID]

Can you make the headings static in the sheet that owns the query....and return the odbc linked data starting @ cell A2?