Merging Data from One Excel Document to Another - NOOB

[rdreynolds]

I have been put in charge of a project that I am quite frankly too
dumb to figure out.

Basically, we have clients that send int an Excel spreadsheet that
lists shipping information for several locations. We want to pull
that data from one spreadsheet to another one which would be a packing
list. How would you go about doing this? It's almost like a mail
merge or something in Word...would that be the way to go? Is there a
way to automate this?

This noob thanks you in advance!

R~

 

[JE McGimpsey]

I have been put in charge of a project that I am quite frankly too
dumb to figure out.

Basically, we have clients that send int an Excel spreadsheet that
lists shipping information for several locations. We want to pull
that data from one spreadsheet to another one which would be a packing
list. How would you go about doing this? It's almost like a mail
merge or something in Word...would that be the way to go? Is there a
way to automate this?

You really haven't given enough information to make an informed guess.

One way would be to copy and paste.

Another would be to use a macro or AppleScript to transfer information
from one sheet to another.

There are a number of other ways, depending on your situation. Perhaps
if you give some more detail it would be possible to make a suggestion.

What version of MacXL are you using?

 

[Jim Gordon MVP]

Hi,

What you want to do can be easily accomplished using a query.

Windows Excel has an ODBC driver that lets you do this. For the moment, Mac
Excel does not. I'm hoping this situation will change.

Be patient and keep your eyes on this newsgroup every once in a while for
more information.

Thanks.

-Jim Gordon
Mac MVP

I have been put in charge of a project that I am quite frankly too
dumb to figure out.

Basically, we have clients that send int an Excel spreadsheet that
lists shipping information for several locations. We want to pull
that data from one spreadsheet to another one which would be a packing
list. How would you go about doing this? It's almost like a mail
merge or something in Word...would that be the way to go? Is there a
way to automate this?

This noob thanks you in advance!

R~

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[rdreynolds]

You really haven't given enough information to make an informed guess.

One way would be to copy and paste.

That is what we currently do. However, we are talking a lots of data
over and over from different sheets.

Another would be to use a macro or AppleScript to transfer information
from one sheet to another.

There are a number of other ways, depending on your situation. Perhaps
if you give some more detail it would be possible to make a suggestion.

Basically, let's say document a has a listing of items that we are
supposed to ship to a store. So we have:

Store - Item 1 - Item 2 - Item 3 - Item 4
Philadelphia - 5 - 0 - 0 - 4
Atlanta - 0 - 3 - 2 - 1
Guam - 2 - 0 - 1 - 0

With the numbers being how many of each item gets shipped to each
store. What we would want is to pull that information and make a
packing slip for each store. That's what I am trying to accomplish in
an automated manner.

What version of MacXL are you using?

Running either X or 2004. I can run either. I can run it in Windows
as well if that would work better. Just need a solution.

R~

 

[gimme_this_gimme_that]

If you want it fast and sweet ... Look for a tool that would let you
upload the Workbook/Worksheet as is and imports the data into a table
in a database. AQT for Windows (cough!) would do it.

Then use SQL to join the tables.

---

Other approaches (Mac)

1. You could save the file as CSV and read the CSV file from Perl.

From Perl you could also insert the data into a database.

2. You could write a VBA macro that writes the data to disk and have
your customer put the his data into an Excel Template that contains
the VBA macro. This approach would allow you to write the content of
the Worksheet in any format you wanted - say in XML with your own DTD.

3. Enhance step 1 by writing an Applescript that checks if Excel is
open, if it is it opens the Workbook and saves it in CSV format - then
it reads the CSV.

4. Same as step 1 but read the file in Java instead of Perl.

5. Use Perl's Mac::Glue (or MacPerl::Applescript) modules and see if
you can get to the point to where you can read an Excel Workbook. This
would be the most tedious, but perhaps the must intellectually
satisfying solution. With this approach you wouldn't have to save the
XL file in CSV format.


Other approaches (Windows)

1. Export the data to a database (The export tool will create the
table and the columns for you - but the definitions it selects for you
will suck.)

2. Use VBA to read the data and store in a database via DAO. (Add the
VBA to the Template from your customer)

 

[Jim Gordon MVP]

Another suggestion...

Just wait a couple weeks for something new.

-Jim Gordon
Mac MVP


Quoting from "(e-mail address removed)"

If you want it fast and sweet ... Look for a tool that would let you
upload the Workbook/Worksheet as is and imports the data into a table
in a database. AQT for Windows (cough!) would do it.

Then use SQL to join the tables.

---

Other approaches (Mac)

1. You could save the file as CSV and read the CSV file from Perl.

2. You could write a VBA macro that writes the data to disk and have
your customer put the his data into an Excel Template that contains
the VBA macro. This approach would allow you to write the content of
the Worksheet in any format you wanted - say in XML with your own DTD.

3. Enhance step 1 by writing an Applescript that checks if Excel is
open, if it is it opens the Workbook and saves it in CSV format - then
it reads the CSV.

4. Same as step 1 but read the file in Java instead of Perl.

5. Use Perl's Mac::Glue (or MacPerl::Applescript) modules and see if
you can get to the point to where you can read an Excel Workbook. This
would be the most tedious, but perhaps the must intellectually
satisfying solution. With this approach you wouldn't have to save the
XL file in CSV format.


Other approaches (Windows)

1. Export the data to a database (The export tool will create the
table and the columns for you - but the definitions it selects for you
will suck.)

2. Use VBA to read the data and store in a database via DAO. (Add the
VBA to the Template from your customer)

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Jim Gordon MVP]

Hi R,

I'm sorry that I had to put you off, but I was not able to disclose what I
knew about Actual Technology's fantastic plan to make an Excel ODBC driver
for Mac Office.

Effective today you can now do SQL SELECT queries against any XLS workbook
that's arranged with the data in standard database format.

The data source worksheets need to be arranged in rows and columns such that
the first row (and only the first row) contains the header (field)
information, and each subsequent row is a record in the database.

See the announcement I made today in this newsgroup for details.

Thanks.

-Jim Gordon
Mac MVP

I have been put in charge of a project that I am quite frankly too
dumb to figure out.

Basically, we have clients that send int an Excel spreadsheet that
lists shipping information for several locations. We want to pull
that data from one spreadsheet to another one which would be a packing
list. How would you go about doing this? It's almost like a mail
merge or something in Word...would that be the way to go? Is there a
way to automate this?

This noob thanks you in advance!

R~

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info