asked on

using Autofit to set cell size in csv file

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

[ 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.

trojan_uk

ASKER

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

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trojan_uk

ASKER

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

trojan_uk

ASKER

It's all gone quiet!