Excel Macro: Open Sheet in Background and Execute Analysis in Background Sheet

Question

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.

This is my dilemma:

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.

2. 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).

3. 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.

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 :)

Sincere thanks,

Answer 1

Try this:

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

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.