Exporting multiple rows from access to excel, each in a new row

[sam]

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

 

[Per Jessen]

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

 

[Patrick Molloy]

please see my earlier posting where I explained that you opened a workBOOK
and your WITH requires a workSHEET

so to repeat

please chage this
Set newWks = exApp.Workbooks.Open(pathAndFile)
to

Set newWkBk = exApp.Workbooks.Open(pathAndFile)

and add

Set newWks = newWkBk.Activesheet
or
Set newWks = newWkBk.worksheets(1)

and ensusre you DIM correctly....
dim newWkBk as workbook
dim newWks as worksheet