Solved

Expand Excel columns using VBscript

Posted on 2011-03-09
1
703 Views
Last Modified: 2012-05-11
Running Excel 2000 on a pc running Windows XP pro.   I want to expand the columns.  My code is below.  The command:

objExcel.Range("A3:H3")Columns.AutoFit

Does not work.  What is the correct syntax to expand the columns so the data will fit?

Thank you.
' ==============  DiskDriveFileList.vbs ========================
'   Purpose:  List files and folders from a disk drive to an Excel spreadsheet
' =========================================================================

Option Explicit

Dim objExcel   'Reference to Excel

Dim strSheetName
Dim strFolderName

Set objExcel = WScript.CreateObject("Excel.Application") 'Instantiate Excel

'Main Function

  strSheetName	= "\\sbs2003server\E-drive\TVDeleteFiles\Test.xls"
  strFolderName	= "\\sbs2003server\E-drive\TVDeleteFiles\"
  
  fnCreateNewSheet()
  
  fnWriteExcelData(strFolderName) 
  
  fnSaveExcelSheet(strSheetName)
  
  fnCloseSheetAndExcel()
  
  fnTerminateScript()
  
  Function fnCreateNewSheet()
    
    ' == Workbooks is a collection.  Add() is a method belonging to Workbooks collection
    ' == Open new worksheet
    objExcel.Workbooks.Add
    
  End Function

  Function fnWriteExcelData(strFldName)
    With objExcel.Range("A1")
         .Formula	= "Folder Contents"
         .Font.Bold	= True
         .Font.Size	= 12
    End With

    objExcel.Range("A3").Formula		= "File Name:"
    objExcel.Range("B3").Formula		= "File Size:"
    objExcel.Range("C3").Formula		= "File Type:"
    objExcel.Range("D3").Formula		= "Date Created:"
    objExcel.Range("E3").Formula		= "Date Last Accessed"
    objExcel.Range("F3").Formula		= "Date Last Modified"
    objExcel.Range("G3").Formula		= "Attributes"
    objExcel.Range("H3").Formula		= "Short File Name"
    objExcel.Range("A3:H3").Font.Bold		= True
    objExcel.Range("A3:H3")Columns.AutoFit
    
    subListFilesInFolder strFldName, True
    
  End Function

  ' == List files in folders and sub folders
  Sub subListFilesInFolder(strSourceFldrName, bolSubFolders)
    
  End Sub

  Function fnSaveExcelSheet(strSheetNm)
    ' == Use the ActiveWorkBook property to reference the current workbook.
    ' == Use the WorkBook object SaveAs function
    
    objExcel.ActiveWorkBook.SaveAs(strSheetNm)
    
  End Function

  Function fnCloseSheetAndExcel()
    ' == Use the Workbook object Close() Method
    objExcel.ActiveWorkbook.Close()
    objExcel.Quit()
    
  End Function

  Function fnTerminateScript()
    WScript.Quit()    'Terminate script execution
    
  End Function

Open in new window

0
Comment
Question by:donpick
1 Comment
 
LVL 6

Accepted Solution

by:
TinTombStone earned 500 total points
ID: 35093466
Try

objExcel.Columns("A:H").AutoFit
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this tutorial I will show you how to make a simple HTML bar chart with the usage of WhizBase, If you want more information about WhizBase please read my previous articles at http://www.experts-exchange.com/ARTH_5123186.html (http://www.experts-ex…
I hope you'll find this tutorial useful and interesting. So let's try to extend Tcl with a new package.  For anyone more deeply interested please check out the book "Practical Programming in Tcl and Tk". It's really one of the best written books abo…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

747 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

12 Experts available now in Live!

Get 1:1 Help Now