Question

请求帮助使用vba从一本工作手册中删除格式。我的法典是行之有效的:它根据工作量选择了Excel文档。进口另一本工作手册,但希望在进口前采用多种格式,如超链接、字体格式。由于存在进口问题,该方案不能通知我。太多形式的我很难要求帮助。谢谢。

Sub add()
    
     On Error Resume Next

    Application.DisplayAlerts = False

    Application.ScreenUpdating = False

    Dim FNames As Variant
    Dim Cnt As Long
    Dim MstWbk As Workbook
    Dim ws As Worksheet

    Set MstWbk = ThisWorkbook

    FNames = Application.GetOpenFilename(fileFilter:="Excel files (*.xls*), *.xls*", MultiSelect:=True, Title:="choose Files")
    If Not IsArray(FNames) Then Exit Sub
    
    ThisWorkbook.ActiveSheet.Cells.ClearHyperlinks

    For Cnt = 1 To UBound(FNames)
        Set ws = Workbooks.Open(FNames(Cnt)).Sheets(1)
        ws.Copy After:=MstWbk.Sheets(MstWbk.Sheets.Count) 
        MstWbk.Sheets(MstWbk.Sheets.Count).Name = Left(ws.Parent.Name, InStr(2, ws.Parent.Name, ".") - 1)
        ws.Parent.Close False
    Next Cnt

    MsgBox "Import complete!", vbInformation
    
    Application.ScreenUpdating = True
    
    Application.DisplayAlerts = True

End Sub

在进口另一本工作手册之前澄清档案格式。

Answer 1

Pls尝试。

    For Cnt = 1 To UBound(FNames)
        Set ws = Workbooks.Open(FNames(Cnt)).Sheets(1)
        ws.ClearFormats    ** clear all formats
        ws.Copy After:=MstWbk.Sheets(MstWbk.Sheets.Count)
        MstWbk.Sheets(MstWbk.Sheets.Count).Name = Left(ws.Parent.Name, InStr(2, ws.Parent.Name, ".") - 1)
        ws.Parent.Close False
    Next Cnt

Microsoft documentation:

明确表格方法(Excel)

友情链接