Results 1 to 7 of 7
  1. #1
    tdhw
    Guest

    Open AutoCAD txt file in Excel 97 (Excel 97)

    I have tried to open AutoCAD's exported text file (for drawing items, descriptions, etc) by using OpenText method (DataType:=xlFixedWidth). It surprised me that the diff files have different layout in Excel although the AutoCAD's users all used the same way to make the text files and the files have same layout in AutoCAD. For example, the files I dealt with have 7 or 8 or 9 or to 10 cols in Excel, while they are all supposed to be 8 col in both text files and in Excel. I know someone may use diff AutoCAD versions but there are max. 3 versions for the users while I have found 4 diff layouts by col number in Excel (maybe more). Furthermore, even with same number of columns in the opened files in Excel, I found in some files, e.g., col E's content in the place of col D, while in other files col E's content goes to col F. I opened the text files manually in Excel by choosing fixed width data type, all the layouts were exactly same as I did by using the OpenText code.
    My target is to convert these txt files from AutoCAD to Excel 97 files with standard 8 cols, and of course the right data go to right column. So far these diff layouts give me no rule to follow/make my program code. Can someone there give me some advice?

    TIA

    David

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open AutoCAD txt file in Excel 97 (Excel 97)

    Can you show us your code and a little of the file? If you are using xlFixedWidth, it is impossible to say what is happening without seeing what the other prarmeters to OpenText are, particularly the FieldInfo parameter.
    Legare Coleman

  3. #3
    tdhw
    Guest

    Re: Open AutoCAD txt file in Excel 97 (Excel 97)

    Thanks, Legare,

    I didnt use other parameters in my code:
    ....
    Workbooks.OpenText FileName:=direct, DataType:=xlFixedWidth

    Set NewItem = Workbooks(Workbooks.Count)
    ....

    "direct" is a string containing the file name and path. I tried "fieldinfo" parameter, it seemed not to help. However, I m not good at this method. Not sure what is right way to use other parameters.

    David

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open AutoCAD txt file in Excel 97 (Excel 97)

    That is your problem. You will need to use the FieldInfo parameter to tell Excell where the fixed width columns are. Otherwise, it will guess, and it will not usually guess correctly.

    It will not be possible for us to help with the FileInfo parameter without seeing at least some of the file.
    Legare Coleman

  5. #5
    tdhw
    Guest

    Re: Open AutoCAD txt file in Excel 97 (Excel 97)

    Thanks. Now I know where is the problem. As said before, I dont know much about how to use/define FiedInfo parameter for my case and I tried myself but unsuccessful.
    I have attached serveral of the text files as samples and hope to help you to understand the format.
    The difficult point to me is to how to define the fixed width cols. The length of contents in some cols can vary much, say from 10 char to 100 char, but for the requried format converted to Excel the total width of all the cols is limited to A4 landscope size. I tried to use delimited data type it seemed even worse, all the cols mixed up. Can you please show me some examples of how to define FieldInfo in my case? The help file for OpenText cannot give me good hint.

    Thanks again,
    David

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open AutoCAD txt file in Excel 97 (Excel 97)

    Since I don't know what the stuff in the file is, it is dificult for me to figure exactly how you want it loaded. However, the following code should get you close.

    <pre>Public Sub LoadFile()
    Dim vFields As Variant, vFileName As Variant
    vFileName = Application.GetOpenFilename
    If vFileName = False Then Exit Sub
    vFields = Array(Array(0, xlGeneralFormat), Array(3, xlGeneralFormat), Array(28, xlGeneralFormat), _
    Array(34, xlGeneralFormat), Array(54, xlGeneralFormat), _
    Array(134, xlGeneralFormat), Array(154, xlGeneralFormat), Array(168, xlGeneralFormat))
    Workbooks.OpenText Filename:=vFileName, DataType:=xlFixedWidth, FieldInfo:=vFields
    End Sub
    </pre>

    Legare Coleman

  7. #7
    tdhw
    Guest

    Re: Open AutoCAD txt file in Excel 97 (Excel 97)

    Thanks a lot, Legare!

    Your reply is good enough to give me a clear guide.
    I used to use FieldInfo by misunderstood the Help file examples. It seems that if you have the same format for the col, dont need to specify every col because the those examples just show col/array with diff formats and indicate that the remaining formats will be same as the last col/array you defined. I just gave one array (0, xlGeneralFormat) and the result was the same as that I didnt set FiledInfo parameter (now I know why). That was why I removed the FieldInfo parameter.
    I now set 5 cols rather than 8 as it is too difficult to decide a right width for each 8 col due to diff widths of a particular col from diff files. Some of my 5 cols are wide/safe enough to cover two or three original cols. So now all opened files output 5 cols and all the data fall in these 5 cols by certain parterns, I am then be able to use my program to parse and edit them and finally produce the required 8 col format. Now it works very good. Thank you again.

    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •