vba Excel中出现错误“另存为方法工作簿失败”

Question

我有多个优秀的基本数据，我需要根据列对数据进行排序，而排序后的数据有一个唯一的值，这些唯一的值我放在数组中，基于唯一的值必须创建一个新的工作簿。

我的问题：

1. 当我执行宏时，有时它会将错误显示为saveas method workbook class failed
2. 如何冻结列

我的代码：

sub marcel()
Dim sArray as string
Dim saArray as string
Dim Lastrow_Sub As Integer
Dim Lastrow_Aging As Integer
Dim Array_Sub As Variant
Dim Array_Sub_Aging As Variant
Dim rngFilter_Ws2 as range
Dim Sht6 as worksheet
Dim ColumnsToRemove2 as variant
Dim j2 as integer
Dim vItem2 as variant

Check_date = Format(Date, "yymm")
         Sheets("q_Aging_base_data_incl_FDB").Columns("D:D").AdvancedFilter Action:=xlFilterCopy, _
         CopyToRange:=.Range("AY1"), Unique:=True
         Lastrow_Aging = .Cells(.Rows.Count, "AY").End(xlUp).Row
         Array_Sub_Aging = Range("AY2:AY" & Lastrow_Aging)
         saArray = Array_Sub_Aging(m, 1)
            Sheets("BASE qry_Inventory Activation b").Columns("H:H").AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=.Range("AZ1"), Unique:=True
            Lastrow_Sub = .Cells(.Rows.Count, "AZ").End(xlUp).Row
            Array_Sub = Range("AZ2:AZ" & Lastrow_Sub)
            sArray = Array_Sub(k, 1)
If sArray <> "APE" And saArray = "APE" Or sArray <> "XXUMA" And saArray = "XXUMA" Then
                  Dim NewBook_Sub_Aging As Workbook
                  Set NewBook_Sub_Aging = Workbooks.Add
                  With NewBook_Sub_Aging
                        .Title = saArray
                        NewBook_Sub_Aging.Worksheets("sheet1").Name = "Aging Inventory"
                        With rngFilter_Ws2
                            .AutoFilter Field:=4, Criteria1:=saArray, Operator:=xlFilterValues
                            .AutoFilter Field:=15, Criteria1:="reporting relevant Location", Operator:=xlFilterValues
                            .AutoFilter Field:=32, Criteria1:="<>(a)  0 - 360", Operator:=xlFilterValues
                            Set rngCopyAging_Sub = .SpecialCells(xlCellTypeVisible)
                                                   .AutoFilter ' Switch off AutoFilter
                        End With
                        rngCopyAging_Sub.Copy Destination:=NewBook_Sub_Aging.Worksheets("Aging Inventory").Cells(1, 1)
                         ' Delete unwanted columns for subregions(APE and XXUMA) Aging
                         Set Sht6 = NewBook_Sub_Aging.Worksheets("Aging Inventory")
                          ColumnsToRemove2 = Array("Period", "AP_BU", "Subregion", "Strategic_BU", "Company_Code", "Company_name", "Plant_name", _
                                            "Rep Location", "Storage_Location", "Storage_Location_name", "Date_last_goods_rec", "Stock_type", _
                                            "Stock_type_name", "Kind_of_Material_name", "Supplier_name", "SummevonVEU_OIV1", "Days_since_production", _
                                            "Remaining_shelf_life", "APO_Planner", "S_SCM_or_SVC")
                          For j2 = LBound(ColumnsToRemove2) To UBound(ColumnsToRemove2) Step 1
                                vItem6 = Application.Match(ColumnsToRemove2(j2), Sht6.Rows(1), 0)
                                Debug.Print vItem6
                            If IsNumeric(vItem6) Then
                                Sht6.Columns(vItem6).Delete
                             End If
                          Next j2
                                NewBook_Sub_Aging.Worksheets("Aging Inventory").Cells.EntireColumn.AutoFit
                                NewBook_Sub_Aging.Worksheets("Aging Inventory").Range("A1:P1").AutoFilter
                                NewBook_Sub_Aging.Worksheets("Aging Inventory").Range("A2:P2").Select
                                ActiveWindow.FreezePanes = True

                        .SaveAs Filename:="KPI" & " " & saArray & " " & Check_date & ".xlsx"
                        Application.DisplayAlerts = False
                  NewBook_Sub_Aging.Close
                  End With
             End If
end sub

有谁能帮我解决这个问题吗。

Answer 1

sArray和saArray似乎都没有被定义为实际的数组。数组定义如下：

Dim myArray() as Integer

分配给任何阵列位置的任何值都需要以这种方式放置：

myArray(i) = 815 'i being a position, like 0 or 5

当你想获得某个位置的值时，你也会引用那个位置。所以，如果我想要五月阵列第四个位置的值，我会做

myArray(3) 'Arrays are zero-index based.

所以当你这样做的时候：

.SaveAs Filename:="KPI" & " " & saArray & " " & Check_date & ".xlsx"

您为Filename参数提供了一个可能不包含任何内容的变量，因此该行将抛出一个错误。

我假设saArray和Check_date是来自不同Sub的全局变量，并且创建了数组并在那里分配了值；它仍然会抛出一个错误，因为您给SaveAs模块的是一个数组，而不是字符串。您必须给它一个数组中的值（它必须是字符串，因为Filename必须是字符串）。