Solved

Expand Excel columns using VBscript

Posted on 2011-03-09
1
705 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

This tutorial will discuss fancy secure registration forms, with AJAX technology support. In this article I assume you already know HTML and some JS. I will write the code using WhizBase Server Pages, so you need to know some basics in WBSP (you mig…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

20 Experts available now in Live!

Get 1:1 Help Now