[µo°Ý] §â¸ê®Æ¦s¨ì¥t¤@­Ó xls Àɮפº [¤w¸Ñ¨M] [ÁÂÁÂ] VBA, path

jimmyhk

²{¦b³o¬qµ{¦¡½X¥i¥Hµ¹§Ú§â¦b Sheets("Input") «ü©wªº¸ê®Æ½Æ»s¨ì Sheet("Databse") ¥h,
¦ý§Ú¸Õ¤F«Ü¦h¸ô®|²Õ¦X³£¥¼¯à§â¥Øªº¦aÀx¦s¨ì¥t¤@­Ó xls Àɮפº.

¦³§_¿ìªk¹F¦¨?

1. Sub SaveRecord()
   
2. 
   
3.     Dim formSheet As Worksheet, dataSheet As Worksheet, targetRange As Range, i As Long
   
4.     Dim dataAddressList(), newRecord As Range, tmpRecord As Range
   
5. 
   
6.     Set formSheet = Sheets("Input")
   
7.     Set dataSheet = Sheets("Database")
   
8. 
   
9.    
   
10.    
    
11. 
    
12.     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")
    
13. 
    
14. 
    
15.     Set targetRange = dataSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
    
16.         For i = 0 To UBound(dataAddressList)
    
17.         targetRange.Offset(0, i).Value = formSheet.Range(dataAddressList(i)).Value
    
18. 
    
19.     Next
    
20.    
    
21.     Set newRecord = dataSheet.Range(targetRange, targetRange.Offset(0, UBound(dataAddressList)))
    
22.     Set tmpRecord = newRecord.Offset(-1, 0)
    
23.     tmpRecord.Copy
    
24.     newRecord.PasteSpecial xlFormats
    
25.     Application.CutCopyMode = False
    
26.    
    
27.     Sheets("Database").Select
    
28.     Range("A1").Select
    
29. 
    
30. 
    
31.     Dim tmpNo As Integer, myrange As Range
    
32.     Set myrange = Sheets("Database").Columns(1)
    
33.     tmpNo = Application.WorksheetFunction.Max(myrange.Columns(1))
    
34.     Range("A" & Rows.Count).End(xlUp).Offset(1).Value = tmpNo + 1
    
35. 
    
36.     Sheets("Database").Select
    
37.     Range("A1").Select
    
38.     Sheets("Sheet1").Select
    
39.     Range("A1").Select
    
40.     ActiveWorkbook.Save
    
41. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 1# jimmyhk
°ÝÃD­n´y­z­«ÂI
¥t¤@xls¬O¤w¦s¦bÀɮשάO­n¥t¦s·sÀÉ?
­Y¬°¤w¦s¦bÀɮסA¬Oª½±µÂл\©Î±µÄò¸ê®Æ?

jimmyhk

¦^´_  jimmyhk
°ÝÃD­n´y­z­«ÂI
¥t¤@xls¬O¤w¦s¦bÀɮשάO­n¥t¦s·sÀÉ?
­Y¬°¤w¦s¦bÀɮסA¬Oª½±µÂл\©Î±µÄò ...
Hsieh µoªí©ó 2011-9-8 21:36

¤£¬O­n¥t¦s·sÀÉ,
¬O·Q§â¦³Ãö¸ê®Æ½Æ»s¨ì²{¦sªº excel ¤º±µÄò¸ê®Æ.

²{¦³ªº¥N½X¥u¥i¥H§â¸ê®Æ¥Ñ Sheet1 ½Æ»s¨ì sheet2,
¦ý¤£¯à¥Ñ file1 ªº sheet1 ½Æ»s¨ì file2 ªº sheet1 ¤º.

½Ð«ü±Ð

GBKEE

¦^´_ 3# jimmyhk
Set formSheet = WorkBooks("A").Sheets("Input")
Set dataSheet = WorkBooks("B").Sheets("Database")

jimmyhk

¦^´_  jimmyhk
Set formSheet = WorkBooks("A").Sheets("Input")
Set dataSheet = WorkBooks("B").Sheet ...
GBKEE µoªí©ó 2011-9-9 09:35

³o­Ó¤èªk§Ú¸Õ¹L¤F, §Ú¥H¥H¤Uªº»y¥y¿é¤J«á°õ¦æ

1.     Set dataSheet = Workbooks("Database.xls").Sheets("Database")

½Æ»s¥N½X

¥¦¦^ÂЦx¦æ¶¥¬q¿ù»~ '9',
°}¦C¯Á¤Þ¶W¥X½d³ò.


¥X¿ùªº­ì¦]¦b¨º¸Ì?

GBKEE

¦^´_ 5# jimmyhk
¬q¿ù»~ '9',°}¦C¯Á¤Þ¶W¥X½d³ò.   ´N¬OÀɮרS¥´¶}°Ú!!!!

1. 'ÀÉ®×¥´¶}ªºCode
   
2. With Workbooks.Open("Database.xls")
   
3.     Set dataSheet = .Sheets("Database")
   
4. End With

½Æ»s¥N½X

jimmyhk

¦^´_ 6# GBKEE


§ä¤£¨ì databse.xls.....
(§Ú¤w§â¥¦­Ì©ñ¦b¦P¤@¸ê®Æ§¨¤º)
¬O§_­n¥t¥[¸ô®|¼Æ¦¡?

GBKEE

¦^´_ 7# jimmyhk
¬O§_­n¥t¥[¸ô®| ³o¬O·íµMªº

jimmyhk

¦^´_ 6# GBKEE


§Ú¦¨¥\¤F
­ì¨Ó­n¦b path ¤W¤U¦h¤@ÂIÂI¥\¤Ò

1.     mypath = ThisWorkbook.Path
   
2.    
   
3. 
   
4.     With Workbooks.Open(mypath & "\" & "Database.xls")
   
5.     Set dataSheet = .Sheets("Database")
   
6. End With

½Æ»s¥N½X

ÁÂÁÂÀ°¦£~