Trying to create an Excel spreadsheet

[Rico]

I am trying to get the table field names as the cell names, I thought this was possible, but all I get is the file names as the first row of data.

Dim FolderPath As String
FolderPath = "C:\Access\Meter Readings for " & CurrentMonth (Doesn't need to be there, if it is, will overwrite it.)



DoCmd.OutputTo ObjectType:=acOutputTable, ObjectName:="PropertyTB", OutputFormat:=acFormatXLS, OutputFile:=FolderPath (Tried this, doesn't seem to work very well.)

DoCmd.TransferSpreadsheet acExport, , "PropertyTB", FolderPath (This works but doesn't alter the cell titles (still A B C etc), puts table field names as first line.)

DoCmd.TransferSpreadsheet acExport, , "PropertyTB", FolderPath, True (Same as above)

MsgBox "Done, transfered PropertyTB to Meter Readings for " & CurrentMonth

Any help would be appreciated, thanks Roy

[madpiet]

This worked for me.
Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Flour Percentage", "C:\Users\User\Desktop\ExportedXLS", True
End Sub

[June7]

AFAIK, Excel column letters cannot be replaced. Column letters and row numbers must be present in the Excel grid frame. First row of sheet is supposed to be custom column headers if you want any.

madpiet, is your example missing a dot for the file extension? What do you mean by "worked" - are the column letters in frame actually replaced with table fieldnames?

Rico, why not use acSpreadsheetTypeExcel12XML and .xlsx extension?

[Rico]

This site seems to have been down for a couple days, (in Australia).
Give me time to check out the 2 replies and I will get back to you.
Thanks, Roco

[Micron]

I don't think cells have names - they have addresses. Columns have letters to id them, but those aren't names either AFAIK. So maybe you want your Access table field names as Excel column headers?

[CJ_London]

Excel column letters cannot be replaced. Column letters and row numbers must be present in the Excel grid frame.

but you can hide them - on the ribbon - Page Layout>untick Headings>View


to do this automatically, you would need vba code to untick the box. To determine the code, record a macro in excel doing the actions required and then after you have created your file, using vba open it and apply the recorded code - which as I recorded it would be

Sub Macro2()
'
' Macro2 Macro
'


    ActiveWindow.DisplayHeadings = False
End Sub

You may have other requirements such as naming sheets, hiding gridlines, apply formatting, freeze top row, autosize columns as well, so record these actions at the same time

[Rico]

Ok, thanks to everyone, I think I have enough info to fix my problem now.
Rico

[Welshgasman]

Well, please post your solution when you have it.
These forums are here to help others as well?

[Rico]

I wrote a small ms access program to record the electrical meter readings, once a month, for our village of 282 properties.
Once recorded, my access program produced an Excel Spreadsheet to be passed on to our accounts dept.
I was hoping that the table field names could replace the spreadsheet Columns names. (1 2 3 etc). you now tell me that this can't be done and the field names end up as the first row of the spreadsheet.
So, I will delete the first row after I produce the spreadsheet, because the accounts dept say they don't want it there.
I'm happy, Rico

[Micron]

Well if you're happy with what you've got then carry on. Otherwise I wonder if Automation is what you can make use of. From Access, you could do practically anything with Excel. That includes creating a sheet or even a workbook and sheet, or populating a sheet with data below the sheet column headers, or within a named range, or creating the headers and populating the sheet...

and somewhere within all of that you'd be able to get table names and/or field names for your Excel headers - if that's what you want, but not clear to me what you want from Access on the Excel side.

This is where I'd direct anyone for Automation code
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm