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

Answers


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

then:

with oSheet.range("A:XFD") 
     <Format>
end with
oBook.close
set oBook = nothing 
set oSheet = nothing

And so on.


Need Your Help

Run an ASP.NET website in a subfolder

asp.net 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.