Writting ACCESS table to Excel

[Stuart McCall]

This line:

Set xlApp = Excel.Application

should be:

Set xlApp = New Excel.Application

or:

Set xlApp = CreateObject("Excel.Application")

 

[mls via AccessMonster.com]

Hi Stuart, Can you change the specific line in Geoff's code to fix ERROR 429.
I could not figure out and I feel so bad about it.


Thanks

 

[mls via AccessMonster.com]

Hi Geoff,
I commented the following lines which checks for already running application
and it works perfectly.

Thank you so much for all your efforts.

'On Error Resume Next
' Set objXL = GetObject(, "Excel.Application")
' If Err.Number <> 0 Then
'On Error GoTo Error_DXReport
' End If

 

[GeoffG]

Hey MLS,

I'm pleased you're home and dry.
It's odd that GetObject() was causing such pain.

Your experience is making me think it'd be easier to
recommend using "New" - rather than CreateObject() - when
you can set a reference to Excel in Tools > References, that
is:

Set objXL = New Excel.Application

AFAIK Access uses DAO under the hood and I prefer it.

Geoff

 

[Stuart McCall]

Hi Stuart, Can you change the specific line in Geoff's code to fix ERROR
429.
I could not figure out and I feel so bad about it.


Thanks

Er, I thought I did that with the above code. Anyhow it seems everyone's
happy now (I notice from elsewhere in the thread).

 

[Stuart McCall]

Hey MLS,

I'm pleased you're home and dry.
It's odd that GetObject() was causing such pain.

Your experience is making me think it'd be easier to recommend using
"New" - rather than CreateObject() - when you can set a reference to Excel
in Tools > References, that is:

Set objXL = New Excel.Application

AFAIK Access uses DAO under the hood and I prefer it.

Geoff

You can use 'New' when you want to create an instance, but to connect to an
already loaded app, you still have to use GetObject (AFAIK - I never found
another way).

Yes Access does use DAO internally. I only ever touch ADO when the situation
requires it, which in my case is hardly ever.

 

[mls via AccessMonster.com]

I have one more question. How can I format my range to center alignment? Also
where can I get complete list of formats? with the following code I am not
getting any error and even it is not formatting too

With objRNG1
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

In the code we have 'objRNG3.Columns.AutoFit but commented this because the
columns are not even size.

Hey MLS,

[quoted text clipped - 10 lines]

Geoff

You can use 'New' when you want to create an instance, but to connect to an
already loaded app, you still have to use GetObject (AFAIK - I never found
another way).

Yes Access does use DAO internally. I only ever touch ADO when the situation
requires it, which in my case is hardly ever.

 

[GeoffG]

As you mention objRNG1, I think you want to center the
headings and the data. You can do this by using the
CurrentRegion method of the Range object to expand objRNG1,
as follows:

' This code is as before just to indicate where the new code
goes:
' Point again to where copying is to begin,
' move down one row, and copy recordset data:
Set objRNG1 = objWS.Range(strStartCopyingAtCell)
Set objRNG1 = objRNG1.Offset(1, 0)
objRNG1.CopyFromRecordset objRST

' Then add the following new code:
' To center the headings and the data
' vertically and horizontally:
Set objRNG1 = objRNG1.CurrentRegion
With objRNG1
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

Just in case:
I don't think you want to center only the headings (because
they are formatted using objRNG2). However, if you did
(want only to format the headings), I'm sure you will have
figured the following code would do it:

' Copy recordset field names to the worksheet:
intFieldCount = objRST.Fields.Count
For iField = 0 To intFieldCount - 1
Set objRNG2 = objRNG1.Offset(0, iField)
With objRNG2
.Value = objRST.Fields(iField).Name
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Next

AutoFit:
The AutoFit code was just a demo to jazz things up!

Good luck with your project!
Geoff

 

[GeoffG]

The other part of your question was where can you find a
complete list of formats.

Here are a few tips (which you may already know):

1. The Excel Range object has a number of formatting
properties, the main ones being:
* Horizontal and vertical alignment
* Font

2. You may have on your hard disc the compiled html file,
VBAXLxx.CHM, (where xx = version number). This is the Excel
VBA help file. Double-click this file in Windows Explorer
to open it.

3. In the VBA editor you can press F2 to bring up the
Object Browser. In the Object Browser, select the Excel
object library (in the top textbox). Then:
(a) Enter some search text (eg Range) in the next box down.
(b) Click the binoculars to begin the search.
(c) Select a topic in the list of found topics.
(d) Click the question mark to bring up the help topic.
(e) Click the Close "X" button to close Object Browser.

4. Go to the Microsoft website. In the search box, enter
"Microsoft Excel" "topic you're interested in". Use double
quotation marks to limit the seach.

Correction to last post:
The CurrentRegion is a property, not a method, of the Excel
Range Object (as became evident when I took a look at
VBAXLxx.CHM).

Geoff

 

[GeoffG]

After poking around at bit, I came up with these formatting
code examples:

' Point to a range:
Set objRNG1 = objWS.Range("A1", "Z1")

' Put borders around each cell:
For Each objRNG2 In objRNG1.Cells
objRNG2.BorderAround xlContinuous, _
xlMedium, xlColorIndexAutomatic, _
RGB(255, 255, 255)
Next

' Format the whole range:
With objRNG1

' Shade each cell:
With .Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

With .Font
.Size = 12
.Bold = True
.FontStyle = "Arial"
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With


' Format various columns in different
' number formats:

' General number format:
Set objRNG1 = objWS.Columns(2)
objRNG1.NumberFormat = "General"

' Currency format
' (positive in black, negative in red)
Set objRNG1 = objWS.Columns(3)
objRNG1.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

' Time format:
Set objRNG1 = objWS.Columns(4)
objRNG1.NumberFormat = "hh:mm:ss"


' Point to top left corner:
Set objRNG1 = objWS.Range("A1")

' Expand range to embrace all contiguous data:
Set objRNG1 = objRNG1.CurrentRegion

' Sort data:
' Put cursor on the words "Sort" and
' "SortSpecial", press F1, and read help
' topics to determine which parameters to
' use after the words Sort and SortSpecial.
objRNG1.Sort
' or:
objRNG1.SortSpecial

Sorting in Excel looks complicated.
I would sort the recordset before copying it to Excel.

Geoff

 

[mls via AccessMonster.com]

Geoff, My automation process works perfectly. Thank you very much for all
your help.

After poking around at bit, I came up with these formatting
code examples:

' Point to a range:
Set objRNG1 = objWS.Range("A1", "Z1")

' Put borders around each cell:
For Each objRNG2 In objRNG1.Cells
objRNG2.BorderAround xlContinuous, _
xlMedium, xlColorIndexAutomatic, _
RGB(255, 255, 255)
Next

' Format the whole range:
With objRNG1

' Shade each cell:
With .Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

With .Font
.Size = 12
.Bold = True
.FontStyle = "Arial"
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

' Format various columns in different
' number formats:

' General number format:
Set objRNG1 = objWS.Columns(2)
objRNG1.NumberFormat = "General"

' Currency format
' (positive in black, negative in red)
Set objRNG1 = objWS.Columns(3)
objRNG1.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

' Time format:
Set objRNG1 = objWS.Columns(4)
objRNG1.NumberFormat = "hh:mm:ss"

' Point to top left corner:
Set objRNG1 = objWS.Range("A1")

' Expand range to embrace all contiguous data:
Set objRNG1 = objRNG1.CurrentRegion

' Sort data:
' Put cursor on the words "Sort" and
' "SortSpecial", press F1, and read help
' topics to determine which parameters to
' use after the words Sort and SortSpecial.
objRNG1.Sort
' or:
objRNG1.SortSpecial

Sorting in Excel looks complicated.
I would sort the recordset before copying it to Excel.

Geoff

I have one more question. How can I format my range to
center alignment? Also

[quoted text clipped - 10 lines]

this because the
columns are not even size.

 

[GeoffG]

Hi MLS
You're welcome.
Geoff