[AccessD] Access To Excel via a CSV File

I created a rather elaborate series of spreadsheets in an app once and, in
order to get it to work right I cheated.  To do all the formatting, etc., I
used the macro recorder in Excel (like for selecting and bolding a group of
cells, or changing the font or size, or the width of a column, etc.) and
then just copied the code out of the code page behind the spreadsheet and
pasted it into the access code.  I can send you something off line if you
like. 

But here's a snip to give you some ideas.  First I set up a two dimensional
array DIMmed as variant to hold the spreadsheet values and then used
automation to create the spreadsheet from a spreadsheet template. 

Snip:

Aray(21, 1) = "Total Landing and Skippers"
Aray(23, 1) = "   Berth Credits - 1/2-3/4 All"
Aray(24, 1) = "   Berth Credits - Full Day Open Party"
Aray(25, 1) = "   Berth Credits - Full Day Charter"
Aray(26, 1) = "   Adjmt. Inclusive"
Aray(27, 1) = "   Adjmt. Long Range"
Aray(29, 1) = "Adjusted Net"

Aray(31, 1) = "Booking Fees:"

Aray(32, 1) = "   Bait - 1/2-3/4"
Aray(32, 2) = 0.125
Aray(32, 3) = "=C6+D6-C12-D12-C23-D23"
Aray(32, 4) = "=Round(B32*C32,2)"


After setting up all the cell values in the array I write the values to the
spreadsheet.

'write it out
For intThisRow = 1 To 67
    For intThisCol = 1 To 7
        
            objXLWS.Cells(intThisRow, intThisCol) = Aray(intThisRow,
intThisCol)
        
    Next intThisCol
Next intThisRow

Then do a little formatting:

' Currency format for numbers
    For intThisCol = 2 To 7
        objXLWS.Columns(intThisCol).NumberFormat = "0.00_);[Red](0.00)"
    Next intThisCol

    For intThisRow = 32 To 37
        objXLWS.Cells(intThisRow, 2).NumberFormat = "0.00%"
    Next intThisRow
    
    For intThisRow = 39 To 52
        objXLWS.Cells(intThisRow, 2).NumberFormat = "0.00%"
    Next intThisRow

' Format Bold Some Rows
    objXLWS.Rows(1).Font.Bold = True
    objXLWS.Rows(2).Font.Bold = True
    objXLWS.Rows(3).Font.Bold = True
    objXLWS.Rows(5).Font.Bold = True
    objXLWS.Rows(19).Font.Bold = True

' Hide These Cells
    objXLWS.Range("B1:D1").NumberFormat = ";;;"

' Font Size
    objXLWS.Range("A1:E67").Select
    objXLWS.Cells.Font.Size = 9
    
' Margins
    objXLWS.PageSetup.BottomMargin = 0.25
    objXLWS.PageSetup.TopMargin = 0.25
    
' Print Area
    objXLWS.PageSetup.PrintArea = "$A$1:$E$67"

HTH

Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Thursday, June 05, 2008 7:34 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Access To Excel via a CSV File


Can any of you Access/Excel gurus help here please.
I need to create a csv spreadsheet by code.  When it is complete, I need to
put formatting on it. Ie,make columns into Currency Types. Bold some Rows,
set some bacground colours, etc.
I have got as far as creating the CSV ok but then I am stumped.
Any advice most welcome.
Ta
Max

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG. 
Version: 8.0.100 / Virus Database: 270.0.0/1485 - Release Date: 5/6/2008
10:07