脚踏实地 精选回答 1、启用宏的步骤如下：1，在Excel的菜单栏上依次单击“工具”——“宏”——“安全性”，打开下图所示的“安全性”对话框，2，在“安全级”选项卡中将宏的安全级别设置为“中”或“低”。2、其中推荐使用“中”级，这样的设置并不会降低您的安全性，您仍然对任意一个宏文件都拥有主动选择是否运行的权力。3、在宏的安全性级别设置成功后，再次打开Excel程序文件时，会出现下图所示信息框，此时点击“启用宏”按钮即可使本程序正常运行：在Excel2007/2010版本中，可以依以下步骤操作：1，依次单击Excel的Office按钮（2010版本为“文件”按钮）——“Excel选项”，打开下图所示的“Excel选项”选项卡，在左侧列表中选择“信任中心”，在右侧出现的窗口中点击“信任中心设置”按钮，2，打开“信任中心”选项卡，在左侧选择“宏设置”，在右侧出现的窗口中选择“禁用所有宏，并发出通知”选项，单击确定完成。4、在宏的安全性级别设置成功后，再次打开Excel程序文件时，在信息栏（编辑栏上方）会出现下图所示的警告信息，此时点击其中的“选项”按钮打开Office安全选项对话框，在其中选择“启用此内容”选项并确定即可使本程序正常运行。 倾听幸福 2023-12-09 15:41:531-2021-4041-6061-8081-100101-120121-140141-160161-180181-2001-2021-4041-6061-8081-100101-120121-140141-160161-180181-200

社区首页 >问答首页 >从MS Access调用时，Excel宏在启动时无法打开Stack Overflow用户提问于 2020-12-24 11:27:55回答 1查看 40关注 0票数 0我知道这个问题已经被讨论过很多次了，但我的问题并没有在那里得到解决。我有一个excel宏，用于不同的项目。它是用变量编码的，变量是从MS Access传递过来的，因此，这个宏可以用于任何项目。它不仅在双击时运行良好，而且也适用于其他项目。我能想到的唯一让这个项目与众不同的是，在同一过程中，另一个excel文件在几个步骤之前被称为。但是在运行之后，该excel实例被关闭(我检查了，它确实关闭了，我通过代码语言：javascriptSET myexcelinstance = nothing 那会是什么呢？我也可以双击这个项目的文件，所有的东西都会被创建，没有错误。但是当从Ms Access调用它时，它只是像打开一个常规文件一样打开。编辑:添加宏这是一个单独的模块代码语言：javascriptOption Explicit
Public Sub auto_open()
MainProcedure
End Sub这是一个单独的模块中的主过程代码语言：javascriptOption Explicit
Public x
As Integer
Public PadLength As Integer
Public LastRow As Long
Public LastRow4 As Long
Public LastRow2 As Long
Public CurPath As String
Public ProjectName As String
Public FormattedDate As String
Public RunDate As Date
Public ReportPath As String
Public MonthlyPath As String
Public TableName As String
Public FinalExcelFileName As String
Public ExcelFileName As String
Public ExcelSheetName As String
Public ExcelTemplate As String
Public ExcelPasteTo As String
Public TemplateFileName As String
Public SheetToSelect As String
Public FSO
Public oShell As Object
Public iResponse As Integer
Public CurCell, CurRange As Range
Public CurRowNum As Long
Public LastRowOfSection As Long
Public FirstRowOfSection As Long
Public CurLastColumn As Variant
Public CurLastRow As Long
Public CurFileName As String
Public CurSheetName As String
Public Sub MainProcedure()
Application.EnableCancelKey = xlDisabled
Application.DisplayAlerts = False
'Application.ScreenUpdating = False
Application.EnableEvents = False
CurPath = ActiveWorkbook.Path & "\"
'this is to deselect sheets
Sheets("QFilesToExportEMail").Select
Sheets("QReportDates").Activate
FormattedDate = Range("A2").Value
RunDate = Range("B2").Value
ReportPath = Range("C2").Value
MonthlyPath = Range("D2").Value
ProjectName = Range("E2").Value
Windows(ProjectName & ".xlsm").Activate
Sheets("QFilesToExportEMail").Select
'Ctrl + Shift + End
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim i
As Integer
CurRowNum = 2
Set CurRange = Sheets("QFilesToExportEMail").Range("B" & CurRowNum & ":B" & LastRow) '''''sets the range to use
For Each CurCell In CurRange
''''checks each cell in range
If CurCell <> "" Then
'''''will only do something if the cell is not blank
Windows(ProjectName & ".xlsm").Activate
Sheets("QFilesToExportEMail").Select
ExcelFileName = Range("B" & CurRowNum).Value
FinalExcelFileName = Range("B" & CurRowNum).Value
LastRowOfSection = Sheets("QFilesToExportEMail").Range("B" & CurRowNum & ":B" & LastRow).Find(what:=ExcelFileName, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
'FirstRowOfSection = Sheets("QFilesToExportEMail").Range("B" & CurRowNum & ":B" & LastRow).Find(what:=ExcelFileName).Row
TemplateFileName = Range("F" & CurRowNum).Value
FirstRowOfSection = ActiveWorkbook.Worksheets("QFilesToExportEMail").Columns(2).Find(ExcelFileName).Row
TableName = Range("A" & CurRowNum).Value
ExcelSheetName = Range("C" & CurRowNum).Value
If ExcelSheetName = "" Then
ExcelSheetName = TableName
End If
If CurRowNum = FirstRowOfSection Then
SheetToSelect = ExcelSheetName
End If
If IsNull(TemplateFileName) Or TemplateFileName = "" Then
Workbooks.Add
Else
Workbooks.Open CurPath & TemplateFileName
End If
ActiveWorkbook.SaveAs MonthlyPath & FinalExcelFileName
For i = CurRowNum To LastRowOfSection
Windows(ProjectName & ".xlsm").Activate
Sheets("QFilesToExportEMail").Select
TableName = Range("A" & i).Value
ExcelSheetName = Range("C" & i).Value
ExcelTemplate = Range("D" & i).Value
ExcelPasteTo = Range("E" & i).Value
If ExcelSheetName = "" Then
ExcelSheetName = TableName
End If
If ExcelTemplate = "format" Then
Windows(FinalExcelFileName).Activate
ActiveWorkbook.Sheets.Add(after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = ExcelSheetName
Windows(ProjectName & ".xlsm").Activate
Sheets(TableName).Select
CurLastColumn = MyColumnLetter(Range("A1").CurrentRegion.Columns.Count)
CurLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:" & CurLastColumn & CurLastRow).Select
Selection.Copy
Windows(FinalExcelFileName).Activate
Sheets(ExcelSheetName).Select
Range(ExcelPasteTo).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
FormatBasics
ElseIf ExcelTemplate = "" Then
Windows(ProjectName & ".xlsm").Activate
Sheets(TableName).Select
CurLastColumn = MyColumnLetter(Range("A1").CurrentRegion.Columns.Count)
CurLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:" & CurLastColumn & CurLastRow).Select
Selection.Copy
Windows(FinalExcelFileName).Activate
Sheets(ExcelSheetName).Select
Range(ExcelPasteTo).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'
'
Dim SearchString As String
'
Dim SearchRange As Range, cl As Range
'
Dim FirstFound As String
'
Dim sh As Worksheet
'
'
' Set Search value
'
SearchString = "N/A"
'
Application.FindFormat.Clear
'
' loop through all sheets
'
For Each sh In ActiveWorkbook.Worksheets
'
' Find first instance on sheet
'
Set cl = sh.Cells.Find(What:=SearchString, _
'
After:=sh.Cells(1, 1), _
'
LookIn:=xlValues, _
'
LookAt:=xlPart, _
'
SearchOrder:=xlByRows, _
'
SearchDirection:=xlNext, _
'
MatchCase:=False, _
'
SearchFormat:=False)
'
If Not cl Is Nothing Then
'
' if found, remember location
'
FirstFound = cl.Address
'
' format found cell
'
Do
'
cl.Font = "Calibri"
'
cl.Interior.ColorIndex = 3
'
' find next instance
'
Set cl = sh.Cells.FindNext(After:=cl)
'
' repeat until back where we started
'
Loop Until FirstFound = cl.Address
'
End If
'
Next
ElseIf ExcelTemplate Like "*TEMPLATE*" Then
Windows(ProjectName & ".xlsm").Activate
Sheets(ExcelTemplate).Copy after:=Workbooks(FinalExcelFileName).Sheets(1)
ActiveSheet.Name = ExcelSheetName
ActiveSheet.Move after:=Worksheets(Worksheets.Count) 'moves it to the end
Sheets(ExcelSheetName).Select
Windows(ProjectName & ".xlsm").Activate
Sheets(TableName).Select
CurLastColumn = MyColumnLetter(Range("A1").CurrentRegion.Columns.Count)
CurLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:" & CurLastColumn & CurLastRow).Select
Selection.Copy
Windows(FinalExcelFileName).Activate
Sheets(ExcelSheetName).Select
Range(ExcelPasteTo).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Range(ExcelPasteTo).Select
Next i
If LastRowOfSection < LastRow Then
CurRowNum = LastRowOfSection + 1
Else
CurRowNum = LastRowOfSection
End If
End If
Windows(FinalExcelFileName).Activate
If CheckSheet("Sheet1") Then
Sheets("Sheet1").Delete
End If
Sheets(SheetToSelect).Select
ActiveWorkbook.Save
ActiveWorkbook.Close
If LastRowOfSection >= LastRow Then
Exit For
End If
Next
CurSheetName = ""
Windows(ProjectName & ".xlsm").Activate
Sheets("QFilesToExportEMail").Select
'Ctrl + Shift + End
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set CurRange = Sheets("QFilesToExportEMail").Range("A2:A" & LastRow) '''''sets the range to use
For Each CurCell In CurRange
''''checks each cell in range
If CurCell <> "" Then
'''''will only do something if the cell is not blank
CurSheetName = CurCell
If CheckSheet(CurSheetName) Then
Sheets(CurSheetName).Delete
End If
End If
Next
Sheets("QFilesToExportEMail").Delete
Sheets("QReportDates").Delete
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
Function CheckSheet(ByVal sCurSheetName As String) As Boolean
Dim oSheet As Excel.Worksheet
Dim bReturn As Boolean
For Each oSheet In ActiveWorkbook.Sheets
If oSheet.Name = sCurSheetName Then
bReturn = True
Exit For
End If
Next oSheet
CheckSheet = bReturn
End Function
Public Function MyColumnLetter(MyNumber As Long) As String
If MyNumber > 26 Then
MyColumnLetter = Chr(Int((MyNumber - 1) / 26) + 64) & Chr(((MyNumber - 1) Mod 26) + 65)
Else
MyColumnLetter = Chr(MyNumber + 64)
End If
End Function
Public Function xlLastCol(Optional WorkCurSheetName As String) As Long 'finds the last populated col in a worksheet
If WorkCurSheetName = vbNullString Then WorkCurSheetName = ActiveSheet.Name
With Worksheets(WorkCurSheetName)
On Error Resume Next
xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, xlWhole, xlByColumns, xlPrevious).Column
If Err <> 0 Then xlLastCol = 0
End With
End Function
Public Function QueryDB(ByVal connectionString As String, ByVal Target As Excel.Range, ByVal SQL As String)
Dim qt
As Excel.QueryTable
Dim ws
As Excel.Worksheet
Set ws = Target.Parent
Set qt = ws.QueryTables.Add(connectionString, Target, SQL)
qt.Refresh BackgroundQuery:=False
End Function
'=====================================================================
'The following function will left pad a string with a specified
'character. It accepts a base string which is to be left padded with
'characters, a character to be used as the pad character, and a
'length which specifies the total length of the padded result.
'=====================================================================
Function Lpad(MyValue$, MyPadCharacter$, MyPaddedLength%)
PadLength = MyPaddedLength - Len(MyValue)
Dim PadString As String
For x = 1 To PadLength
PadString = PadString & MyPadCharacter
Next
Lpad = PadString + MyValue
End Function
'=====================================================================
'The following function will right pad a string with a specified
'character. It accepts a base string which is to be right padded with
'characters, a character to be used as the pad character, and a
'length which specifies the total length of the padded result.
'=====================================================================
Function Rpad(MyValue$, MyPadCharacter$, MyPaddedLength%)
PadLength = MyPaddedLength - Len(MyValue)
Dim PadString As String
For x = 1 To PadLength
PadString = MyPadCharacter & PadString
Next
Rpad = MyValue + PadString
End Function
Public Function FormatBasics()
Dim x
As Long
Dim FormatColumnLetter, FormatColumnName As String
Application.EnableCancelKey = xlDisabled
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("A1:" & MyColumnLetter(xlLastCol) & "1").Select
Selection.Font.Bold = True
With Selection.Interior
.PatternColorIndex = 2
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Selection.AutoFilter
Rows("2:2").Select
ActiveWindow.FreezePanes = True
For x = 1 To xlLastCol
FormatColumnLetter = MyColumnLetter(x)
FormatColumnName = StrConv(Range(FormatColumnLetter & "1").Value, vbLowerCase)
Range(FormatColumnLetter & "1").Value = StrConv(Range(FormatColumnLetter & "1").Value, vbProperCase)
If FormatColumnName = "factypedesc" Then
Range(FormatColumnLetter & "1").Value = "FacTypeDesc"
ElseIf FormatColumnName = "facsubtypedesc" Then
Range(FormatColumnLetter & "1").Value = "FacSubTypeDesc"
ElseIf FormatColumnName = "facsubtype" Then
Range(FormatColumnLetter & "1").Value = "FacSubType"
ElseIf FormatColumnName = "mpinetworkcode" Then
Range(FormatColumnLetter & "1").Value = "Network"
ElseIf FormatColumnName = "mpicontractid" Then
Range(FormatColumnLetter & "1").Value = "ContractNumber"
ElseIf FormatColumnName = "dob" Or FormatColumnName = "dateofbirth" Then
Range(FormatColumnLetter & "1").Value = "DOB"
ElseIf FormatColumnName = "deanumber" Then
Range(FormatColumnLetter & "1").Value = "DEANumber"
End If
Next x
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:" & MyColumnLetter(xlLastCol)).Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes
Range("A2").Select
End FunctionStack Overflow用户发布于 2020-12-24 12:26:00为了防止其他人遇到困难，在我的例子中，在MS Access中，我执行了以下操作代码语言：javascript
Set ExcelApp2 = CreateObject("Excel.Application")
some code
ExcelApp2.Workbooks.Open CurPath & "MatchLoad.xlsm", True
ExcelApp2.Visible = True
ExcelApp2.Quit
some code
ExcelApp2.Workbooks.Open CurPath & "MatchLoad2.xlsm", True
ExcelApp2.Visible = True
ExcelApp2.Quit 正如您所看到的，我创建了一个实例，然后稍后重用该实例。由于某些原因，如果第一个宏位于内存中，这会阻止在启动时打开下一个宏。我不知道为什么，但似乎是这样的。因此，对于第二个xlsm文件，它将打开，但启动宏不会运行。我后来添加了代码语言：javascriptSet ExcelApp2 = Nothing 当然会得到一个错误，因为实例是关闭的，但我正在尝试打开它下面的另一个文件。现在我正在做以下事情代码语言：javascriptSet ExcelApp2 = CreateObject("Excel.Application")
ExcelApp2.Workbooks.Open CurPath & "MatchLoad.xlsm", True
ExcelApp2.Visible = True
ExcelApp2.Quit
Set ExcelApp2 = Nothing
some code
Set ExcelApp2 = CreateObject("Excel.Application")
ExcelApp2.Workbooks.Open CurPath & "MatchLoad2.xlsm", True
ExcelApp2.Visible = True
ExcelApp2.Quit
Set ExcelApp2 = Nothing 而且它是有效的。然而，还剩下一个谜题。即使有了代码语言：javascriptSet ExcelApp2 = Nothing 我仍然在内存中看到excel实例。你知道为什么会发生这种事吗？查看全部 1 条回答页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持原文链接：https://stackoverflow.com/questions/65433615复制