export to xls

[Dimitris Nikolakakis]

I have the following qry:

SELECT [OrdersTx].[OrderNo],
LTRIM(Format([OrdersTx.StorageItem],"@@@@@@@@@@@@@@@@@@@@")) AS StorageItem,
[OrdersTx].[Quantity]
FROM OrdersTx
WHERE [Forms]![FOrders]!OrderID=[OrdersTx].[OrderNo] And
[OrdersTx].[typeID]="ORD";

and I have a button on the form FOrders that:

DoCmd.OutputTo acOutputQuery, "qOrder_XLS(IRWIN)", acFormatXLS,
"c:\order.xls", 0

1. Is there any better way to export the StorageItem as text? (in the table
is text 20)?
2. In a very strange way I get error after I press the button that there is
no field in the qry. I open the qry in design and I see no field. I have to
rewrite it and save it.


thanks

 

[Arvin Meyer]

You can use the Space function to add spaces to either side of the field.
Here's an example of adding it to the left side of a field named Whatever:

SELECT Table1.Whatever, Space(20-Len([Whatever])) & [Whatever] AS Expr1
FROM Table1;

You can also use a custom function like LPad to add spaces:

Function LPad(strIn, ByVal PadCharacter As String, intCount As Integer) As
String
' Adds character PadCharacter to the left of strIn to make it right
justified
On Error Resume Next

If Len(PadCharacter) = 0 Then PadCharacter = " "

If intCount < 1 Then
LPad = ""
Else
LPad = Right$(String$(intCount, Left$(PadCharacter, 1)) & strIn,
intCount)
End If

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I have the following qry:

SELECT [OrdersTx].[OrderNo],
LTRIM(Format([OrdersTx.StorageItem],"@@@@@@@@@@@@@@@@@@@@")) AS StorageItem,
[OrdersTx].[Quantity]
FROM OrdersTx
WHERE [Forms]![FOrders]!OrderID=[OrdersTx].[OrderNo] And
[OrdersTx].[typeID]="ORD";

and I have a button on the form FOrders that:

DoCmd.OutputTo acOutputQuery, "qOrder_XLS(IRWIN)", acFormatXLS,
"c:\order.xls", 0

1. Is there any better way to export the StorageItem as text? (in the table
is text 20)?
2. In a very strange way I get error after I press the button that there is
no field in the qry. I open the qry in design and I see no field. I have to
rewrite it and save it.


thanks

 

[Tim]

You could try the following, which works for me. You must
have 2 forms open: a datasheet form
(properties/other/modal=yes,popup=no) with data source set
to your query, and a second form
(properties/other/modal=no,popup=yes) with the export
button on it. The second form floats in front of the
first, datasheet form.

If you don't want the user to see the data in the
datasheet form, you could set the 'visible' property for
the datasheet form to 'no', probably in its load
procedure. When you press the export button, the data on
the datasheet form is exported to c:\order.xls.

Create two macros:

MacExport:
Action: OutputTo,Form,FOrders,Microsoft Excel,c:\order.xls

MacRunExcel:
Action: RunApp, Excel c:\order.xls

And this is the code behind your button on the second,
popup form:

Private Sub cmdMacExport_Click()
On Error GoTo Err_cmdMacExport_Click
Dim stDocName As String
stDocName = "macExport"
DoCmd.RunMacro stDocName
stDocName = "macRunExcel"
DoCmd.RunMacro stDocName
Exit_cmdMacExport_Click:
Exit Sub
Err_cmdMacExport_Click:
MsgBox Err.Description
Resume Exit_cmdMacExport_Click
End Sub

This will export to an Excel spreadsheet. Of course the
name of the Excel file is hardcoded in the macros, but
there may be a similar way to accomplish the same thing.

I hope this helps.

-----Original Message-----
I have the following qry:

SELECT [OrdersTx].[OrderNo],
LTRIM(Format

([OrdersTx.StorageItem],"@@@@@@@@@@@@@@@@@@@@")) AS
StorageItem,

[OrdersTx].[Quantity]
FROM OrdersTx
WHERE [Forms]![FOrders]!OrderID=[OrdersTx].[OrderNo] And
[OrdersTx].[typeID]="ORD";

and I have a button on the form FOrders that:

DoCmd.OutputTo acOutputQuery, "qOrder_XLS(IRWIN)", acFormatXLS,
"c:\order.xls", 0

1. Is there any better way to export the StorageItem as text? (in the table
is text 20)?
2. In a very strange way I get error after I press the button that there is
no field in the qry. I open the qry in design and I see no field. I have to
rewrite it and save it.

thanks
.