TransferSpreadsheet with columnwidth set

[Gismo]

Hi all,

I have quite a few queries that I transfer to excel
I want the column width to be set to Auto Fit

Below is the code and the module

Please could you advise if I am on the right track

Private Sub ExcelOpenItems_Click()
On Error GoTo ExcelOpenItems_Click_Err

Dim StrFileName As String
Dim StrQryName As String
Dim StrSaveFile As String

StrFileName = strGetFileFolderName("Open Items" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO], 2, "Excel")
'Debug.Print StrFileName
StrQryName = "Open Item QRY"

If Len(StrFileName & "") < 1 Then
StrFileName = Application.CurrentProject.Path & "\Open Items" & Format(Date, "yyyymmdd") & ".xls"
End If

Debug.Print "My File is:" & StrFileName
DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True


'Set column widths
DoCmd.OpenModule "AutoFitColumns"

ExcelOpenItems_Click_Exit:

Exit Sub

ExcelOpenItems_Click_Err:
MsgBox Error$
Resume ExcelOpenItems_Click_Exit
End Sub

Sub AutoFitColumns()

Dim sht As Worksheet

'AutoFit Every Worksheet Column in a Workbook
For Each sht In ThisWorkbook.Worksheets
sht.Cells.EntireColumn.AutoFit
Next sht

On Error GoTo 0

End Sub

 

[Gasman]

I doubt it.
You have no reference to excel whatsoever.
You need to reference Excel, open the workbook, select the worksheet, then autofit. Then save and close workbook, close Excel and set the objects to Nothing.

Also if you need to do this, you could try CopyFromRecordset as well, as TransferSpreadsheet is great when you do not have to open the Excel file, but now you do.?

 

[The_Doc_Man]

Gasman's answer is spot on. All I can add is that the properties of the sheet are not available unless you create an Excel object and actually open the workbook to select a sheet. Access is at least semi-object-oriented so there needs to be an appropriately opened object for it to find things like cells and columns in a worksheet.