Hi,

I know how to use the transferspreadsheet command in VBA, but what I would like to know is: when I export some data to excell and then I want to read the data I exported in Excell, some cells or not big enough to contain the information, so I always have to resize the cells when I open this excell-file. Is it possible to use a code (or any other way) that could help me to make sure the cell is already that big that the longest data form every column fits the cell so I don't have to resize it every time?

thanks

Try opening a recordset, looping thru, and then use Autofit

Dim StrWorksheet as string
Dim StrWorksheetPath as string
Dim appExcel as excel.Application
Dim StrTemplatePath as string
Dim i as integer
Dim bks as Excel.Workbooks
Dim Wks as Excel.Worksheet
Dim rs as recordset

Set appExcel = New Excel.Application
StrTemplatePath = "C:\YrExcelPath"
StrWorksheet = "The_Name_of_Your_Workbook.xls"
StrWorkSheetPath = StrTemplatePath & StrWorksheet
Set bks = appExcel.workbooks

'Open the workbook
bks.open strWorksheetPath
Set wks = appExcel.Sheets("Report")
appExcel.DisplayAlerts = False

With wks

'----Delete prior data-----
wks.Range("A:DD").Delete

'----loop thru and add data to excel----
Set rs = currentdb.openrecordset("MyQry")

For i = 1 to rs.recordcount
wks.Range("A1").offset(i,0).value = rs!Type
wks.Range("B1").offset(i,0).value = rs!Type
rs.movenext
Next i

rs.Close


'----AutoFit your Columns-----
Wks.Range("A:DD").Columns.Autofit

appExcel.Application.Visible = True
appExcel.DisplayAlerts = True
End with

PS - you have to have Microsoft Excel 11.0 Object Library referenced:

Tools/Refrence/then scroll down and select