Tricky Excel problem...

MrNightly

Anybody here a guru at excel? (And by guru, I mean guru... I'm fairly good at basic code and problems, but this one has me stumped.)

Riddle me this:

I want to be able to enter data (A 5-digit job number, 80227 for example) into a cell, and this would result in multiple other cells to auto fill with data from an outside worksheet. The worksheets are in a separate folder on my server and named under the same name as the project. (So if I entered 80227 as the data in A1, I would want A2, A3 and A4 to open spreadsheet 80227.xls on the server, locate the referenced cell/s, and insert the info from 80227.xls into them.)

I can easily reference the cells alone and have them input the data, but this is extremely time consuming since this spreadsheet has all the current jobs our company is working on. I currently am running a macro/VBA that automatically updates all linked spreadsheets without them having to be open, so that is not an issue.

Is what I am seeking to do possible with Excel? (Or do you know how to do it?)

Any help would be appreciated.

Thanks!

audiobliss

Dang...that's deep! :eek:

Systems

MrNightly wrote: »

Anybody here a guru at excel? (And by guru, I mean guru... I'm fairly good at basic code and problems, but this one has me stumped.)

Riddle me this:

I want to be able to enter data (A 5-digit job number, 80227 for example) into a cell, and this would result in multiple other cells to auto fill with data from an outside worksheet. The worksheets are in a separate folder on my server and named under the same name as the project. (So if I entered 80227 as the data in A1, I would want A2, A3 and A4 to open spreadsheet 80227.xls on the server, locate the referenced cell/s, and insert the info from 80227.xls into them.)

I can easily reference the cells alone and have them input the data, but this is extremely time consuming since this spreadsheet has all the current jobs our company is working on. I currently am running a macro/VBA that automatically updates all linked spreadsheets without them having to be open, so that is not an issue.

Is what I am seeking to do possible with Excel? (Or do you know how to do it?)

Any help would be appreciated.

Thanks!

I would think the only way you could do this is if excel supported hyperlinks in the formulas.....

tcrossma

I'm not an Excel guru, but can't you use VBA to open the file, get the range contents, and insert it into your spreadsheet? I'm sure you've tried that, but what obstacles did you run into?

GEBBY44

Like the last post said, I don't know if that's possible. However, I didn't listen as well in my OPIM classes as well as some people I know. I'll send it around to some people I know that are currently OPIM majors at my school, a few of which are insane with this stuff, and see if they know. I think one guy I know should know the answer, if it's even possible. And he'd better considering he's top of his class and going to work for $120,000 at Goldman next year. Little ****:(

tcrossma

You should be able to respond to a cell change event and use VBA to open a workbook file, read a range of cells, and set those cells into the current workbook.

In VBA you should be able to do something to the effect of:

set wb = workbookbook.open (filename)
set ws = wb(0).worksheet(0)
me.range(range) = ws(range)

Simplistic example, but something to that effect should work. Have you tried something like this and if so what road blocks are you hitting?

mrbigbluelight

I know, but I'm not telling.

MrNightly

GEBBY44 wrote: »

Like the last post said, I don't know if that's possible. However, I didn't listen as well in my OPIM classes as well as some people I know. I'll send it around to some people I know that are currently OPIM majors at my school, a few of which are insane with this stuff, and see if they know. I think one guy I know should know the answer, if it's even possible. And he'd better considering he's top of his class and going to work for $120,000 at Goldman next year. Little ****:(

I'd appreciate that muchly!

I bought Excel 2007 Power Programming with VBA but considering it's 1100 pages long I haven't gotten through it all yet

I can pull everything in just by linking to the separate spreadsheets via a path, but it's cumbersome. I'm not VBA genius, but I thought maybe some out there were.

MrNightly

tcrossma wrote: »

You should be able to respond to a cell change event and use VBA to open a workbook file, read a range of cells, and set those cells into the current workbook.

In VBA you should be able to do something to the effect of:

set wb = workbookbook.open (filename)
set ws = wb(0).worksheet(0)
me.range(range) = ws(range)

Simplistic example, but something to that effect should work. Have you tried something like this and if so what road blocks are you hitting?

No I haven't tried anything like this yet... I am not the guru at programming VBA yet Hence asking a bunch of audio geeks what their thoughts are on it. Ha. (Kidding)

But seriously, most of the VBA programming I've done to this workbook is copy and paste from the pro's with minor tweeking from myself. I really need to get my learn on for this power programming stuff.

The only problem I see with your example above, IMO, is I will have well over 50 external worksheets being referenced in the master file. The external worksheets are the actually worksheets for each project with sales totals, margins, profit, costs etc etc... and I need their summary page to all be linked into my profit worksheet which reflects the entire jobs at a glance for the sales executives. Did any of that make sense?

I'll keep trying stuff... and reading. The answer is out there somewhere.

jdhdiggs

It is possible and I do have some examples but their all back on the main network at the office. When I get back, I'll send you some sample code that did a similar thing.

MrNightly

jdhdiggs wrote: »

It is possible and I do have some examples but their all back on the main network at the office. When I get back, I'll send you some sample code that did a similar thing.

Most Excellent!!! PM on the way so you can send it to my blackberry!

jdhdiggs

Yeah, but I won't be back in the office until Tuesday. Hit me with the PM then....

The code I'm thinking of would read all excel files from a directory and it's subs, open each one in turn and import a selected range into Access (Although into excel would be easier). This script should only be maybe 15 lines (I think....)