Excel VBA: Import worksheets from workbooks into another workbook while performing operations

Question

Goal: I want to import a worksheet from workbook1 to workbook3, but before that I want to make an addition to the importing sheet from workbook1. The addition is equal to one colum with calculations done from a third workbook - workbook2.

Set-up: I would want an button in workbook3 that prompt for file 1 - workbook1 and file 2 - workbook2, then it appends the calculation data from workbook 2 to a colum in workbook 1 before importing that entire sheet two workbook3 with the filename of the workbook or the worksheets name in workbook1.

Why? It's a purchasing thing, I got a workbook - workbook1 with delivery accuracy percentage for a set of rows (suppliers). But I don't know just from workbook1 if this percentage is based on one PO-line (purchase orderline) or 100 (the latter which indicate more 'reliable' percentage). This data is found in workbook 2. Where each row is a PO-line and there is a colum indicating which supplier it is - SupplierID.

Is this even possible to peform calculation in some kind of 'temp'-mode? I was thinking to have an operation like two array lists with the same length, one would contain (create) the supplier ID and the other the corresponding nbr of PO-lines for that supplier. I would in my for-loop for the arrays in woorkbook2 just append +1 (PO-line) to array2 whenever the loop encountered a row with the same supplier again.

When the arrays would be done, I would just do a row-loop in workbook1 to writhe out the summarized nbr of PO-lines (one row for each supplier). When I would have this one sheet with all information. It would just be imported to workbook3 with the name of either workbook1 or the sheet in workbook1.

Is it possible and how? Or do you have any other suggestion based on what I want to achive given that the data is contained in two different workbooks, and that I need workbook3 where I compile this kind of data for each month (so there is already history in this file - workbook3). Of course I want minimal manual steps when trying to achive this.

Thanks!

To almost all questions asking "is this possible" the answer is typically "Yes". However, as it stands this is not a good question for SO: to improve it you need to make it more specific. Describe exactly what is preventing you from doing what you want to do. Which part is giving you problems, what did you try, and what errors (etc) did you run into ? — Tim Williams
Hi Tim! I haven't tried anything as of now, becuase I thought it wouldn't be possible to use commands in workbooks that weren't 'open', as I bealive you would seem to imply is possible, is it? I'm a novich, but I know I can specify Sheets("xxx").Cells(... etc (perform operations in a sheet that isen't active), are you saying I could add to this; the particular file name before .Sheets("xxx")? If so, that I can read and writhe to non-active workbooks, how do use prompt for file's and store their names as Strings? — Christian
You can operate on workbook which are not active, but your options for workbooks which aren't open are much more limited. You didn't mention in your question that you wanted to work with closed workbooks. — Tim Williams
Sry, I guess in my mind those files (workbooks) weren’t open, except the one that will prompt for those files (workbook 3). And by ‘temp’ I somewhat meant performing operations and calculations in not open (active) workbooks. Again, sorry for the confusion.. However based this ‘new’ information, is it still possible to do the kind of operations that I described (that are possible in open – active workbooks) in not active workbooks? Or do you have any other suggestions and how I could achieve what I’m trying to do? In my world a workbooks is a file, a sheet a sheet within a workbook – correct? — Christian
All you can do with a closed workbook is read values (and maybe if you use ADO you could write to one) but you cannot perform a calculation in a closed workbook. Is it important that these workbooks remain closed, or could you open them to do what you want? — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2014-08-04T05:49:45

Something like:

Sub Tester()

Dim wb1 As Workbook, wb2 As Workbook
Dim v1, v2

    Set wb1 = PromptForWorkbook("Select first input file")
    If wb1 Is Nothing Then Exit Sub

    Set wb2 = PromptForWorkbook("Select second input file")
    If wb2 Is Nothing Then Exit Sub

    v1 = wb1.Sheets("Sheet1").Range("A10").Value
    v2 = wb2.Sheets("Sheet2").Range("C3").Value

    'etc etc

    wb1.Close False 'don't save changes
    wb2.Close False 'don't save changes

End Sub

Function PromptForWorkbook(sMsg As String) As Workbook
    Dim f, wb As Workbook
    f = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", _
                                    Title:=sMsg, MultiSelect:=False)
    If f <> False Then
        Set wb = Workbooks.Open(f)
    End If
    Set PromptForWorkbook = wb
End Function

Excel VBA: Import worksheets from workbooks into another workbook while performing operations

1 Answers