Solved

using Autofit to set cell size in csv file

Posted on 2004-11-01
768 Views
Last Modified: 2008-03-10
Hi,

I have a number of csv files that vary in name, the first column contains dates and times so when the file is opened it just shows a series of hashes ##### where the cell width is not wide enough, what I want to do is to set the field to autofit the data when the user opens one of the documents, I am doing this within a Macro in the Personal.xls

I have tried looking for the answer here and on MSDN but can't seem to find the answer, the code below worked (from MSDN) but only when I ran the Macro within VB so it didn't work when I opened the document, plus I hard coded the file path.

Thanks for any help on this


   With ActiveSheet
   Workbooks.Open Filename:="C:\file.csv"
   Dim dataRange
   Set dataRange = Nothing
   For Each x In Selection
       If dataRange Is Nothing Then
           Set dataRange = x
       Else
           Set dataRange = Union(dataRange, x)
       End If
   Next x
   dataRange.Select
   dataRange.Cells.EntireColumn.AutoFit
  End With
0
Question by:trojan_uk
    5 Comments
     
    LVL 35

    Expert Comment

    by:[ fanpages ]
    Hi,

    I presume the ".csv" files are being opened in MS-Excel.

    I am sure you are aware, but just in case you are not, a ".csv" file does not contain any definitions of column widths, it just contains raw data, and it is at the discretion of the application used to display the file contents (in this case, MS-Excel) as to how the file is presented (initially).

    If you wish all the columns to be automatically sized, I suggest you try this:

    Workbooks.Open Filename:="c:\file.csv"
    Cells.Select
    Selection.Columns.AutoFit

    Or, specifically the first column ["A"] only:

    Workbooks.Open Filename:="c:\file.csv"
    Columns(1).AutoFit


    If I misunderstood your requirements, please post another comment.

    BFN,

    fp.

    0
     

    Author Comment

    by:trojan_uk
    Thanks fp,

    That's the sort of thing which works if I run the Macro within VB but I need it to run when the user opens a csv file also instead of defining the file name I need the file name to be passed in depending on what the user opens, which might be 0009.csv or 008.csv or console.csv or any number of permutations.

    Many thanks
    0
     
    LVL 35

    Accepted Solution

    by:
    Hi,

    You would probably have to write an MS-Excel Add-In or have a routine (in a "personal.xls" file) repeatedly polling the Excel Windows to 'detect' if a file (with a type/extension of "csv") has been opened, and then automagically applying the formatting you require.

    I can offer VBA code to allow you to select a ".csv" file, open it, and re-size the column(s) accordingly, but it sounds as if you want the process to be more automated.

    Any purpose-built VBA code would need to be invoked either on the detection of opening a file, or manually.

    Which approach do you prefer?

    BFN,

    fp.
    0
     

    Author Comment

    by:trojan_uk

    I currently use personal.xls to stop the user saving an amended file back into the directory from which it was opened, and redirect them to a pre-defined directory if the file was opened within a directory called Traffic, so I do have the path and file name (as in the class below) just not sure how to adapt it that so it will re-size the column when the file opens or if I should place this within a class or a module



    =====Class=========================

    Option Explicit
    Option Compare Text

    Public WithEvents xlApp As Application

    Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sFile As String
     
        If InStr(1, UCase(Wb.Path), "TRAFFIC") > 1 Then
             Wb.SaveAs "C:\Collector\Amended\" & Wb.Name
       End If
       
    End Sub

    ========================================

    many thanks
    0
     

    Author Comment

    by:trojan_uk
    It's all gone quiet!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now