exporting a range of cells to excel

[datacontrol]

Anyone have the code or means to export a range of cells of a table to an excel spreadsheet with no column headings? I can not have table headings in my report. I have the export feature set up now on a comand button and it works, but I do not want column headings.

I have searched and searched here for this specific piece of info. Thanks for your help.

 

[dcx693]

If you're using Access 2000:
Displaying Data from a Recordset in an Excel Worksheet
If you use this, be sure to skip the code section that says "Use field names as headers in the first row" since you don't want column headings.

 

[dcx693]

That post will work for you if you want to run the code from Excel, but if you prefer to run it from Access, you can download the automation help file here: Microsoft Office 97 Automation Help File Available it has sample code in it. Here's the Office 2000 version: Microsoft Office 2000 Automation Help File Available and the XP version: Microsoft Office XP Automation Help File Available .

Also, take a look at this: Using Automation to Create and Manipulate an Excel Workbook

 

[datacontrol]

ADO learning

Thanks so much for your help. I downloaded the help file and it was very helpful. My command button now does exactly what I want (exporting a range, w/o column headings). One minor detail....how do I set the coulmn widths for export? I need one field to be 8 chars, next 3, so on. I pasted my code from my command button below.


____________________________________________________
Private Sub Command117_Click()
On Error GoTo report_Err

'Create a Recordset from all the data in the INVWC7 table
Dim sRecon As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset

sRecon = "N:\LOG PRO-ADV RECON\reconciliation.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sRecon & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("INVWC7", , adCmdTable)

'Create a new workbook in Excel
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

'Transfer the data to Excel
xlSheet.Range("A1").CopyFromRecordset rs

'Save the Workbook and Quit Excel
xlBook.SaveAs "N:\LOG PRO-ADV RECON\advantage\INV280-B.xls"
xlApp.Quit

export_Exit:

MsgBox "Your INV280-B report has been saved to: N:\LOG PRO-ADV RECON\advantage\INV280-B.xls"

Exit Sub


report_Err:
MsgBox "Your report could not be exported. Be certain that you have no other instances of this file open."
Exit Sub

'Close the connection
rs.Close
conn.Close

End Sub

 

[dcx693]

No problem I hadn't looked at those helpfiles myself in a while and remembered how helpful they were.

As to your question: you cannot specify the column width if you are using:
Set rs = conn.Execute("INVWC7", , adCmdTable) to export the entire INVWC7 table. What you can do is create a query based on the table that formats the columns exactly the way you want them. You can then call the query using the same syntax, just substitute the stored query name for "INVWC7". If you don't want to keep a stored query just for this procedure you can write the SQL query text directly into your code.

 

[datacontrol]

chars

Thanks yet again. I have looked into using a query like you suggested. This is probably a real stupid question but I do not see anywhere I can define column width in chars for what I need.

Please advise

 

[fuzzygeek]

Worksheets

I have a table that I keep the column widths in. Then I set the width, fonts, etc. The following is a bit of my code(wsXL is Excel Worksheet):

wsXLValues.Range("F" & intJ).Font.Size = 8


'Set the column width, boldness and center all columns
wsXLCCS.Range(rstColumnTitles(0)).ColumnWidth _
= rstColumnTitles(2)
wsXLCCS.Range(rstColumnTitles(0)).Font.Bold = True
wsXLCCS.Range(rstColumnTitles(0)).HorizontalAlignment = xlCenter

'Fill in the second row titles for Worksheet 2
wsXLCCS.Range(rstColumnTitles(3)).Value = Nz(rstColumnTitles(4), "")
wsXLCCS.Range(rstColumnTitles(3)).ColumnWidth _
= rstColumnTitles(2)

wsXLCCS.Range(rstColumnTitles(3)).Font.Bold = True
wsXLCCS.Range(rstColumnTitles(3)).HorizontalAlignment = xlCenter
wsXLCCS.Range(rstColumnTitles(3)).Borders(xlEdgeBottom).Weight = xlMedium

Hope it helps!


Sorry...Misunderstood what you were asking. Not my day, the virus wrecked havoc here. Even My edit missed up

 

[dcx693]

datacontrol, you are asking about setting the width of the data in Access to be exported to Excel right? I didn't think you were asking about the column widths in Excel, which I think fuzzygeek is writing about.

In Access queries, you specify the format of the fields (fixed, long date, etc...). If you need data to be a specific width, set the decimal places for numeric values and use the Left and other string functions for text.

 

[datacontrol]

field width

I understand how to change field types. how do I set formatting in query design view? I have tried and tried, no luck.

Thanks

 

[dcx693]

No, no, no

We're not changing field types. We're changing field PROPERTIES inside a query. When you're in query design mode, go to the View menu and choose Properties. For each numeric field, you can set the format.

 

[datacontrol]

column width

No, we are not changing field types. You can also access the properties by right clicking on a column in query design mode. So, my question is still unanswered. I have tried and tried. please tell me exactly what and where I should enter to make a numeric field 10 chars. All of my data is identical in these tables, as far a field length is concerned.

So in properties you have:

Description.....
Format......
Input Mask......
Caption.....

I would think that format is where the values go for column width, however I have tried this alreay as well as the input masks.

Thanks for your help with my project.

 

[dcx693]

OK, you've gotten to the field properties. In the Format line, enter something like:
0000.000;(0000.000)
if you want 4 digits before the decimal and 3 after. Just change the configuration of the zeroes depending on how you want it. This is a custom number format, and you can specify 4 sections to it:
positive format ; negative format; format if zero ; format if null. In the example above, negative numbers have parentheses around them.

 

[datacontrol]

text formatting

Thanks for all of your help....and especially time.

next question......what if my data is text? How would I format for say a column with three text chars?

Thanks again...I love this place

I tried your 00000000 formatting. When I save, and then run the query the results are displayed with spaces to the right, just like they were before formatting.....

hmmm

 

[dcx693]

When I save, and then run the query the results are displayed with spaces to the right

Spaces?

If you're dealing with text, investigate the use of the @ character in the Format line.

By the way, most of this stuff can be found in the Access help files. Check it out.

 

[datacontrol]

it's only field size? isn't a database app capable of that?

I have tried the help files...that's exactly why I keep coming back here.


All of your suggestions for formatting field widths did nothing. I used "@@@" for a field containing text, then ran the query and same old crap. The field is something like 15 spaces.

I have tried adjusting the query when I am viewing the results. You can set column width here and save. However, column widths here can not be set in chars. For example, if I set it to 3 for a 3 char field, my last char is truncated.

I even tried setting the column widths here any way (viewing query results), then exported to Excel and every column width in Excel is 8.43!!!??? I am confused. Time to take a nap under my desk....

How can something so simple be so difficult?

Thanks again

 

[dcx693]

Hmm...don't know what's wrong, but maybe I'm just thinking of it wrong. Try posting your question in the query section and see what answers you get. Good luck.