How to format a closed Excel sheet using VBA
I have sheet1.xls which is closed. I am working in sheet2.xls VBA as follows.
With Range("A:XFD") <I need Some Code here to format entire sheet1.xls cells into string format> End With
Something like this will allow you to format the closed book. It is opened and then formatted and then closed again.
Option Explicit Public Sub Format_Closed_Sheet() Dim sht1book As Workbook Dim sht1ws As Worksheet Dim strValue As String Dim rng As Range Application.ScreenUpdating = False With Application '--> Open sheet1.xls Set sht1book = .Workbooks.Open _ ("Path to Sheet1.xls") End With Set sht1ws = sht1book.Sheets("Sheet1") '--> Format the range as text Set rng = sht1ws.Range("A:XFD") rng.NumberFormat = "@" '--> Save sheet1.xls and close Application.DisplayAlerts = False sht1book.Save sht1book.Close Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
This is how I would do it:
Dim b As Workbook Dim sh As Worksheet Set b = Workbooks.Open("C:\mypath\mybook.xls") ' or wherever your workbook is Set sh = b.Sheets("Sheet1") ' or whatever sheet sh.Cells.NumberFormat = "@" ' format as Text b.Close
If you want to format all of the sheets in your workbook as text, you can do this:
For Each sh In wb.Sheets sh.Cells.NumberFormat = "@" ' format as Text Next sh
Just declare a workbook and sheet:
dim oBook as excel.workbook dim oSheet as excel.worksheet set oBook = workbooks.open("<workbook path and filename>") set oSheet = oBook.sheets("<SheetName>")
with oSheet.range("A:XFD") <Format> end with oBook.close set oBook = nothing set oSheet = nothing
And so on.