本帖最後由 jimmyhk 於 2011-9-23 13:41 編輯
現在這段程式碼可以給我把在 Sheets("Input") 指定的資料複製到 Sheet("Databse") 去,
但我試了很多路徑組合都未能把目的地儲存到另一個 xls 檔案內.
有否辦法達成?- Sub SaveRecord()
- Dim formSheet As Worksheet, dataSheet As Worksheet, targetRange As Range, i As Long
- Dim dataAddressList(), newRecord As Range, tmpRecord As Range
- Set formSheet = Sheets("Input")
- Set dataSheet = Sheets("Database")
-
-
- dataAddressList = Array("A24", "A23", "B16", "C16", "D16", "C25", "E25", "E24", "F23", "C28", "E28", "E27", "F26", "J25", "L25", "L24", "M23", "J28", "L28", "L27", "M26", "J31", "L31", "L30", "N29", "C31", "E31", "E30", "G29", "J34", "L34", "L33", "N32", "C34", "E34", "E33", "G32", "Q25", "S25", "S24", "A72", "B72", "C72", "A73", "B73", "C73", "A74", "B74", "C74", "A75", "B75", "C75", "A76", "B76", "C76", "A77", "B77", "C77", "A78", "B78", "C78", "A79", "B79", "C79", "A80", "B80", "C80", "A81", "B81", "C81", "A82", "B82", "C82", "B60", "B61", "B63", "B64", "B65", "B66", "L58", "L59", "L60", "L61", "L62")
- Set targetRange = dataSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
- For i = 0 To UBound(dataAddressList)
- targetRange.Offset(0, i).Value = formSheet.Range(dataAddressList(i)).Value
- Next
-
- Set newRecord = dataSheet.Range(targetRange, targetRange.Offset(0, UBound(dataAddressList)))
- Set tmpRecord = newRecord.Offset(-1, 0)
- tmpRecord.Copy
- newRecord.PasteSpecial xlFormats
- Application.CutCopyMode = False
-
- Sheets("Database").Select
- Range("A1").Select
- Dim tmpNo As Integer, myrange As Range
- Set myrange = Sheets("Database").Columns(1)
- tmpNo = Application.WorksheetFunction.Max(myrange.Columns(1))
- Range("A" & Rows.Count).End(xlUp).Offset(1).Value = tmpNo + 1
- Sheets("Database").Select
- Range("A1").Select
- Sheets("Sheet1").Select
- Range("A1").Select
- ActiveWorkbook.Save
- End Sub
|
發表於 2011-9-8 21:07
| 
發表於 2011-9-9 13:36
| 
