Excel宏:在后台打开工作表并在后台工作表中执行分析 [英] Excel Macro: Open Sheet in Background and Execute Analysis in Background Sheet

问题描述

我对Excel的真正功能-Macros/VBA相对较新，并且受命为一百万美元的项目建立财务模型.我能够设置一切并使它运行平稳，但是涉及的手动输入过多.我想通过VBA的功能来简化.

I am relatively new to the true power in Excel - Macros/VBA and have been tasked to set up the financial model for a million dollar project. I am able to set everything up and have it run smoothly, but there's too much manual input involved. I would like to seek simplification through the power of VBA.

这是我的难题:

1. 我需要能够单独双击一组特定的单元格(连续)，这将打开一个文件窗口，允许我选择一个EXL文件.

1. I need to be able to individually double-click on a specific set of cells (in a Row), which will open up a file window that allows me to select a EXL file.

选择文件后，最好以临时状态打开该文件(不可见，但是我可以运行函数并从中提取信息).

Once I select the file, that file should be preferably opened in a temp status (not visible, but I can run functions and pull info from it).

然后我将需要宏进入打开的工作表，执行简单的SUMIFS函数，并将结果记录在我正在处理的当前工作表的列中.

I will then need the macro to go into that opened sheet, conduct a simple SUMIFS function, and record the outcome in a column of the current sheet I'm working from.

我一直在Lynda上做一些Excel教程，希望在那里找到答案，但是我认为此请求的复杂性需要真正的大师的知识.

I've been doing some Excel tutorial on Lynda in hope to seek the answers there, but I think the complexity of this request demands the knowledge of a true master.

任何帮助将不胜感激！我想对于那些寻求它的人来说，这可能是一个不错的小挑战:)

Any help will be greatly appreciated! I would imagine this could be a nice little challenge for those who seek it :)

真心的感谢，

推荐答案

尝试一下:

Dim appExcel        As Excel.Application
Set appExcel = CreateObject("EXCEL.Application")

Dim wkbk            As Excel.Workbook
Set wkbk = appExcel.Workbooks.Open(sPathSrc, , False, , , sPassword)

' do EXCEL work here

If Not wkbk Is Nothing Then wkbk.Close True
Set wkbk = Nothing

If Not appExcel Is Nothing Then
    appExcel.DisplayAlerts = False
    appExcel.Quit
End If
Set appExcel = Nothing

此代码来自ACCESS应用程序，该应用程序调用EXCEL来打印一些报告.我发现有必要在周围散布一些DOEVENTS调用，但这只是前一阵子.一些细节此刻逃避了我.

This code is from an ACCESS application that invoked EXCEL to print some reports. I found it necessary to sprinkle some DOEVENTS calls around, but it was all a while ago; some details escape me at this moment.

这篇关于Excel宏:在后台打开工作表并在后台工作表中执行分析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！