can't edit Excel

[qwertyjjj]

I'm trying to enter some values in some cells in an Excel spreadsheet as soon as it has been created but the following code doesn't seem to do anything (no errors either).

Am I missing a reference:

Private Sub cmd_Export_Click()
DoCmd.OutputTo acOutputForm, "Form1", acFormatXLS, "C:\Form1.xls", False
Dim objXL As Object

On Error Resume Next

Set objXL = GetObject(, "Excel.Application")
Set objXL = GetObject("C:\Form1.xls")

objXL.Application.Visible = True
objXL.Parent.Windows(1).Visible = True

objXL.Sheets("Form1").Select
objXL.Range("D1").Select
objXL.activecell.Value = 5
End Sub

 

[cuttsy]

(no errors either).....

On Error Resume Next

If you take out 'On Error Resume Next' then you might get an error.

 

[qwertyjjj]

oops.
Says ActiveX component can't create object on line:
Set objXL = GetObject(, "Excel.Application")

 

[cuttsy]

Well I know nothing about working with Excell because I avoid it like the plague but these 2 lines seem a bit odd.

Set objXL = GetObject(, "Excel.Application")
Set objXL = GetObject("C:\Form1.xls")

Have you tried this instead.

Set objXL = GetObject("C:\Form1.xls", "Excel.Application")

This is a complete stab in the dark so it may make no odds.

 

[boblarson]

If you look in the Access help file it would seem to bear out what Cuttsy has written.

If Excel is registered on the machine, you can open with just:

Set objXL = GetObject("C:\Form1.xls")

If you want to use the full syntax, including the optional pathname, then Cuttsy's method:

Set objXL = GetObject("C:\Form1.xls", "Excel.Application")

would be the correct syntax.

 

[qwertyjjj]

Thanks.
Set objXL = GetObject("C:\Form1.xls", "Excel.Application")
I tried that and get the error File name or class name not found during Automation operation

STarngely enough, if I leave out the Excel.Application it works fine but then it seems to register it as an older version of Excel. So, when I try using things like Worksheet.Selection etc. it throws more errors

Edit: This is Access and Excel 2003 BTW

 

[cuttsy]

Like i said I dunno about VBA and excel but you might need to include a reference.

 

[billyr]

Here is code I use to print spreadsheets from and access application. It works. You could modify this method for editing; I have done it but cannot find the code now.

Dim ExcelObj As Object, i As Integer
Set ExcelObj = CreateObject("Excel.Application")
DoCmd.SetWarnings (False)
With ExcelObj.Application
.Workbooks.Open (DocPath)
.Application.Visible = False
For i = 1 To PCount
.ActiveSheet.PrintOut
Next i
.Quit
End With
Set ExcelObj = Nothing
DoCmd.SetWarnings (True)


Hope this helps.

 

[billyr]

Here is code I use to print spreadsheets from an access application. It works. You could modify this method for editing; I have done it but cannot find the code now.

Dim ExcelObj As Object, i As Integer
Set ExcelObj = CreateObject("Excel.Application")
DoCmd.SetWarnings (False)
With ExcelObj.Application
.Workbooks.Open (DocPath)
.Application.Visible = False
For i = 1 To PCount
.ActiveSheet.PrintOut
Next i
.Quit
End With
Set ExcelObj = Nothing
DoCmd.SetWarnings (True)


Hope this helps.

 

[Brian1960]

Open Excel

And mine. NB you need to have the correct References!

Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "H:\PORs\POR Template.xlt"
objExcel.Visible = False
objExcel.Sheets("DATA INPUT").Select
objExcel.Range("F3").Select
objExcel.ActiveCell.Value = CStr(Me.VendorCode)
objExcel.Range("F5").Select
objExcel.ActiveCell.Value = CStr(Me.nextPORid)

I'd suggest you keep excel.visible = False as it speeds up the code dramatically.
Also make sure the fields types match.
The code is slightly different for creating a new worksheet.

objExcel.Workbooks.Add "H:\PORs\NewWorksheetName.xls"

Hope you get a result.
B