ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð±Ð±NVBA ½X¹B¥Î¦bVB¤º°ÝÃD¡C

[µo°Ý] ½Ð±Ð±NVBA ½X¹B¥Î¦bVB¤º°ÝÃD¡C

§Ú·Q§â¥H¤U2²ÕVBA ªº¥N½X§Ë¶iVB ªº«ö¶s¤º,Åý§Ú«ö¤UVBªº«ö¶s´N¥i¥H°õ¦æVBA ªº°Ê§@,¦ý§Ú¦bVB¶}¤Fªí³æ«á,¦A³]©w¤F«ö¶s,¦P®É¤]¿ï¾Ü¤F¥LªºExcel Library 16 °Ñ¦Ò¡C
¦ý¬O§Ú¦bÂI¤F«ö¶s«á,­n©w¸q¥L¬OExcel ªºµ{§Ç,¥L¥u¬O¤Ï¥Õ¤F,¦n¹³¤£¦æ,©Ò¥H·Q½Ð±Ð¤j®a,¦pªG§Ú»Ý­n§âVBAªº·½½X§Ë¶iVBªº«ö¶s¤ºÀ³¸Ó®t¤°»ò,²{ªþ¤W¥N½Xµ¹¤j®a«ü±Ð¤@¤U,«D±`·PÁ¡C

VB¤ºªº±¡ªp¬O¥H¤U:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
         

        }
    }
}

§Úªº2²Õ­ì¥»VBA ·½½X¡C

²Õ1:

Sub unite_data()

Dim BookNum As Integer, rowNum As Integer, currentRow As Integer
Dim i As Integer
Dim j As Integer
Dim BookName As String



currentRow = 2
BookNum = Workbooks.Count
'MsgBox BookNum

For i = 1 To BookNum
BookName = Workbooks(i).Name
If BookName <> "Data.xlsx" Then

     For j = 1 To 1
      Workbooks(BookName).Activate
      
      rowNum = Workbooks(BookName).Worksheets("Sheet1").UsedRange.Rows.Count
      'MsgBox rowNum
         
      
      If (rowNum - 1 >= 1 And Workbooks(BookName).Worksheets("Sheet1").Cells(2, 1).Value <> "") Then
        Workbooks(BookName).Worksheets("Sheet1").Rows("15:" & rowNum).Select
        Selection.Copy
        Windows("Data.xlsx").Activate
        Rows(currentRow).Select
        ActiveSheet.Paste
        currentRow = currentRow + (rowNum - 14)
        Windows(BookName).Activate
      End If
     Next j

End If
   
Next i



Windows("Data.xlsx").Activate

Range("K2:K14").Value = "1"

Range("L2").Formula = "=J2"

[L2].Copy

With Range("L3:L" & [A65536].End(xlUp).Row).PasteSpecial

Range("M2").Formula = "=J2"

[M2].Copy

With Range("M3:M" & [A65536].End(xlUp).Row).PasteSpecial


End With

End With



End Sub



²Õ2:

Sub ¥¨¶°1()
Application.ScreenUpdating = False
LineNo = ActiveSheet.UsedRange.Rows.Count - 1
Sheets.Add.Name = "rr"
Sheets("AA").Rows(1).Copy Sheets("rr").Rows(1)
rrLow = 1
For i = 1 To LineNo
If Sheets("AA").Cells(i + 1, 7) > 1000 Then
n = WorksheetFunction.RoundUp(Sheets("AA").Cells(i + 1, 7) / 1000, 0)
For k = 1 To n
rrLow = rrLow + 1
Sheets("AA").Rows(i + 1).Copy Sheets("rr").Rows(rrLow)
If k <> n Then
Sheets("rr").Cells(rrLow, 7) = 1000
Else
Sheets("rr").Cells(rrLow, 7) = Sheets("AA").Cells(i + 1, 7) - 1000 * (k - 1)
End If
Sheets("rr").Cells(rrLow, 8) = k
Sheets("rr").Cells(rrLow, 9) = n
Next k
Else
rrLow = rrLow + 1
Sheets("AA").Rows(i + 1).Copy Sheets("rr").Rows(rrLow)
End If
Next i
End Sub

¦^´_ 2# stephenlee


    ¤£¦n·N«ä,¤£ª¾¹D¬O¤£¬O§Ú¿ï¿ù¤FVBªº±M®×¡C
©Ò¥H¥Lªºµ{¦¡½X¤£ª¾¹D¬O¤£¬O¥Î¿ù¤F¡CÀ³¸Ó¬OVBªº windowform,¦ý¬O§Ú³]©w¤F Dim Workbooks©MWorksheet ¥L³£»¡¨S¦³©w¸q,»{¤£¥X¥L¬OExcel ¡C
¦p¤U¹Ï§Ú¦bºô¸ô¤W§äªº¨Ò¤l,¸ò¥L¥´¤F¤§«á,¥X²{°ÝÃD¡CÁÂÁÂ


Å޿褣¤Ó¹ï¡A
oBook¤ÎoSheetÀ³¸Ó«Å§i¦¨Object
¦bVBA¤º¤]¬O¤@¼Ë¡A
§A©Ò©w¸qªº³o¨â­Óµü¤£ÄÝ©ó¦bµ{¦¡»y¨¥¤¤ªº¤@­ÓÄÝ©Ê¡A
­n¥ý©w¸q¥¦¬O¤@­ÓObjectµM«á¦b¥i¥H¦A§i¶Dµ{¦¡¤À§O°µ«ç¼Ëªº°õ¦æ¡C

­×§ï¦p¤U:
  1. Dim oExcel, oBook, oSheet As Object
  2. oExcel = CreateObject("Excel.Application")
  3. oExcel.Workbooks.Open("C:\temp\Exceltest.xlsx")
  4. oBook = oExcel.ActivateWorkbook
  5. oSheet = oExcel.Workbooks("Exceltest.xlsx").Worksheets(1)
  6. oSheet.Range("A1").Value = "test"

  7. '¦sÀÉ
  8. 'oExcel.Workbooks("Exceltest.xlsx").Save()
  9. 'Ãö³¬
  10. 'oExcel.Workbooks("Exceltest.xlsx").Close()
  11. 'oExcel.Application.Quit()
  12. 'GC.Collect()
½Æ»s¥N½X
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¥»©«³Ì«á¥Ñ stephenlee ©ó 2018-9-28 00:05 ½s¿è

¤£¦n·N«ä,¤£ª¾¹D¬O¤£¬O§Ú¿ï¿ù¤FVBªº±M®×¡C
©Ò¥H¥Lªºµ{¦¡½X¤£ª¾¹D¬O¤£¬O¥Î¿ù¤F¡CÀ³¸Ó¬OVBªº windowform,¦ý¬O§Ú³]©w¤F Dim Workbooks©MWorksheet ¥L³£»¡¨S¦³©w¸q,»{¤£¥X¥L¬OExcel ¡C
¦p¤U¹Ï§Ú¦bºô¸ô¤W§äªº¨Ò¤l,¸ò¥L¥´¤F¤§«á,¥X²{°ÝÃD¡CÁÂÁÂ


VB.jpg
2018-9-28 00:03

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD