从MS Access窗体到Excel中复制数据 [英] Copying data from a MS Access form into Excel

问题描述

我有code，它需要从MS访问形式复制的数据字段，为保存Excel文件。在访问的第一条记录在导入到Excel射程A2的：I2。在访问的第二个记录导入到Excel与一系列A3的：I3，等等....如果我在访问结束我的形式，打开它回来了，说我已经有了两个进口记录目前是什么情况，现在是到同样的Excel文件，现在我想添加第三个记录，它将重新开始在第一行（A2：I2），写了什么已经存在。我的问题是我怎么能，如果我关闭和打开访问保持它从（A2：I2）重新开始，而是开始下一个可用行，要遵守给出将是（A4：I4）的例子吗？这是code我有

 私人小组Command73_Click（）
设置objXLApp =的CreateObject（Excel.Application）
设置objXLBook = objXLApp.Workbooks.Open（Y：\ 123files \爱德蒙\酒店预订Daily.xls）
objXLApp.Application.Visible = TRUE

随着objXLBook.ActiveSheet

集合R = .usedRange
I = r.Rows.Count + 1

.Cells第（i + 1，1）。价值= Me.GuestFirstName＆安培; 与＆amp; GuestLastName
.Cells第（i + 1，2）。价值= Me.PhoneNumber
.Cells第（i + 1，3）。价值= Me.cboCheckInDate
.Cells第（i + 1，4）。价值= Me.cboCheckOutDate
.Cells第（i + 1，5）。价值= Me.GuestNo
.Cells第（i + 1，6）。价值= Me.RoomType
.Cells第（i + 1，7）。价值= Me.RoomNumber
.Cells第（i + 1，8）。价值=日期
.Cells（1 + 1,9）。价值= Me.Employee
结束与

集合R =什么
设置objXLBook =什么
设置objXLApp =什么

结束小组
 

解决方案

您可以得到上次使用的一行：

 集R = objXLBook.ActiveSheet.UsedRange
I = r.Rows.Count + 1
 

一些注意事项。

 私人小组Command73_Click（）
''它始终是放在命令按钮明智的名字是个好主意。
''它可能看起来不太像今天的问题，但它会到达那里
昏暗objXLApp作为对象
昏暗objXLBook作为对象
昏暗为r对象
昏暗我作为整数

''这是几乎总是最好先检查Excel中是否已打开之前
''打开另一个副本。
设置objXLApp =的CreateObject（Excel.Application）
设置objXLBook = objXLApp.Workbooks.Open（_
      Y：\ 123files \爱德蒙\酒店预订Daily.xls）
objXLApp.Application.Visible = TRUE

''这是一般最好到指定表
''在objXLBook.ActiveSheet

随着objXLBook.Sheets（客房预订）

    如果使用范围包括空行
    ''它可能不适合
    ''集合R = .UsedRange
    我= r.Rows.Count + 1

     ''从评论，看来数据是致密
     ''但与一些空行于片材的端

     I = .Range（A1）。完（xlDown）.Row +1

    .Cells（ⅰ，1）。价值= Me.GuestFirstName＆安培; 与＆amp; GuestLastName
    .Cells（1,2）。价值= Me.PhoneNumber
    .Cells（I，3）。价值= Me.cboCheckInDate
    .Cells（一，四）。价值= Me.cboCheckOutDate
    .Cells（I，5）。价值= Me.GuestNo
    .Cells（I，6）。价值= Me.RoomType
    .Cells（一，7）。价值= Me.RoomNumber
    .Cells（ⅰ，8）。价值=日期
    .Cells（I，9）。价值= Me.Employee

结束与

''打扫
设置objXLBook =什么
设置objXLApp =什么

结束小组
 

您可能也喜欢看转让preadsheet。

另一种可能性是从一种形式，或任何记录用记录集，供数据，对这一问题。它不给比较的相同的控制，但它是非常快：

 昏暗objXLApp作为对象
昏暗objXLBook作为对象
昏暗为r对象
昏暗我作为整数
昏暗的RS作为DAO.Recordset

设置objXLApp =的CreateObject（Excel.Application）
objXLApp.Visible = TRUE
设置objXLBook = objXLApp.Workbooks.Open（_
      Y：\ 123files \爱德蒙\酒店预订Daily.xls）

设置RS = Me.RecordsetClone
随着objXLBook.Sheets（工作表Sheet1）

    集合R = .UsedRange
    I = r.Rows.Count + 1

    .Cells（ⅰ，1）.CopyFromRecordset RS

结束与
 

I have code that takes fields from a MS Access form and copies the data into a saved Excel file. The first record in Access in imported to Excel with a range of A2:I2. The second record in Access is imported to Excel with a range of A3:I3, and so on.... What currently happens now is if I close my form in Access and open it back up, and say I already had two records imported into this same Excel file, and now I want to add a third record, it will start over at the first row (A2:I2) and write over what is already there. My question is how can I, if I close and open Access keep it from starting over on (A2:I2), and instead start at the next available row, which to follow the example given would be (A4:I4)? This is the code I have

Private Sub Command73_Click()
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("Y:\123files\Edmond\Hotel Reservation Daily.xls")
objXLApp.Application.Visible = True

With objXLBook.ActiveSheet

Set r = .usedRange
i = r.Rows.Count + 1

.Cells(i + 1, 1).Value = Me.GuestFirstName & " " & GuestLastName
.Cells(i + 1, 2).Value = Me.PhoneNumber
.Cells(i + 1, 3).Value = Me.cboCheckInDate
.Cells(i + 1, 4).Value = Me.cboCheckOutDate
.Cells(i + 1, 5).Value = Me.GuestNo
.Cells(i + 1, 6).Value = Me.RoomType
.Cells(i + 1, 7).Value = Me.RoomNumber
.Cells(i + 1, 8).Value = Date
.Cells(i + 1, 9).Value = Me.Employee
End With

Set r = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

End Sub

解决方案

You can get the last used row:

Set r = objXLBook.ActiveSheet.UsedRange
i = r.Rows.Count + 1

Some notes.

Private Sub Command73_Click()
''It is always a good idea to put sensible names on command buttons.
''It may not seem like much of a problem today, but it will get there
Dim objXLApp  As Object
Dim objXLBook  As Object
Dim r As Object
Dim i As Integer

''It is nearly always best to check whether Excel is open before 
''opening another copy.
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open( _
      "Y:\123files\Edmond\Hotel Reservation Daily.xls")
objXLApp.Application.Visible = True

''It is generally best to specify the sheet
''With objXLBook.ActiveSheet

With objXLBook.Sheets("Room Reservation")

    ''If the used range includes empty rows 
    ''it may not suit
    ''Set r = .UsedRange
    ''i = r.Rows.Count + 1

     ''From comments, it appears that the data is dense
     ''but with a number of empty rows at the end of the sheet

     i = .Range("A1").End(xlDown).Row + 1

    .Cells(i, 1).Value = Me.GuestFirstName & " " & GuestLastName
    .Cells(i, 2).Value = Me.PhoneNumber
    .Cells(i, 3).Value = Me.cboCheckInDate
    .Cells(i, 4).Value = Me.cboCheckOutDate
    .Cells(i, 5).Value = Me.GuestNo
    .Cells(i, 6).Value = Me.RoomType
    .Cells(i, 7).Value = Me.RoomNumber
    .Cells(i, 8).Value = Date
    .Cells(i, 9).Value = Me.Employee

End With

''Tidy up
Set objXLBook  = Nothing
Set objXLApp = Nothing

End Sub

You might also like to look at TransferSpreadsheet.

Another possibility is to use the RecordsetClone, for data from a form, or any recordset, for that matter. It does not give quite the same control, but it is very fast:

Dim objXLApp As Object
Dim objXLBook As Object
Dim r As Object
Dim i As Integer
Dim rs As DAO.Recordset

Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = True
Set objXLBook = objXLApp.Workbooks.Open( _
      "Y:\123files\Edmond\Hotel Reservation Daily.xls")

Set rs = Me.RecordsetClone
With objXLBook.Sheets("Sheet1")

    Set r = .UsedRange
    i = r.Rows.Count + 1

    .Cells(i, 1).CopyFromRecordset rs

End With

这篇关于从MS Access窗体到Excel中复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！