EXCEL: Macro to convert data format while opening a text file with delimiter

EXCEL: Macro to convert data format while opening a text file with delimiter

Post by Bird » Sat, 09 Sep 2000 23:00:45



Hi all,

     I am writing a macro to automatically convert a text file containing
many columns separated by predefined delimiter. I want to tell EXCEL to open
all columns in text format, which I know can be done by the following codes:

Workbooks.OpenTextFileName:=f_name, ......
      fieldinfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2),
Array(5, 2)..... Array(n, 2))

     The problem is: the number of columns contained in the text file varies
from one to another, I don't exactly know the optimal value of N. It also
seems to be quite silly to define the exact number of columns in the array,
but not using a more generic command to tell EXCEL to convert *ALL* columns
into text. Anyone can tell me such a command (of course in a macro context,
preferably suitable for EXCEL 5.0 and any higher version)? Many thanks!

Best regards,
Birdy

 
 
 

EXCEL: Macro to convert data format while opening a text file with delimiter

Post by Dave Peterso » Sun, 10 Sep 2000 04:12:03


I'm using xl97sr2, but it might work with 95.

Excel seems to be very forgiving if you specify too many fields (more than you
have in the input file), so I don't think that I'd worry about specifying too
many columns.  But excel has a limit on how many "array(array("'s you can have
(varies between versions).

So maybe you could just do something like:

Option Explicit

Sub Macro1()

    Dim i As Integer
    Dim Columnarray(1 To 99, 1 To 2)

    For i = 1 To 99
       Columnarray(i, 1) = i
       Columnarray(i, 2) = 2
    Next i

    Workbooks.OpenText FileName:=f_name, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
        OtherChar:=";", FieldInfo:=Columnarray

End Sub

Watchout for the otherchar:=, I used ";" in my testing.

Adjust the 99 to as many fields as you think that you'll need (both spots).


> Hi all,

>      I am writing a macro to automatically convert a text file containing
> many columns separated by predefined delimiter. I want to tell EXCEL to open
> all columns in text format, which I know can be done by the following codes:

> Workbooks.OpenTextFileName:=f_name, ......
>       fieldinfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2),
> Array(5, 2)..... Array(n, 2))

>      The problem is: the number of columns contained in the text file varies
> from one to another, I don't exactly know the optimal value of N. It also
> seems to be quite silly to define the exact number of columns in the array,
> but not using a more generic command to tell EXCEL to convert *ALL* columns
> into text. Anyone can tell me such a command (of course in a macro context,
> preferably suitable for EXCEL 5.0 and any higher version)? Many thanks!

> Best regards,
> Birdy

--

Dave Peterson


 
 
 

EXCEL: Macro to convert data format while opening a text file with delimiter

Post by Bird » Sun, 10 Sep 2000 15:40:45


Thank you for your solution. I tested in xl97sr2, but there was error when
the "array(array(" is as large as 50, so I set it to 45. Do you encounter
the same problem? Also, do you know whether the upper limit is due to the
size of memory, version of Excel or anything else? Thanks!


> I'm using xl97sr2, but it might work with 95.

> Excel seems to be very forgiving if you specify too many fields (more than
you
> have in the input file), so I don't think that I'd worry about specifying
too
> many columns.  But excel has a limit on how many "array(array("'s you can
have
> (varies between versions).

> So maybe you could just do something like:

> Option Explicit

> Sub Macro1()

>     Dim i As Integer
>     Dim Columnarray(1 To 99, 1 To 2)

>     For i = 1 To 99
>        Columnarray(i, 1) = i
>        Columnarray(i, 2) = 2
>     Next i

>     Workbooks.OpenText FileName:=f_name, Origin:=xlWindows, _
>         StartRow:=1, DataType:=xlDelimited, _
>         TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
>         Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
>         OtherChar:=";", FieldInfo:=Columnarray

> End Sub

> Watchout for the otherchar:=, I used ";" in my testing.

> Adjust the 99 to as many fields as you think that you'll need (both
spots).


> > Hi all,

> >      I am writing a macro to automatically convert a text file
containing
> > many columns separated by predefined delimiter. I want to tell EXCEL to
open
> > all columns in text format, which I know can be done by the following
codes:

> > Workbooks.OpenTextFileName:=f_name, ......
> >       fieldinfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4,
2),
> > Array(5, 2)..... Array(n, 2))

> >      The problem is: the number of columns contained in the text file
varies
> > from one to another, I don't exactly know the optimal value of N. It
also
> > seems to be quite silly to define the exact number of columns in the
array,
> > but not using a more generic command to tell EXCEL to convert *ALL*
columns
> > into text. Anyone can tell me such a command (of course in a macro
context,
> > preferably suitable for EXCEL 5.0 and any higher version)? Many thanks!

> > Best regards,
> > Birdy

> --

> Dave Peterson


 
 
 

EXCEL: Macro to convert data format while opening a text file with delimiter

Post by Dave Peterso » Sun, 10 Sep 2000 21:39:52


From a ms knowledge base article:

SYMPTOMS

If you open a text file while recording a Visual Basic macro in Microsoft Excel
5.0, when you run the resulting subroutine, you may receive an "Out of
memory" message if the text file you opened contains more than 69 columns.

In Microsoft Excel 7.x, you will receive the error message if the text file
contains more than 55 columns. In Microsoft Excel 97, you will receive the
error message if the text file contains more than 52 columns.

========

One of their workarounds was to set up your own array and use that instead of
the explicit "array(array(" stuff.

Did you try numbers bigger than 50 in the example I sent?  


> Thank you for your solution. I tested in xl97sr2, but there was error when
> the "array(array(" is as large as 50, so I set it to 45. Do you encounter
> the same problem? Also, do you know whether the upper limit is due to the
> size of memory, version of Excel or anything else? Thanks!



> > I'm using xl97sr2, but it might work with 95.

> > Excel seems to be very forgiving if you specify too many fields (more than
> you
> > have in the input file), so I don't think that I'd worry about specifying
> too
> > many columns.  But excel has a limit on how many "array(array("'s you can
> have
> > (varies between versions).

> > So maybe you could just do something like:

> > Option Explicit

> > Sub Macro1()

> >     Dim i As Integer
> >     Dim Columnarray(1 To 99, 1 To 2)

> >     For i = 1 To 99
> >        Columnarray(i, 1) = i
> >        Columnarray(i, 2) = 2
> >     Next i

> >     Workbooks.OpenText FileName:=f_name, Origin:=xlWindows, _
> >         StartRow:=1, DataType:=xlDelimited, _
> >         TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
> Tab:=True, _
> >         Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
> >         OtherChar:=";", FieldInfo:=Columnarray

> > End Sub

> > Watchout for the otherchar:=, I used ";" in my testing.

> > Adjust the 99 to as many fields as you think that you'll need (both
> spots).


> > > Hi all,

> > >      I am writing a macro to automatically convert a text file
> containing
> > > many columns separated by predefined delimiter. I want to tell EXCEL to
> open
> > > all columns in text format, which I know can be done by the following
> codes:

> > > Workbooks.OpenTextFileName:=f_name, ......
> > >       fieldinfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4,
> 2),
> > > Array(5, 2)..... Array(n, 2))

> > >      The problem is: the number of columns contained in the text file
> varies
> > > from one to another, I don't exactly know the optimal value of N. It
> also
> > > seems to be quite silly to define the exact number of columns in the
> array,
> > > but not using a more generic command to tell EXCEL to convert *ALL*
> columns
> > > into text. Anyone can tell me such a command (of course in a macro
> context,
> > > preferably suitable for EXCEL 5.0 and any higher version)? Many thanks!

> > > Best regards,
> > > Birdy

> > --

> > Dave Peterson


--

Dave Peterson

 
 
 

EXCEL: Macro to convert data format while opening a text file with delimiter

Post by Bird » Thu, 14 Sep 2000 15:03:41


Your suggestion is great! I've tried 99 using your codes and it does work in
EXCEL 97 as well as  EXCEL 5.0. I think 99 is OK for the files I am going to
convert. Thanks so much!


Quote:

> One of their workarounds was to set up your own array and use that instead
of
> the explicit "array(array(" stuff.

> Did you try numbers bigger than 50 in the example I sent?

 
 
 

1. connecting to an excel data file without opening the particular excel file

Dear Experts

I am attaching two excel files. One is having Student data.

I am having an userform in another excel file to prepare student marks
information.

Can I connect to the similar fields in the student data.xls without opening
it from the student marks.xls  by clicking UPDATE command button on the
userform?

waiting for your valuable replies.

regards
shankar sb

  student data.xls
20K Download

  student Marks.xls
37K Download

2. HELP: Imsai, Shugart 800, Tarbell, CP/M 2.2

3. How to transfer a DOS text format data file into Excel

4. Help - HyperAccess Lite won't dial automatically

5. Excel - Need to convert MAC format file to PC/Windows format

6. 5394 Controller

7. Help needed- Converting JPG format file to Excel file via VBA

8. VOIP and E&M

9. Converting Text Files To Delimited Text Files

10. looking for some software to convert idl format data to text

11. converting DICOM-format files into PGM-format files

12. EXCEL; How dump as text w/ cell delimiters