473,320 Members | 2,080 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

removing wrap text format when exporting to excel

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
4 11835
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
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
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
I will give it a try thanks for the input.

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Saur | last post by:
Hi, I am using an export to excel functionality from my ASP page. I have set the content type as Response.ContentType = "application/vnd.ms-excel" The data i am exporting has values like 1-2,...
3
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works...
0
by: Norman Fritag | last post by:
Hi there, 1) I is there a more elegant way, more professionally way this export could done, as I have written some code for the first time? 2) who could I wrap text cell a1 a3 via code ? 3) is...
1
by: kim | last post by:
Hello! Here, in brief, is my problem. Access 2002. I need to export to a csv text file with text delimiters on every field. I find that if I save the original data in Excel and make sure the...
8
by: John Brock | last post by:
I am creating an Excel workbook using VB.NET, and have run into a problem. Excel at times insists on reformatting data that I enter into cells, e.g., converting "01234" to "1234", and this screws...
8
by: Taffman | last post by:
I've searched this goup for an answer to this, there are many discussions that come close but non that I can find that actually addresses this particular problem. I'm exporting queries to Excel....
3
by: ggupta78 | last post by:
Hi, Currently in our application we use the MHTML way of exporting to Excel i.e we render the datagrid HTML and set the MIME type to Excel. This works great but has the following limitation: 1....
15
by: pakerly | last post by:
How would i do this, convert a test file to excel? Lets say my text file has fields like this: NUMBER NAME ADDRESS PHONE 11002 Test1 ...
3
by: nandithadevaraj | last post by:
When exporting a report to excel, is it possible to keep the properties around numeric values? More specifically - when exporting a report into excel, all of the numbers in a currency column are...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.