open csv file in Excel and AutoFit via MS & VBScript

[jpuziano]

Hi Marcus,

In an MS macro, I'd like to open a csv file and have it automatically AutoFit the rows and columns so that all data is visible but I don't want to do this by automating keystrokes.

I thought there must be a VBScript way to do this and I found the following code:

How To Excel VBScript Autofit

Wow - I must get 2 or three searches a day for this. So here is how to do it.

dim excel,wb,ws
set excel=WScriptCreateObject("Excel.Application");
'... get a workbook by add or open
'... get a worksheet from the workbook using the Sheets() collection
'... set ws to that worksheet

' To fit the first 10 columns
for column=1 to 10
ws.columns(column).AutoFit()
next

' To fit the third row
ws.rows(3).AutoFit()

I know, I could not believe how simple it is either!

So I took the VBScript example you posted here...

• Copy CSV to another Open Excel worksheet
  http://www.mjtnet.com/forum/viewtopic.php?p=18563

..and I modified it a bit to add an AutoFit VBScript subroutine and changed the MS code to just open my csv file in Excel, wait, attempt an AutoFit, wait and then close the file. Here's the modified code:

VBSTART
Dim xlApp
Dim xlBook
Dim CSVBook

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = true
Set xlBook = xlApp.Workbooks.open(filename)
end sub

Sub AutoFit
xlBook.columns(1).AutoFit()
xlBook.rows(1).AutoFit()
End Sub

Sub OpenCSVFile(filename)
Set CSVBook = xlApp.Workbooks.open(filename)
end sub

Sub CopyPasteCSV(toSheet,startCell)
Dim xlToRight
Dim xlDown

xlToRight=-4161
xlDown=-4121
CSVBook.ActiveSheet.Range("A1").Select
CSVBook.Activesheet.Range(xlApp.Selection, xlApp.Selection.End(xlToRight)).Select
CSVBook.Activesheet.Range(xlApp.Selection, xlApp.Selection.End(xlDown)).Select
xlApp.Selection.Copy
xlBook.Activate
xlBook.Sheets(toSheet).Select
xlBook.sheets(toSheet).Range(startCell).Select
xlBook.sheets(toSheet).Paste
End Sub

'Use this to close Excel later
Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
End Sub

Sub CloseCSV
CSVBook.close
End Sub

'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
GetCell = xlSheet.Cells(Row, Column).Value
End Function

'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
xlSheet.Cells(Row,Column).Value = NewValue
End Function
VBEND

VBRun>OpenExcelFile,c:\exceltest.csv

Wait>3

VBRun>AutoFit

Wait> 3

VBrun>CloseExcel

However it does not work, I get the following error:

---------------------------
Macro Scheduler
---------------------------
Microsoft VBScript runtime error :438

Object doesn't support this property or method: 'xlBook.columns'

Line 25, Column 3
---------------------------
OK Abort
---------------------------

Also, AutoFit seems to be able to act on a range of cells per the example below:

Format a Range of Cells

Demonstration script that adds data to four different cells in a spreadsheet, then uses the Range object to format multiple cells at the same time.

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.Workbooks.Add

objExcel.Cells(1, 1).Value = "Name"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Interior.ColorIndex = 30
objExcel.Cells(1, 1).Font.ColorIndex = 2
objExcel.Cells(2, 1).Value = "Test value 1"
objExcel.Cells(3, 1).Value = "Test value 2"
objExcel.Cells(4, 1).Value = "Tets value 3"
objExcel.Cells(5, 1).Value = "Test value 4"

Set objRange = objExcel.Range("A1","A5")
objRange.Font.Size = 14

Set objRange = objExcel.Range("A2","A5")
objRange.Interior.ColorIndex = 36

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.AutoFit()

I note that MS has a command that returns the "number of rows and columns containing data in the given sheet" which I believe is the range we need:

XLGetSheetDims

XLGetSheetDims>XLBookHandle,SheetName,MaxRows,MaxCols

This function requires Microsoft Excel to be installed.

Returns the number of rows and columns containing data in the given sheet. XLBookHandle is a handle returned by XLCreate/XLOpen.

Could I use that MS command to get the range and pass that to a VBScript function to AutoFit all rows and columns containing data at once?

Does anyone have a way to do something like this?

Thanks and take care

[hagchr]

Hi jpuziano,

For me it works if I add the following to your code:

Set xlBook = xlBook.sheets("Exceltest")

(ie to add a reference to the sheet in addition to the workbook. Sheet name seems to be the same as the CSV name file).



Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = true
Set xlBook = xlApp.Workbooks.open(filename)
Set xlBook = xlBook.sheets("Exceltest")
end sub

[Marcus Tettmar]

Columns is a property of the sheet not the book (books have sheets which have columns, books don't have columns, sheets do) so change your sub to:

Code: Select all

Sub AutoFit(SheetName) 
  xlBook.sheets(SheetName).columns(1).AutoFit()  
  xlBook.sheets(SheetName).rows(1).AutoFit() 
End Sub

Then call with:

VBRun>AutoFit,Sheet1

Or whatever the sheet name is.,

[jpuziano]

Hi hagchr and Marcus,

Thanks for the help, much appreciated... I have it working now.

Take care