Re: Formatting XML in Excel?

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 work*** 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/excel/HA011019641033.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 spead*** 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?


.

Quantcast