Announcement

Collapse
No announcement yet.

Autofit colums in csv file and save

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Autofit colums in csv file and save

    I am trying to creat a script that will open up a current .csv file, auto fit all the columns and save it. Here is what I have so far but I cannot get it to work.

    Code:
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\Ballpark.csv")
    
    objExcel.Visible = True
    
    Set objRange = objExcel.Range("A1:H1")
    objRange.Autofit()
    
    objExcel.Quit

  • #2
    Re: Autofit colums in csv file and save

    CSV files don't have column widths, they simply have lists of values separated by commas.

    If you want to have column widths you have to save it as an Excel Spreadsheet.


    Tom
    For my own and your protection, I do not provide support by private message under any circumstances. All such messages will be deleted and ignored.

    Anything you say will be misquoted and used against you

    Comment


    • #3
      Re: Autofit colums in csv file and save

      Ok thanks...I don't really have an option, it has to be a csv file.

      Comment


      • #4
        Re: Autofit colums in csv file and save

        Unfortunately (I don't know if you looked at one with a text editor like notepad) CSV files are simply plain text: They look like the following:

        Code:
        Serial, Name, Phone, house number
        1,Dave,123456,27
        2,Phil,284495,31
        3,Andrew,112233,1
        Nowhere even to PUT column width information for Excel to refer to later, after saving. If it absolutely has to be CSV, read it in a text reader/text editor...


        Tom
        For my own and your protection, I do not provide support by private message under any circumstances. All such messages will be deleted and ignored.

        Anything you say will be misquoted and used against you

        Comment


        • #5
          Re: Autofit colums in csv file and save

          Just had a play with this and found you can do it:

          Code:
          objExcel.Columns("A:AY").EntireColumn.AutoFit
          Replace the code below with the code above

          Code:
          Set objRange = objExcel.Range("A1:H1")
          objRange.Autofit()
          I think it is because you cannot autofit just 8 cells. You have to do it by columns.
          Server 2000 MCP
          Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

          ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

          Comment


          • #6
            Re: Autofit colums in csv file and save

            Yes, you can autofit the columns while the file is loaded in Excel, but when you save it back to CSV all that column formatting information is lost and you revert to basic CSV information as I list above.


            Tom
            For my own and your protection, I do not provide support by private message under any circumstances. All such messages will be deleted and ignored.

            Anything you say will be misquoted and used against you

            Comment


            • #7
              Re: Autofit colums in csv file and save

              I don't understand the point of the autofit if it has to be SAVED in csv? OP please explain.
              Server 2000 MCP
              Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

              ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

              Comment


              • #8
                Re: Autofit colums in csv file and save

                Yeah its annoying how it goes back...it doesn't make any sense.

                Comment


                • #9
                  Re: Autofit colums in csv file and save

                  What I am actually doing so you guys get an idea, is exporting users from Active Directory using CSVDE. But of course nothing is formatted and looks ugly when you export them.

                  Comment


                  • #10
                    Re: Autofit colums in csv file and save

                    Ok so lets say I can convince whoever to use .xls and not .csv...I still can't get the changes to save when saving it as a .xls file.

                    Code:
                    Set objExcel = CreateObject("Excel.Application")
                    objExcel.Application.DisplayAlerts = False
                    objExcel.Visible = False
                    Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\CSVDE\Ballpark.csv")
                    objExcel.Columns("A:AY").EntireColumn.AutoFit
                    objExcel.Columns("A:A").Delete
                    objExcel.ActiveWorkbook.SaveAs("C:\Documents and Settings\CSVDE\Ballpark.xls")
                    objExcel.Quit

                    Comment


                    • #11
                      Re: Autofit colums in csv file and save

                      Change this line:

                      Code:
                      objExcel.ActiveWorkbook.SaveAs("C:\Documents and Settings\CSVDE\Ballpark.xls")
                      to this:

                      Code:
                      objExcel.ActiveWorkbook.SaveAs("C:\Documents and Settings\CSVDE\Ballpark.xls"), 43
                      The 43 changes the format to Excel 95/97 format. More info here:

                      http://www.microsoft.com/technet/scr...6/hey0307.mspx
                      Server 2000 MCP
                      Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

                      ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                      Comment


                      • #12
                        Re: Autofit colums in csv file and save

                        Thanks that works.

                        Comment


                        • #13
                          Re: Autofit colums in csv file and save

                          Originally posted by ekrengel View Post
                          Thanks that works.
                          Glad i could help!
                          Server 2000 MCP
                          Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

                          ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                          Comment


                          • #14
                            Re: Autofit colums in csv file and save

                            Do you know what the command is to put cell borders on the spreadsheet for a certain selection?

                            Comment


                            • #15
                              Re: Autofit colums in csv file and save

                              I'm using this but getting errors:

                              Code:
                              objExcel.Range("A1:H1").Select
                              objExcel.Selection.Font.Bold = True
                              objExcel.Selection.Font.Size = 11
                              objExcel.Selection.Interior.ColorIndex = 11 
                              objExcel.Selection.Interior.Pattern = 1 'xlSolid 
                              objExcel.Selection.Font.ColorIndex = 2
                              objExcel.Selection.WrapText = True
                              objExcel.Selection.Borders(xlTop).LineStyle = xlNone
                              objExcel.Selection.Borders(xlBottom).LineStyle = xlNone
                              objExcel.Selection.Borders(xlLeft).LineStyle = xlContinuous
                              objExcel.Selection.Borders(xlRight).LineStyle = xlContinuous

                              Comment

                              Working...
                              X