By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,222 Members | 1,728 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,222 IT Pros & Developers. It's quick & easy.

removing wrap text format when exporting to excel

P: n/a
Does anyone know if there is a snippet of code that automatically
removes the wrap text format in excel?

I have an access form that displays data and allows the user to click
on a cmdbutton to export the data to excel. However, every export
defaults to a formatted wrap text. I am trying to remove this default
if possible. Thanks in advance.

Here is my code:

Private Sub cmdADR_Excel_Click()
Dim strSQL As String
Dim dbDatabase As Database
Dim strQueryName As String
Dim intSQL As Integer

'Exports results to excel
If Len(txtCompoundSearch) < 1 Or IsNull(txtCompoundSearch) Then
MsgBox "Please run a search first."
Exit Sub
strSQL = Trim(txtCompoundSearch)
strQueryName = "qryCompFreq_Excel"
Set dbDatabase = CurrentDb()
On Error Resume Next
dbDatabase.QueryDefs.Delete (strQueryName) 'Deletes the query
On Error GoTo 0
Set qdfResults = dbDatabase.CreateQueryDef(strQueryName, strSQL)
'creates new query
End If

On Error GoTo Err_cmdExport_Click
DoCmd.OutputTo acOutputQuery, "qryCompFreq_Excel", acFormatXLS,
"qryCompFreq_Excel" & ".xls", True

Exit Sub
MsgBox Err.Description
Resume Exit_cmdExport_Click
End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
Not an Access question. Open a spreadsheet, start recording a macro,
and then select one or more columns and set the Wrap Text property to
false. Then automate Excel to do this for you.

If you export to a template, you should be able to run this anytime...

Nov 13 '05 #2

P: n/a
I don't think this will help as this is a multiple user form. Does
this mean I would have to supply this template to all users as well?

Nov 13 '05 #3

P: n/a
To achieve your desired functionality you will have to use automation.
Set a reference in Tools/References in your code moduel to Microsoft
Excel Object Library...


Sub SendToExcel()
Dim xlObj As Excel.Application, wkbk As Excel.Workbook
Set xlObj = CreateObject("Excel.Application.10")
Set wkbk = xlObj.Workbooks.Add
wkbk.SaveAs "C:\1A\test1.xls"
wkbk.Sheets("Sheet1").Cells.WrapText = False
Set xlObj = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1",
End Sub
On Excel this will work with version of Excel97 on up. If you aren't
using Office XP just leave out the 10 in "Excel.Application.10" and use
5 instead of acSpreadsheetTypeExcel9. This creates a new workbook on
the respective workstation and sets the Cells.WrapText = False. You
could take this a step further and check for the existence of a workbook
called test1.xls and use GetObject(, "Excel.Application.10") note that
for GetObject you must include a comma , at the beginning of the call -
unlike CreateObject.


*** Sent via Developersdex ***
Nov 13 '05 #4

P: n/a
I will give it a try thanks for the input.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.