How to reference closed worksheet data in external workbooks in VBA

[lockjaw4829]

I'm coding an application where I have to reference certain cells in four closed worksheets each in an external workbook. I'm having a problem with the method I should use to do this. All the workbooks exist on the same server and the cells I need to reference are in the exact same locations on each worksheet.

I know this is probably a newbie question, but I'm new to both VB and VBA althouth I've been a programmer on IBM midrange systems for many years.

Any help would be appreciated/

 

[BaldyMcFatFat]

This actually doesn't need VBA at all. If I am understanding this correctly, you are trying to pull data from a complete seperate workbook. If that is so, you would just need to use a LookUp formula and specify the location of the worksheet you are attempting to access. I don't want to give confusing information so I would suggest just looking through the excel Help feature in regards to how to thoroughly utilize the LookUp functions. If I am completely of base, then I am sorry and hopefully someone else can assist you.

Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[lockjaw4829]

Thanks for the response. Does it make any difference that there are four worksheets each in a different workbook?
The output I'm trying to generate is an EOM report. My big problem is my background in procedural programming makes it easier to code in VBA since I don't really have the time to learn excel, although if you feel I can do this job easier with an excel lookup function, I guess I'll just have to puzzle it out.

 

[BaldyMcFatFat]

As long as you are moving data cell-forcell (meaning not trying to merge two cells into one) then you will be fine. the LookUp functions are extremely useful when it comes to grabbing data from one workbook to another. At work, I use the function to pull info from four pages all in different workbooks to create one complicated spreadsheet in a completely seperate workbook. The only thing you should look out for is when you change the information in one of the contributing workbooks, the information will update automatically(depending on your settings) unless you convert the formulas to values. I don't presume to be an Excel Expert but if you need other clarification, let me know.

Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[lockjaw4829]

I have to add cells from each sheet in the different workbooks placing the totals in cells of my EOM worksheet.Then I can easily sum the cells on the EOM sheet to give the catagory and YTD totals I need.

Maybe an example will clarify the situation. Each of the worksheets I'm grabbing cells from represent a sales rep's totals for a certain class of transaction for a given month. I have to sum each of these monthly cells for each rep and display them on my EOM worksheet as a monthly total for that transaction class. Then these are going to be summed for each month to give a YTD total for each catagory and each summed to give a total transaction count for each month.

The work on the EOM spreadsheet is a piece of cake if I can only get the data combined from each of the other sheets.

I was trying to use VBA to do this like I would do it in my native RPG language, using the cells on the EOM spreadsheet as a 2 dimensional array (Catagoies by Months). Is it easier to use a lookup formula, or some other formula, on my EOM sheet. Wouldn't I have to use a summation formula for each cell per transaction type per month? I just thought this would be easier to do as a couple of nested Do loops to grab the data and sum it into the cells on my EOM sheet.

I hope this clarifies the situation so you'll know if you're pointing me in the right direction.

 

[BaldyMcFatFat]

You can do it either way. I'm just saying you don't need VBA. However, if you are more familiar with it, you could use some Do Loops with the LookUp included in the coding as a worksheet function. I was just saying it would probably easier to do the formula. Definitely less time consuming to set up. Now if you are going to have to be doing this so much you feel a macro would be more efficient, that is up to you. Hope this clarifies some. On tips on how to include the worksheet.function into your code, I would suggest getting the information from one of the other people on the forum do to I don't have much experience with that particular item. I am new to the whole VBA thing but have been working with the formulas and excel for way too long (according to my wife at least). Once again, I hope this is what you were looking for. If not then I deeply appologize for wasting your time.

Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[lockjaw4829]

Thanks for the input. Anything I learn I never consider to be a wast of time. I just have to figure out the proper code structure to use to access the worksheets I need. I've got the whole VBA subroutine written its just I just can't get connected to the workbooks I need to pull the data from. I tried using the path, workbook, worksheet name to point to the cells using the "cells.value" property but I can't seem to find the proper way to code this. I tried the method used in cell formulas, but the examples I used had single quote marks in them and VBA treats the entire rest of the line as a comment after the first single quote.

I know there's got to be a way to do this in VBA and I hate to chuck the work I've done and start over.

Once again thanks for you assistance and I will tinker with some of the ideas you provided. In the mean time, I'll leave this thread open in case someone who is a VBA wizard stumbles across it and provides the solution.

 

[BaldyMcFatFat]

I'm interested in what else will come up, because that might be able to help me in a couple reports I run for work. If anyone else has any suggestions, please provide.

Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[SkipVought]

If your data is tabluar in your 4 workbooks...

faq68-5829

I query data in other workbooks OR in the activeworkbook regularly.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]