error: Object doesnt support this property or method

[sam]

Hi All, Here is my code for transfering data from access form to excel sheet.
But I am getting this error "object doesnt support this property or method"
on:
With xl
..Range("City").Value = Me.City.Value
'.Range("A3").Value = Me.City.Value
End With

Here is the whole code:

Private Sub ExportToExcel_Click()

Dim exApp As Object
Dim xl As Object
Dim fdialog As FileDialog
Dim pathAndFile As String
Dim filePath As String
Dim shortName As String

filePath = "C:\My Documents\Sheet.xls"

Set exApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
Set exApp = CreateObject("Excel.Application")
End If
On Error GoTo 0

exApp.Visible = True

Dim FName As String

Set fdialog = exApp.FileDialog(msoFileDialogFilePicker)

With fdialog
.AllowMultiSelect = False
.Filters.Clear
.InitialFileName = filePath & "\*.xls*"

If .Show Then
pathAndFile = .SelectedItems(1)
shortName = Right(pathAndFile, _
Len(pathAndFile) - InStrRev(pathAndFile, "\"))
Else
MsgBox "User cancelled. Did not select a file"
GoTo PastCode
End If
End With

Set fdialog = Nothing

Set xl = exApp.Workbooks.Open(pathAndFile)

With xl
..Range("City").Value = Me.City.Value
'.Range("A3").Value = Me.City.Value
End With

exApp.Quit
Set xl = Nothing
Set exApp = Nothing
End Sub

Thanks in advance

 

[Jim Thomlinson]

You need a sheet reference. Your xl object is a workbook. To get to a range
you need to specify the sheet.

Workbooks contains sheets. Sheets contain ranges...

 

[sam]

Thanks Jim, I got that to work now.
One more thing that I need and am struggling with is:

On clicking the button from access to launch excel file then -->Populate it
--> Save it with different name.. I want to keep that excel file open and
keep on updating the sheet with records from access by clicking that button..
Once I am dont with updating the sheet I will manualy close it.. Is there a
way to keep this sheet open until I am dont updating it with records in a new
row? Here is the code for that. But it doesnt work as I want it to.

Set wst = Worksheets("sheet1")

With wst
..Range("A1").Value = Me.City.Value

End With

r = r + 1

FName = "C:\My Documents\" _
& "Address" & ".xls"

wst.SaveAs FileName:=FName


Hope I made it clear.

Thanks in Advance