Export Data to Sheets in Excel
Simon
I build a series of flat lines and write these sequentially to a file
and have no problem doing this to a csv file.
How can a nominate the 'sheet' to use ?
How can I insert more sheets ?
How can I name the sheets ?
John Vinciguerra
sources
for help if you are not familiar with VBA. Microsoft also has some excellent
OOP & VBA
intro docs on their web site.
(you need to install VBA help explicitly, even if you did a full install of
Office)
> I want to write a prog which writes to nominated 'sheets' in excel.
Is this a pre-existing spreadsheet or will a new spreadsheet be created on
the fly?
> I build a series of flat lines and write these sequentially to a file
> and have no problem doing this to a csv file.
You can write out a control record at the same time in the style of a
Windows
INI file which you can use to control VBA dynamically. That way you don't
need
to 'hard code' sheet names etc into the script. Just open the file and
extract the
appropriate parameters. e.g. SheetName="MyNewExcelSheet"
>> How can a nominate the 'sheet' to use ?
Sheets("SheetName").Activate
>> How can I insert more sheets ?
Sub JVTest()
Dim Sh As Worksheet
Set Sh = ActiveWorkbook.Sheets.Add
Sh.Name = "MyNewSheetsName"
End Sub
> How can I name the sheets ?
Sheets("SheetName").Name = "Literal"/Variable
Good Luck,
j
ashe...@inlandkwpp.com
I have done this using part of the apache cocoon project and XML/ XSL.
You can create an xml document and transform it into a formated excel
document with as many sheets as u want.
By using XML / XSL along with numerous (free) libraries you can do
lots of funky stuff.
-Adam
here is an example of the xsl:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:sql="http://apache.org/cocoon/SQL/2.0"
xmlns:gmr="http://www.gnome.org/gnumeric/v7" >
<xsl:param name="view-source"/>
<xsl:template match="page">
<gmr:Workbook xmlns:gmr="http://www.gnome.org/gnumeric/v7">
<gmr:Sheets>
<gmr:Sheet DisplayFormulas="false" HideZero="false"
HideGrid="false" HideColHeader="false" HideRowHeader="false"
DisplayOutlines="true" OutlineSymbolsBelow="true"
OutlineSymbolsRight="true">
<gmr:Name><xsl:value-of select="title"/></gmr:Name>
<gmr:MaxCol>2</gmr:MaxCol>
<gmr:Cols DefaultSizePts="48">
<gmr:ColInfo No="0" Unit="48" MarginA="2"
MarginB="2" Count="7"/>
</gmr:Cols>
<gmr:Rows DefaultSizePts="12.8">
<gmr:RowInfo No="0" Unit="12.8" MarginA="0"
MarginB="0" Count="9"/>
<gmr:RowInfo No="10" Unit="12.8" MarginA="1"
MarginB="0" Count="24"/>
</gmr:Rows>
<gmr:Cells>
<xsl:apply-templates/>
</gmr:Cells>
</gmr:Sheet>
</gmr:Sheets>
</gmr:Workbook>
</xsl:template>
<xsl:template match="content">
<xsl:apply-templates/>
</xsl:template>
<xsl:template match="para">
<gmr:Cell Col="0" ValueType="60">
<xsl:variable name="rownumber"><xsl:number level="any"
from="content" count="para"/></xsl:variable>
<xsl:attribute name="Row">
<xsl:value-of select="$rownumber"/>
</xsl:attribute>
<gmr:Content>
<xsl:apply-templates/>
</gmr:Content>
</gmr:Cell>
</xsl:template>
<xsl:template match="title"></xsl:template>
</xsl:stylesheet>
George H.
build multiple excel sheets. This was some test code I wrote to test
the functionality.
We are a Unidata 6 / SB+ / Unix shop
You will use :
WIN.DDEOPEN
WIN.DDEPOKE
WIN.DDECLOSE
051: CALL WIN.DDEOPEN("EXCEL_":EXCEL.SUFFIX, "excel", "Sheet1" , OK)
052: *
053: IF NOT(OK) THEN
054: *CALL WIN.COLOR("LightRed","")
055: PRINT "We could not open the link so we will exit the
demonstration."
056: RETURN
057: END ELSE
058: CALL *DC.LOCK.RECORD(R.HOLD, K.HOLD, FV.HOLD)
059: *-----------------------------------------------------------------------
060: * Build the headings
061: *-----------------------------------------------------------------------
062: FOR XX = 1 TO MAX.DICTS
063: CALL WIN.DDEPOKE("EXCEL_":EXCEL.SUFFIX, "R1C":XX,
DICTS<1,XX>)
064: NEXT XX
065: *-----------------------------------------------------------------------
066: * Load the Data
*--: P
067: *-----------------------------------------------------------------------
068: MAX.FMS = DCOUNT(R.HOLD,@FM)
069: FOR XX = 1 TO MAX.FMS
070: FOR YY = 1 TO MAX.DICTS
071: CALL WIN.DDEPOKE("EXCEL_":EXCEL.SUFFIX, "R":(XX+1):"C":YY,
R.HOLD<XX,
YY>)
072: NEXT YY
073: NEXT XX
074: *-----------------------------------------------------------------------
075: * Keep the _HOLD_ file clean
076: *-----------------------------------------------------------------------
077: *DELETE FV.HOLD,K.HOLD
078: END
079: CALL WIN.DDECLOSE("EXCEL_":EXCEL.SUFFIX)
080:
081:
082:
083:
084:
085: *-----------------------------------------------------------------------
086: * Build sheet 2 to see if we can
087: *-----------------------------------------------------------------------
088:
*--: P
089: CALL WIN.DDEOPEN("EXCEL_":EXCEL.SUFFIX, "excel", "Sheet2" , OK)
090: *
091: IF NOT(OK) THEN
092: *CALL WIN.COLOR("LightRed","")
093: PRINT "We could not open the link so we will exit the
demonstration."
094: RETURN
095: END ELSE
096: *CALL *DC.LOCK.RECORD(R.HOLD, K.HOLD, FV.HOLD)
097: *-----------------------------------------------------------------------
098: * Build the headings
099: *-----------------------------------------------------------------------
100: FOR XX = 1 TO MAX.DICTS
101: CALL WIN.DDEPOKE("EXCEL_":EXCEL.SUFFIX, "R1C":XX,
DICTS<1,XX>)
102: NEXT XX
103: *-----------------------------------------------------------------------
104: * Load the Data
105: *-----------------------------------------------------------------------
106: MAX.FMS = DCOUNT(R.HOLD,@FM)
107: FOR XX = 1 TO MAX.FMS
108: FOR YY = 1 TO MAX.DICTS
109: CALL WIN.DDEPOKE("EXCEL_":EXCEL.SUFFIX, "R":(XX+1):"C":YY,
R.HOLD<XX,
YY>)
110: NEXT YY
*--: P
111: NEXT XX
112: *-----------------------------------------------------------------------
113: * Keep the _HOLD_ file clean
114: *-----------------------------------------------------------------------
115: *DELETE FV.HOLD,K.HOLD
116: END
117: CALL WIN.DDECLOSE("EXCEL_":EXCEL.SUFFIX)
George
ashe...@inlandkwpp.com wrote in message news:<b5mk90pbfpqdkp3nt...@4ax.com>...
George H.
George
ashe...@inlandkwpp.com
I don't like DDE or any microsoft API. They like to give you
functionality and then take it away in service packs. In my
experience they are untstable and unsuitable for a non-crash
enviroment. That would also get generated on the client side and is
subject to many different problem scenarios. It also relies on the
client having *most likely* a specific version of excel (although
multiple may work another program such as open office wouldn.t). XML
/ STYLE sheets is a flexible way to output pretty much any document.
Pick definately does not perform well in the area of accessibility and
you are better off getting the data off the system and working with it
using external tools. Most tools are available free to use *even
commercially - apache license* and you don't have to rely on 3rd party
proprietary software.
Of course, there are development costs with taking the time to make
such a system but in the end you will have stable, extensible and more
flexable scheme of generating documents without relying on expensive
software.
-Adam
George H.
>
> I don't like DDE or any microsoft API. They like to give you
> functionality and then take it away in service packs. In my
> experience they are untstable and unsuitable for a non-crash
> enviroment. That would also get generated on the client side and is
> subject to many different problem scenarios. It also relies on the
> client having *most likely* a specific version of excel (although
> multiple may work another program such as open office wouldn.t). XML
> / STYLE sheets is a flexible way to output pretty much any document.
> Pick definately does not perform well in the area of accessibility and
> you are better off getting the data off the system and working with it
> using external tools. Most tools are available free to use *even
> commercially - apache license* and you don't have to rely on 3rd party
> proprietary software.
>
> Of course, there are development costs with taking the time to make
> such a system but in the end you will have stable, extensible and more
> flexable scheme of generating documents without relying on expensive
> software.
>
> -Adam
True, I am not a big fan of DDE either. I know that DDEPOKE works
because I have written test programs and it is very easy to use. I
usually create Excel spreadsheets by creating a web page with a table
and sticking ".xls" on the end.
George
PtLin
name the file with a ".xls" extension. Open with excel, change column widths,
and SAVE AS a worksheet. All very easy. All reports in my system can be
displayed, printed, exported to excel, represented as HTML, represented as XML,
saved as a unix text file, e-mailed, etc etc etc.
Luke Webber
news:20040513171346...@mb-m17.aol.com...
> Anothter easy way to accomplish this is to export tab delimited documents
and
> name the file with a ".xls" extension. Open with excel, change column
widths,
> and SAVE AS a worksheet. All very easy. All reports in my system can be
> displayed, printed, exported to excel, represented as HTML, represented as
XML,
> saved as a unix text file, e-mailed, etc etc etc.
I've done quite a bit of work generating HTML with a .xls extension.
Microsoft have a guide for doing just this at...
It's less than ideal, but far better than just text or CSV. You can use XML
for later versions of Office as well, but the XML is far from simple!
Cheers,
Luke
aussiecoder
using ODBC. This way you format the spreadsheet as required (fonts,
colours, formulars), then populate it with the data.
You can tell excel to refresh the data when the sheet is open (or
not).
just my 2c
regards,
Dave
BTW, I work for Fusionware who sell ODBC middleware for mv databases.
pt...@aol.com (PtLin) wrote in message news:<20040513171346...@mb-m17.aol.com>...
R Kann
(Attunity connect). It works real nice to pull data from Jbase into
excel and is very flexible.
Richard Kann
Mark Brown
VB macro using the D3 class library and do all the work in Excel. Works
like a charm. You can add forms and prompts and make the process
interactive.
Mark Brown
Drexel Mgt Inc
www.drexelmgt.com
"aussiecoder" <aussi...@hotmail.com> wrote in message
news:26c04ccc.04052...@posting.google.com...
Infotools
You can do it with BRIZ from PickBasic
1 way
Create a sheet and place in it data by means of the built-in
terminal object
2 way
Create a file with dividers, create macro, start macro
3 way
Write client program with support COM and start it from the session
(completely interactive)
.......
Best regards, Grigory
infotools.hotmail.ru