At work, I get a lot of textfiles that I need to open in a particular format in excel. I have to open them with fixed width, and the column widths, including the column headings are usually given to me in a seperate excel spreadsheet. I have recorded a macro to do this, but, it only works for a particular format. I get this type of code coming up which I wish to generalise:
Array(Array(0,1),Array(3,1),Array(4,1),.....)
I am given an excel spreadsheet with the values 0,3,4 etc. in column A of the spreadsheet. My wish is for the code above to read this in automatically, at present I have to go into the code and change the 0,3,4 to be the new format all the time which is a pain.
Also, I am given in column B, the column headings. It would be nice if those column headings could be placed in the top row of the resulting text file, I am sure this could be done with transposing the range in column B and inserting it in the top row of the text file.
But, as a general point, assume I have the following values in Range("a1:a5")
2,5,6,7,8
ok. now I sometimes want to loop using these values which is not a problem. But, sometimes, I need the index number to be used. i.e. I would like B(2)=5 and so on .
This ties into what I thought would work for the above. Ideally I would like to get something of this sort:
array(array(A(0),1), array(A(1),0), array(A(2),0),.....)
where A = Range("A1:A5") or something like that? Somethign along these lines woul dbe awesome. Especially so if we could get A to be a dynamic range.
Cool, now please try and help me.
Alternatively, at the moment what I have done is use the spreadsheet I have been given with the widths and column headings format and used them to generate the code on the spreadsheet by manipulating text strings etc. So I have th actual macro as text in Range("D4:D7") say. Is there a way that a macro in a module could actually 'read' the text in D4:D7 of this worksheet and exectute it? Wouldn't that be cool.
Thanks.
Array(Array(0,1),Array(3,1),Array(4,1),.....)
I am given an excel spreadsheet with the values 0,3,4 etc. in column A of the spreadsheet. My wish is for the code above to read this in automatically, at present I have to go into the code and change the 0,3,4 to be the new format all the time which is a pain.
Also, I am given in column B, the column headings. It would be nice if those column headings could be placed in the top row of the resulting text file, I am sure this could be done with transposing the range in column B and inserting it in the top row of the text file.
But, as a general point, assume I have the following values in Range("a1:a5")
2,5,6,7,8
ok. now I sometimes want to loop using these values which is not a problem. But, sometimes, I need the index number to be used. i.e. I would like B(2)=5 and so on .
This ties into what I thought would work for the above. Ideally I would like to get something of this sort:
array(array(A(0),1), array(A(1),0), array(A(2),0),.....)
where A = Range("A1:A5") or something like that? Somethign along these lines woul dbe awesome. Especially so if we could get A to be a dynamic range.
Cool, now please try and help me.
Alternatively, at the moment what I have done is use the spreadsheet I have been given with the widths and column headings format and used them to generate the code on the spreadsheet by manipulating text strings etc. So I have th actual macro as text in Range("D4:D7") say. Is there a way that a macro in a module could actually 'read' the text in D4:D7 of this worksheet and exectute it? Wouldn't that be cool.
Thanks.
