?
Solved

Expand Excel columns using VBscript

Posted on 2011-03-09
1
Medium Priority
?
713 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
[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
1 Comment
 
LVL 6

Accepted Solution

by:
TinTombStone earned 2000 total points
ID: 35093466
Try

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

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Batch, VBS, and scripts in general are incredibly useful for repetitive tasks.  Some tasks can take a while to complete and it can be annoying to check back only to discover that your script finished 5 minutes ago.  Some scripts may complete nearly …
This article will show, step by step, how to integrate R code into a R Sweave document
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Suggested Courses

765 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