using Autofit to set cell size in csv file


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
           Set dataRange = Union(dataRange, x)
       End If
   Next x
  End With
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:

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"

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

Workbooks.Open Filename:="c:\file.csv"

If I misunderstood your requirements, please post another comment.



trojan_ukAuthor Commented:
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
[ fanpages ]IT Services ConsultantCommented:

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?



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trojan_ukAuthor Commented:

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


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
trojan_ukAuthor Commented:
It's all gone quiet!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.