C
Chris M.
I seem to be ahving massive trouble writing code in access to do anything in Excel. I'm fairly new to VBA, so please bear with me.
I'm trying to justify all the columns in a newly created Excel spreadsheet from the VB code in Access that created it. I can't seem to use Cell.EntireColumn.AutoFit without getting an error, and I do have the Excel 9.0 library loaded. How would I reference the Excel Workbook in order to carry out this type of command? Here's my current code. Thanks in advance.
Function ExportSheet()
'Export table to specified worksheet name.
Dim outputname As String
Dim objws As Excel.Worksheet
Dim objwb As Excel.Workbook
Set obj = New Excel.Application
obj.Visible = True
Dim strinput As String, tfile As String
strinput = Forms![print target closing]!fname
outputname = "G:\Evenflow\" & strinput & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Incentive Close Data", outputname, True
objwb.Application.Cells.EntireColumn.AutoFit 'This is the trouble line
'Protect specified cells of target worksheet.
tfile = "G:\evenflow\" & strinput & ".xls"
Set objwb = obj.workbooks.Open(tfile)
Set objws = objwb.ActiveSheet
objws.range("M1:N600").Locked = False
objws.protect password = "xetnec"
objwb.Save
objwb.Close
obj.Quit
Set obj = Nothing
Set objws = Nothing
Set objwb = Nothing
End Function
I'm trying to justify all the columns in a newly created Excel spreadsheet from the VB code in Access that created it. I can't seem to use Cell.EntireColumn.AutoFit without getting an error, and I do have the Excel 9.0 library loaded. How would I reference the Excel Workbook in order to carry out this type of command? Here's my current code. Thanks in advance.
Function ExportSheet()
'Export table to specified worksheet name.
Dim outputname As String
Dim objws As Excel.Worksheet
Dim objwb As Excel.Workbook
Set obj = New Excel.Application
obj.Visible = True
Dim strinput As String, tfile As String
strinput = Forms![print target closing]!fname
outputname = "G:\Evenflow\" & strinput & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Incentive Close Data", outputname, True
objwb.Application.Cells.EntireColumn.AutoFit 'This is the trouble line
'Protect specified cells of target worksheet.
tfile = "G:\evenflow\" & strinput & ".xls"
Set objwb = obj.workbooks.Open(tfile)
Set objws = objwb.ActiveSheet
objws.range("M1:N600").Locked = False
objws.protect password = "xetnec"
objwb.Save
objwb.Close
obj.Quit
Set obj = Nothing
Set objws = Nothing
Set objwb = Nothing
End Function