By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 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
Else
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_cmdExport_Click:
Exit Sub
Err_cmdExport_Click:
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...

Then

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
wkbk.Save
wkbk.Close
xlObj.Quit
Set xlObj = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1",
"C:\dir1\test1.xls"
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.

Rich


*** Sent via Developersdex http://www.developersdex.com ***
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.