Exporting Access Query to Excel - Help

[CMPaccess]

I have two question I would be grateful of some help with.

1) I have a query that I'm exporting to Excel using the transferspreadsheet method. This works fine apart from I keep getting randon errors when the code reaches the format part. Eg

exApp.Rows("1:1").Select
Selection.RowHeight = 35 -- On this line I get an error.

But sometimes it works others it does not. Is there a way to solve this. ??


2) My second question is how do I set up a loop so I only format the correct number of cells to match the number of records in the query. ??


Thanks in advance.

 

[mordja]

CMPaccess,

Maybe you should be in the VBA for apps section. Not sure about your first question but as to the second, assuming you write one record to one row then

ActiveSheet.UsedRange.End(xlDown).Row

Will give you the number of the last row with data in it.

So you could do a loop of your choice with that as your maximum bound.

Mordja

 

[PHV]

Replace this:
Selection.RowHeight = 35
By this:
exApp.Selection.RowHeight = 35

You may even replace the two lines with this:
exApp.Rows("1:1").RowHeight = 35


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CMPaccess]

Thanks PH,

Mordja, could you expand a bit on the loop theroy.

Say I have 10 records in the query. How do I format the spreadsheet so

Row 1 is individual.
Rows 2-9 are the same
Row 10 is individual

Cheers

 

[mordja]

CMPAccess,

I dont understand, you asked how to find the last row of your query which is the last row of the UsedRange of the spreadsheet. So now you want to format every 10th row differently to the rest ?

Maybe something like

(Format First Row)
For i = 2 to ActiveSheet.UsedRange.End(xlDown).Row
If i mod 10 = 0 Then
(Format One Way)
Else
(Format Other Way)
End If
Next i

I cant remember if the first row starts at 0 you may have to modify the above slightly.

Mordja

 

[CMPaccess]

To expand on what I mean.

Basically I want to export results of a query into a spreadsheet in excel. I will then use this to xport into autoCAD using a Lisp routine I have.

Depending on how many drawings have been selected. The query could hold any given number of results.

The table I would like to format has to follow a certain style. I.E.

Row 1 is the Title.
Rows 2 to what ever are the drawing data.
Last row is the end of the table, and hence the cell border line at the bottom will be thicker.

Hope that makes more sense.

Would appreciate your help. Not much good at this loop idea.

thanks

 

[mordja]

CMPaccess,

You want to format your excel sheet in a particular manner without knowing the number of results ?? The answer I provided above does exactly that. UsedRange returns the used range of your excel sheet ie all results. If you want to format row 1 as a title rows 2 - (n-1) however and row n however then just modify the above a little.

(Format First Row)
For i = 2 to ActiveSheet.UsedRange.End(xlDown).Row -1
(Format Middle Rows)
Next i
(Format Last Row)


If you result set is just one column then Cell(i,1) will probably get your cell, if not build a range using i.

Mordja

 

[CMPaccess]

Mordja,

Apologies for this. My code below is running but what appears to happen is that is formats the first row three times.

Could kindly point out where I have gone wrong.

thanks

Dim i As Integer


For i = 1 To ActiveSheet.UsedRange.End(xlDown).Row - 1

Next i


Sub Macro4()
'
' Macro4 Macro
' Macro recorded 10/11/2004
'

'
Dim i As Integer
'Format Column Widths
Selection.ColumnWidth = 6
Columns("B:B").Select
Selection.ColumnWidth = 15
Columns("C:C").Select
Selection.ColumnWidth = 35
Columns("D").Select
Selection.ColumnWidth = 10
'Format First Row
Rows("1:1").Select
Selection.RowHeight = 35
Range("A11").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
'Format Middle Rows
For i = 2 To ActiveSheet.UsedRange.End(xlDown).Row - 1
'Rows("2:2").Select
Selection.RowHeight = 18
'Range("A22").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
'Format Last Row
Next i
'Range("A33").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.RowHeight = 18
'Format Column A & D to Center Horizontal Alignment
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("D").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

End Sub

 

[mordja]

CMPAccess,

From what I can see it only ever formats, the first three rows :

Columns("B:B").Select
Columns("C:C").Select
Columns("D").Select
Rows("1:1").Select
Range("A11").Select
'Rows("2:2").Select
'Range("A22").Select
'Range("A33").Select
Columns("A:A").Select
Columns("D").Select

As you have commented out the statements in bold, when you enter your

For i = 2 To ActiveSheet.UsedRange.End(xlDown).Row - 1

loop the last Selection you had was Range("A11"), therefore you continue to reformat Range("A11") until the loop is finished. You need to uncomment Rows("2:2").Select, but also change it so that incorporates the loop variable i other wise you will continue to format the second row again and again.

Delcare a string and build your range.

ie
instead of

'Rows("2:2").Select
'Range("A22").Select

use something like

RangeStr = i & ":" & i
Rows(RangeStr).Select
RangeStr = "A" & i & "" & i
Range(RangeStr).Select


Lastly im assuming the following 'Range("A33").Select
which is outside the loop is meant to represent the last row as opposed to the third row. Use something similar to the above here.

Hope this helps

Mordja

 

[CMPaccess]

Mordja,

Thanks.

I had almost cracked it before you replied. I just wasn't sure how to build a range using "i"

Thanks a mill.