Hi

You miss a sheet reference:

Set newWks = exApp.Workbooks.Open(pathAndFile)

With newWks.worksheets("MySheet") ' Change to suit
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Regards,
Per

"sam" skrev i meddelelsen
...
Hi All,

I am trying to insert records from access form into excel sheet, So
basically each new form entry ( a ribon of data in access) to populate a
new
row in excel sheet.
The is my code so for.. I I am getting an error "Object doesnt support
this
property or method" on this line:

Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)

Here is my code for the process:

Private Sub ExportToExcel_Click()

Dim exApp As Object
Dim exl As Object
Dim fdialog As FileDialog
Dim pathAndFile As String
Dim filePath As String
Dim shortName As String
Dim newWks As Workbook
Dim DestCell As Range
Dim FName As String

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

On Error Resume Next
Set exApp = GetObject(, "Excel.Application")

exApp.Visible = True

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"

End If
End With


Set newWks = exApp.Workbooks.Open(pathAndFile)


With newWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With DestCell
.Value = Me.Student_Id.Value
.Offset(0, 1).Value = Me.FirstName.Value
.Offset(0, 2).Value = Me.LastName.Value
End With

With newWks.Parent
.SaveAs _
FileName:="C:\My Documents\" & "Test" & ".xls", _
FileFormat:=xlWorkbookNormal
.Close savechanges:=False
End With

exApp.Quit
Set exl = Nothing
Set exApp = Nothing

End Sub

Thanks in Advance