如何使用VBA格式化关闭的Excel工作表 [英] How to format a closed Excel sheet using VBA

问题描述

我有被关闭的sheet1.xls.我正在按以下方式在sheet2.xls 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的帮助.谢谢

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
    sht1book.Save
    sht1book.Close
    Application.DisplayAlerts = True

    Application.ScreenUpdating = True
End Sub

这篇关于如何使用VBA格式化关闭的Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！