Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

using Autofit to set cell size in csv file

Posted on 2004-11-01
5
Medium Priority
?
1,198 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
Comment
Question by:trojan_uk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 12467376
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
ID: 12471073
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:
[ fanpages ] earned 500 total points
ID: 12471232
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
ID: 12471401

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
ID: 12502802
It's all gone quiet!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

618 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