If I had a speadsheet that had the labels Order Data, Customer Data, and
Salesman's data in Column A with the associated data in column B as the
one side of an order and in rows C-F I had the associated order details;
Description, qty, cost, total...can such a format be done using an XML file?

Ex:
ColA ColB Desc Cost Qty Tot
Order# 15 Blue ribbon .15 10 $1.50
OrdDate 7/11/2009 Green ribbon .20 5 $1.00
Customer Joe Blow Red ribbon .10 30 $3.00
Address 123 A St
Salesman Henry Smith
Phone 555-123-4567

Order# 16 White ribbon .11 10 $1.10
OrdDate 7/11/2009 Green ribbon .20 8 $1.60
Customer Sally Sue
Address 456 B Ave
Salesman Dave Jones
Phone 555-444-3333

Order# 17 Tan ribbon .40 10 $4.00
OrdDate 7/12/2009
Customer Sally Sue
Address 543 C Dr
Salesman Amy Marks
Phone 555-333-2222

I can't figure out how to do multiple records. Is it possible to do so
in Excel?

Do you mean XML or XLSM. They are completely different type files. Can you
explain exactly what you want. It can be done but need more details.

"Salad" wrote:

If I had a speadsheet that had the labels Order Data, Customer Data, and
Salesman's data in Column A with the associated data in column B as the
one side of an order and in rows C-F I had the associated order details;
Description, qty, cost, total...can such a format be done using an XML file?

Ex:
ColA ColB Desc Cost Qty Tot
Order# 15 Blue ribbon .15 10 $1.50
OrdDate 7/11/2009 Green ribbon .20 5 $1.00
Customer Joe Blow Red ribbon .10 30 $3.00
Address 123 A St
Salesman Henry Smith
Phone 555-123-4567

Order# 16 White ribbon .11 10 $1.10
OrdDate 7/11/2009 Green ribbon .20 8 $1.60
Customer Sally Sue
Address 456 B Ave
Salesman Dave Jones
Phone 555-444-3333

Order# 17 Tan ribbon .40 10 $4.00
OrdDate 7/12/2009
Customer Sally Sue
Address 543 C Dr
Salesman Amy Marks
Phone 555-333-2222

I can't figure out how to do multiple records. Is it possible to do so
in Excel?

Joel wrote:

Do you mean XML or XLSM. They are completely different type files. Can you
explain exactly what you want. It can be done but need more details.

In Access I can export to an XML file. Using an order as an example; on
the one side I might have order data, customer data, and sales data. On
the many side, the items ordered for each specific order.

I was looking at
http://office.microsoft.com/en-us/ex...019641033.aspx. At the
bottom of the page you can dl a sample file and run a little tutorial.
It does utilize a one-to-many example...but only one employee record.
What if one had two or three or more employees?

I really don't know the difference between XML and XLSM. There's not as
much data on using Access and XML on the web (and at B&N) unless its
superficial data one is looking for.

There is a report in an Access system at my work that writes to an Excel
file like the example below. Selecting the records is quick (I've timed
them) but writing to the Excel file takes a long time. The reason? The
developer that wrote the report writes the data cell by cell due to the
format (the "one" side is vertically written, the "many" side typical
Excel style of 1 record per row). I thought that perhaps sending the
output to XML and then importing the file would be quicker.

But my ability to format it doesn't work like I want it to.



"Salad" wrote:


If I had a speadsheet that had the labels Order Data, Customer Data, and
Salesman's data in Column A with the associated data in column B as the
one side of an order and in rows C-F I had the associated order details;
Description, qty, cost, total...can such a format be done using an XML file?

Ex:
ColA ColB Desc Cost Qty Tot
Order# 15 Blue ribbon .15 10 $1.50
OrdDate 7/11/2009 Green ribbon .20 5 $1.00
Customer Joe Blow Red ribbon .10 30 $3.00
Address 123 A St
Salesman Henry Smith
Phone 555-123-4567

Order# 16 White ribbon .11 10 $1.10
OrdDate 7/11/2009 Green ribbon .20 8 $1.60
Customer Sally Sue
Address 456 B Ave
Salesman Dave Jones
Phone 555-444-3333

Order# 17 Tan ribbon .40 10 $4.00
OrdDate 7/12/2009
Customer Sally Sue
Address 543 C Dr
Salesman Amy Marks
Phone 555-333-2222

I can't figure out how to do multiple records. Is it possible to do so
in Excel?

The original excel workbook extension is xls. Microsoft added an new
extension in 2007 whichis xlsm which is a workbook with macros. A older xls
workbook in 2007 needs to save the workbook as xlsm before the macro(s) will
work. A xls in 2007 has macros disabled. The xls and xlsm are binary files
which is in a custom format that microsoft developed

A XML (extended meta languge file) file is a webpage file which is a text
file. It is very similar to HTML (Hyper-Text meta Languge).

XLS and XLSM can only be viewed using excel. To post a workbook on the web
so people without microsoft office can read the file (not modify) it is
common to save the workbook as either XML or HTML and then post the file on
webpage.

Saving the file as CSV would be the quickest was to save the data. The
speed problem may be the database is large and searching the database for
each individual item may be slow and not the exporting. Another choice is
the share option in access which automatically converts the data to excel may
be quicker. Or creating a custom table in access and then exporting the the
table to excel using the share option. Finlly exporting the entire table
from access and then performing a filter in excel may be quicker.

"Salad" wrote:

Joel wrote:

Do you mean XML or XLSM. They are completely different type files. Can you
explain exactly what you want. It can be done but need more details.

In Access I can export to an XML file. Using an order as an example; on
the one side I might have order data, customer data, and sales data. On
the many side, the items ordered for each specific order.

I was looking at
http://office.microsoft.com/en-us/ex...019641033.aspx. At the
bottom of the page you can dl a sample file and run a little tutorial.
It does utilize a one-to-many example...but only one employee record.
What if one had two or three or more employees?

I really don't know the difference between XML and XLSM. There's not as
much data on using Access and XML on the web (and at B&N) unless its
superficial data one is looking for.

There is a report in an Access system at my work that writes to an Excel
file like the example below. Selecting the records is quick (I've timed
them) but writing to the Excel file takes a long time. The reason? The
developer that wrote the report writes the data cell by cell due to the
format (the "one" side is vertically written, the "many" side typical
Excel style of 1 record per row). I thought that perhaps sending the
output to XML and then importing the file would be quicker.

But my ability to format it doesn't work like I want it to.



"Salad" wrote:


If I had a speadsheet that had the labels Order Data, Customer Data, and
Salesman's data in Column A with the associated data in column B as the
one side of an order and in rows C-F I had the associated order details;
Description, qty, cost, total...can such a format be done using an XML file?

Ex:
ColA ColB Desc Cost Qty Tot
Order# 15 Blue ribbon .15 10 $1.50
OrdDate 7/11/2009 Green ribbon .20 5 $1.00
Customer Joe Blow Red ribbon .10 30 $3.00
Address 123 A St
Salesman Henry Smith
Phone 555-123-4567

Order# 16 White ribbon .11 10 $1.10
OrdDate 7/11/2009 Green ribbon .20 8 $1.60
Customer Sally Sue
Address 456 B Ave
Salesman Dave Jones
Phone 555-444-3333

Order# 17 Tan ribbon .40 10 $4.00
OrdDate 7/12/2009
Customer Sally Sue
Address 543 C Dr
Salesman Amy Marks
Phone 555-333-2222

I can't figure out how to do multiple records. Is it possible to do so
in Excel?

Joel wrote:
The original excel workbook extension is xls. Microsoft added an new
extension in 2007 whichis xlsm which is a workbook with macros. A older xls
workbook in 2007 needs to save the workbook as xlsm before the macro(s) will
work. A xls in 2007 has macros disabled. The xls and xlsm are binary files
which is in a custom format that microsoft developed

A XML (extended meta languge file) file is a webpage file which is a text
file. It is very similar to HTML (Hyper-Text meta Languge).

XLS and XLSM can only be viewed using excel. To post a workbook on the web
so people without microsoft office can read the file (not modify) it is
common to save the workbook as either XML or HTML and then post the file on
webpage.

Saving the file as CSV would be the quickest was to save the data. The
speed problem may be the database is large and searching the database for
each individual item may be slow and not the exporting. Another choice is
the share option in access which automatically converts the data to excel may
be quicker. Or creating a custom table in access and then exporting the the
table to excel using the share option. Finlly exporting the entire table
from access and then performing a filter in excel may be quicker.

Yes, I am looking for a faster method. Currently, it starts at row B2
(Col A has the header stuff for the one side) and fills in the one side
from B2 to B12. It then writes, for that ID, all detail records from C2
to however many records there are (1 to 11 details). It then skips to
the next record to B14 and repeats. It creates a new worksheet for each
day. So if the date range was 1st of month to 15th, there'd be 15
worksheets.

Due to the formatting (similar to the sample I posted), the original
developer wrote the data to the Excel file cell by cell.

I don't know how many records there are per day. I don't know how many
detail records there are per order. The company does like the format.

If I could use a method of "sucking up the data" from a file that could
be faster than writing cell by cell I'd be happy. Like the CSV. But
getting it formatted like my example is my issue/problem.


"Salad" wrote:


Joel wrote:


Do you mean XML or XLSM. They are completely different type files. Can you
explain exactly what you want. It can be done but need more details.

In Access I can export to an XML file. Using an order as an example; on
the one side I might have order data, customer data, and sales data. On
the many side, the items ordered for each specific order.

I was looking at
http://office.microsoft.com/en-us/ex...019641033.aspx. At the
bottom of the page you can dl a sample file and run a little tutorial.
It does utilize a one-to-many example...but only one employee record.
What if one had two or three or more employees?

I really don't know the difference between XML and XLSM. There's not as
much data on using Access and XML on the web (and at B&N) unless its
superficial data one is looking for.

There is a report in an Access system at my work that writes to an Excel
file like the example below. Selecting the records is quick (I've timed
them) but writing to the Excel file takes a long time. The reason? The
developer that wrote the report writes the data cell by cell due to the
format (the "one" side is vertically written, the "many" side typical
Excel style of 1 record per row). I thought that perhaps sending the
output to XML and then importing the file would be quicker.

But my ability to format it doesn't work like I want it to.



"Salad" wrote:



If I had a speadsheet that had the labels Order Data, Customer Data, and
Salesman's data in Column A with the associated data in column B as the
one side of an order and in rows C-F I had the associated order details;
Description, qty, cost, total...can such a format be done using an XML file?

Ex:
ColA ColB Desc Cost Qty Tot
Order# 15 Blue ribbon .15 10 $1.50
OrdDate 7/11/2009 Green ribbon .20 5 $1.00
Customer Joe Blow Red ribbon .10 30 $3.00
Address 123 A St
Salesman Henry Smith
Phone 555-123-4567

Order# 16 White ribbon .11 10 $1.10
OrdDate 7/11/2009 Green ribbon .20 8 $1.60
Customer Sally Sue
Address 456 B Ave
Salesman Dave Jones
Phone 555-444-3333

Order# 17 Tan ribbon .40 10 $4.00
OrdDate 7/12/2009
Customer Sally Sue
Address 543 C Dr
Salesman Amy Marks
Phone 555-333-2222

I can't figure out how to do multiple records. Is it possible to do so
in Excel?

Joel wrote:

The original excel workbook extension is xls. Microsoft added an new
extension in 2007 whichis xlsm which is a workbook with macros. A older xls
workbook in 2007 needs to save the workbook as xlsm before the macro(s) will
work. A xls in 2007 has macros disabled. The xls and xlsm are binary files
which is in a custom format that microsoft developed

Thanks for the explanation. We're using Office 2003 and in the process
of discussing upgrading to 2007. It will occur, but when I can't say.
For all I know, maybe after another version of Office is released...or
maybe next month.