宏在Excel 2003中有效，而在2007年不可用 [英] Macro works in Excel 2003, not in 2007

问题描述

我有一个宏，它可以导出一个工作簿，该工作簿在Excel 2003下可以很好地工作(并且可以使用很多年).但是，它不能在具有2007或2010的任何计算机上运行.它会运行并打开Save As框，但是无论我键入什么内容，当我单击确定"时，它就坐在那儿.单击Ok保存不会执行任何操作.有人可以帮忙吗?

I have a macro that exports a workbook, which works perfectly fine (and has for years) under Excel 2003. However, it doesn't work on any machine with 2007 or 2010. It runs and opens the Save As box, however no matter what I type, when I click ok, it just sits there. Clicking Ok to save doesn't do anything. Can someone help please?

代码:

Sub ExportReports()

Dim fdialog As Office.FileDialog
Dim varfile As String

Static varfile_name As String
Dim curr_wb_name As String
Dim num_sheets As Integer
Dim xflag As String
Dim openflag As Boolean
Static strpassword As String


'check to see if invoice has been moved
'check to see if all programs report has been moved
'move specified report


'User selects the file containing the budget - must be in set format
'Changes to the format of budget spreadsheet are likely to affect this code

curr_wb_name = ActiveWorkbook.Name
prog_name = ActiveWorkbook.Worksheets("Menu").Range("F14")

lineselectfile: 
Set fdialog = Application.FileDialog(msoFileDialogFilePicker)

With fdialog
    .Title = "Please select or create the file you wish to export reports to"
    .Filters.Clear
    .Filters.Add "Microsoft Excel Files", "*.xlsx"

    If .Show = True Then
        varfile = .SelectedItems(1)
    Else
    Exit Sub
        'MsgBox "You must select a file to import, please try again", _
        '        vbOKOnly, "Import Error!"
        'GoTo lineselectfile
    End If
End With

If strpassword = "" Then
   strpassword = InputBox("Enter a password to protect worksheets in this file")
End If

n = 0
For n = 1 To Workbooks.Count
   If Workbooks(n).Name = varfile_name Then
     openflag = True
     Workbooks(n).Activate
   End If
Next

If openflag = False Then
   Workbooks.Open Filename:=varfile, UpdateLinks:=0
End If

varfile_name = ActiveWorkbook.Name
num_sheets = Workbooks.Count
'n = 0
xflag = "a"
'Do Until n = num_sheets
If Sheets(1).Name = "Invoice" Then
   xflag = xflag & "b"
End If
If Sheets(2).Name = "All Programs" Then
   xflag = xflag & "c"
End If
'n = n + 1
'Loop

Select Case xflag
Case "a" ' Both Invoice and All Programs must be exported
    Windows(curr_wb_name).Activate
    Sheets("Invoice").Select
    Sheets("Invoice").Copy before:=Workbooks(varfile_name).Sheets(1)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=False
    ActiveSheet.Protect Password:=strpassword, Scenarios:=True
    Range("a1").Select
    Windows(curr_wb_name).Activate
    Sheets("Preview All Programs").Select
    Sheets("Preview All Programs").Copy before:=Workbooks(varfile_name).Sheets(2)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=False
    Sheets("Preview All Programs").Name = "All Programs"
    ActiveSheet.Protect Password:=strpassword, Scenarios:=True
    Range("a1").Select
Case "ab" ' Only All Programs must be exported
    Windows(curr_wb_name).Activate
    Sheets("Preview All Programs").Select
    Sheets("Preview All Programs").Copy After:=Workbooks(varfile_name).Sheets(2)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=False
    Sheets("Preview All Programs").Name = "All Programs"
    ActiveSheet.Protect Password:=strpassword, Scenarios:=True
    Range("a1").Select
Case "ac" ' Only invoice must be exported
    Windows(curr_wb_name).Activate
    Sheets("Invoice").Select
    Sheets("Invoice").Copy After:=Workbooks(varfile_name).Sheets(1)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=False
    ActiveSheet.Protect Password:=strpassword, Scenarios:=True
    Range("a1").Select

End Select
    Windows(curr_wb_name).Activate
    Sheets("Preview").Select
    Sheets("Preview").Copy After:=Workbooks(varfile_name).Sheets(2)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=False
    Sheets("Preview").Name = prog_name
    ActiveSheet.Protect Password:=strpassword, Scenarios:=True
    Range("a1").Select
    Windows(curr_wb_name).Activate
    Worksheets("Menu").Activate
    'Workbooks(varfile_name).Close

End Sub

推荐答案

那里有很多代码，但是关于Excel 2007中的更改只有一件事跳了出来.在2003年，如果您将工作表复制到另一个位置，它过去就变成了ActiveSheet. 不会发生在2007年以后，因此您需要重新编写代码以显式引用该副本.

Lot of code there, but only one thing jumps out regarding changes in Excel 2007. In 2003, if you copied a sheet to another location, it used to become the ActiveSheet. That doesn't happen in 2007+, so you need to re-work your code to explicitly reference the copy.

例如:

Dim shtCopy as Worksheet

'copy a sheet
ThisWorkbook.Sheets("Template").Copy After:=Thisworkbook.Sheets("Data")
'get a reference to the copy
Set shtCopy = ThisWorkbook.Sheets(Thisworkbook.Sheets("Data").Index+1)

您真的是这个意思

num_sheets = Workbooks.Count

而不是

num_sheets = ActiveWorkbook.Sheets.Count

?

最好，我猜这应该对您有用

best I can guess this should work for you

Sub ExportReports()

    Static varfile_name As String
    Static strpassword As String

    'Dim fdialog As Office.FileDialog
    Dim varfile As String
    Dim prog_name As String
    Dim curr_wb As Workbook
    Dim selected_wb As Workbook

    Dim xflag As String
    Dim n As Integer

    Set curr_wb = ActiveWorkbook
    prog_name = curr_wb.Worksheets("Menu").Range("F14")

    'Set fdialog = Application.FileDialog(msoFileDialogFilePicker)
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Please select or create the file you wish to export reports to"
        .Filters.Clear
        .Filters.Add "Microsoft Excel Files", "*.xlsx"
        If .Show = True Then
            varfile = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With

    If strpassword = "" Then
       strpassword = InputBox("Enter a password to protect worksheets in this file")
    End If

    'tw Not sure what the purpose of this is?
    '  by default it will select the *previous* selected wb...
    For n = 1 To Application.Workbooks.Count
       If Workbooks(n).Name = varfile_name Then
         Set selected_wb = Workbooks(n)
         Exit For 'break out of loop
       End If
    Next

    If selected_wb Is Nothing Then
       Set selected_wb = Workbooks.Open(Filename:=varfile, UpdateLinks:=0)
    End If

    varfile_name = selected_wb.Name
    xflag = "a"
    If selected_wb.Sheets(1).Name = "Invoice" Then
       xflag = xflag & "b"
    End If
    If selected_wb.Sheets(2).Name = "All Programs" Then
       xflag = xflag & "c"
    End If

    Select Case xflag
    Case "a" ' Both Invoice and All Programs must be exported

        CopySheet curr_wb.Sheets("Invoice"), _
                  selected_wb, 1, "", strpassword

        CopySheet curr_wb.Sheets("Preview All Programs"), _
                  selected_wb, 2, "All Programs", strpassword

    Case "ab" ' Only All Programs must be exported

        CopySheet curr_wb.Sheets("Preview All Programs"), _
                  selected_wb, 3, "All Programs", strpassword

    Case "ac" ' Only invoice must be exported

        CopySheet curr_wb.Sheets("Invoice"), _
                  selected_wb, 2, "", strpassword

    End Select

    CopySheet curr_wb.Sheets("Preview"), _
                  selected_wb, 3, prog_name, strpassword


    curr_wb.Activate
    curr_wb.Worksheets("Menu").Activate

    'selected_wb.Close

End Sub

'Copy sheet to specific position, convert to values,
'  change name
Sub CopySheet(wsToCopy As Worksheet, destWb As Workbook, _
              destPos As Integer, newName As String, pw As String)
    Dim shtCopy As Worksheet

    If destPos = 1 Then
        wsToCopy.Copy Before:=destWb.Sheets(1)
    Else
        wsToCopy.Copy After:=destWb.Sheets(destPos - 1)
    End If
    With destWb.Sheets(destPos)
        .UsedRange.Value = .UsedRange.Value
        If Len(newName) > 0 Then .Name = newName
        .Protect Password:=pw, Scenarios:=True
        .Range("A1").Select
    End With
End Sub

这篇关于宏在Excel 2003中有效，而在2007年不可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！