?
Solved

Expand Excel columns using VBscript

Posted on 2011-03-09
1
Medium Priority
?
718 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 2000 total points
ID: 35093466
Try

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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

621 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