Importing Data from a pre-existing spreadsheet

[Todd Helmick]

I would like to be able to create an .xls document on my computer that
does 1 of 2 things (both would achieve the same result, i just dont
know which one would be better, or how to do either)

GOAL: get data from a spreadsheet (that is saved on the company
server) and have it populate in a combo box (on a userform) on the xls
document that is saved on my computer.

method 1) create a macro that opens up the external xls data, copies
it to the local spreadsheet and then closes the external spreadsheet.

method 2) create some sort of a lookup function that dynamically pulls
from that external xls document without the need to do a crazy import
macro.

That "external xls document" is a file that gets changed automatically
(daily) from an outside company and they just dump the xls file on our
server for our reference. this xls document contains about 15 columns
and anywhere from 150 to 300 rows on any given day.

I want to be able to have the combo box in my userform (on the xls
file saved on my computer) dynamically display the contents of column
A from the xls file located on the server.

Is this possible with out too much brain damage??

 

[Dave Peterson]

I think I would use the crazy option #1. It seems easier and more robust.

The first thing I'd do is open the receiving file
Turn on the macro recorder
Open the sending file
copy and past the data
close the sending file
Stop the macro recorder

Then your userform will have the data it needs to populate the controls you
have.

You could assign this macro to a button from the Forms Toolbar so that you could
run it on demand -- or even rename the macro to Auto_Open and have it run each
time you open your receiving workbook.

(If the receiving file is opened multiple times per day, then the auto_open may
be redundant.)

 

[Dave Peterson]

ps. When you open that sending file, open it in readonly mode. Then you won't
have to worry about the file being in use by others.