Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting multiple rows from access to excel, each in a new row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting multiple rows from access to excel, each in a new row
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting multiple rows from access to excel, each in a new row
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 "sam" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows of Data in multiple Excel Sheets upon importing to Access | Excel Programming | |||
Limited in Exporting Access to Excel to 65,000 Rows | Excel Discussion (Misc queries) | |||
Exporting data from ms-access database to multiple worksheets using ASP | Excel Programming | |||
Exporting subset of rows to multiple files | Excel Programming |