Adding an excel workbook into excel as an object and then reading the embedded Excel Sheet

Hey guys, I've been working on this all day and cant seem to find a lot of information online.
I'm working on a project where we have a worksheet that requires us to enter our estimates into the sheet. Im trying to create code to press a command button in excel that will allow you to browse your computer, find the file, insert it as an object and then read a specific range from the embedded workbook into the main worksheet. I've slowly built the following code, but I've hit a brick wall and I dont know how to correctly grab the information that i need.

Does anyone have any suggestions?



'Insert Total Estimate Workbook into Estimate Worksheet
Private Sub CommandButton1_Click()
Dim TotalEstimateFilePath As Variant, FileName As Variant, FileLink As Variant
Dim TotalEstimateFile As Object

Application.ScreenUpdating = False 'Speeds up program by preventing screen flickering

'FileName = "Total Estimate"

TotalEstimateFilePath = Application.GetOpenFilename(FileFilter:="Microsoft Excel files(*.xls),*.xls", Title:="Total Estimate Excel File")
If TotalEstimateFilePath = False Then
MsgBox ("No file chosen")
Exit Sub
End If

Worksheets("Estimate").Range("H1").Select 'To place inserted object in the correct place on a protected sheet
Set TotalEstimateFile = Worksheets("Estimate").OLEObjects.add(FileName:=TotalEstimateFilePath, Link:=False, DisplayAsIcon:=True)

'These were some old trials to name the inserted object that keep failing, this is a small side problem that i'm going to focus on later
'ClassType:="TotalEstimateFile", IconFileName:=FileName, IconLabel:=FileName, TotalEstimateFile.Name = "Total Estimate"

For i = 1 To 27
Worksheets("Estimate").Range("I11").Offset(i - 1, 0) = xlobject.TotalEstimateFile.ActiveSheet("Contracting Sales Calculator WS").Range("A11").Offset(i - 1, 0)
Next i


Application.ScreenUpdating = True 'restart updating screen
End Sub
Let me know if i need to clarify anything above, this is my first time posting on this website.

Why importing the excel file as an object?

Why not just import the sheet data? (if it's always the same reference book, and the same reference sheet, VBA shouldn't even be necessary at all)

Or better yet, why not just grabbing the needed data on the fly?

Please Login or Register  to view this content.

Additionally, you could extract the data directly, without even opening the origin workbook...

You can take a look about several ways of getting data from another workbook here: http://vba4all.com/various-ways-to-p...mulas-and-vba/

The purpose of actually attaching the document is because I need to have the workbook all locked down, except by editing the embedded sheet, which has a specific structure and needs to be submitted as well. My thought there is to keep a "Living" embedded file that anyone can add or subtract numbers from, without messing with the main data or cell linking. But, for booking purposes, we also need to show the individual documents when we finish submitting the file and that's the file which needs to be kept in the sheet itself. Eventually all the changes have to link up to a separate program that we use, but those changes can be kept track of fairly simply in that program itself and the excel file I'm embedding.

Unfortunately its complicated because of accountability and making sure the numbers all match up. Also,the document I'm pulling the embedded file into isn't a straight up 1:1 display of the data, rather it takes what I bring in and edits it for sales vs cost amounts. I just didn't include the simple VBA for simplicity sake.

All that is to say, I need an excel sheet that allows an excel document to be embedded and not linked or copied. That's just the way that we have to report our data and I'm running into issues with this very specific process.

Thank you very much for the link, I'm off to a meeting/dinner tonight but I'll check it tomorrow for sure!

Edit: Just looked at the code you posted, that will help me a TON when it comes to cleaning up the code tomorrow, thank you again!