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

Kinldy help.Thanks


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
    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


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 ="<workbook path and filename>")
set oSheet = oBook.sheets("<SheetName>")


with oSheet.range("A:XFD") 
end with
set oBook = nothing 
set oSheet = nothing

And so on.

Need Your Help

Run an ASP.NET website in a subfolder configuration web-config screwturn

Is there a way of running an ASP.NET website in a subfolder of the website?

How to pass 2 variables to a php document with jquery

php jquery ajax variables datepicker

Hello I have two text inputs which get their values from a datepicker.