Sales reports, every day I have a system to automatically download push a, there are 30 a month, format, often to the sum of the every day in a sheet, there are currently using VBA statement summary, but need to open a workbook, by looking at the code was running, a little bit of a problem, I want to implement scenario is as follows:
1, statements are placed in the same folder
2, a button, click and automatically build a name in report in the folder "merge results. XLSX" watch
3, all folders automatically RiBiao in all the content of the sheet to new XLSX table in the sheet1
4, finally save close the merge result. XLSX

CodePudding user response:

 Private Sub CommandButton1_Click () 
Dim path As a String, the name As a String, the index As Long, wb As Workbook 
With the Application. The FileDialog (msoFileDialogFolderPicker) 
The Title="select statements in the folder" 
. InitialFileName=Application. DefaultFilePath 
The Show 
If. SelectedItems. Count & gt; 0 Then 
Path=. SelectedItems (1) & amp; "" 
The Else 
The Exit Sub 
End the If 
End With 
The index=1 
On the Error Resume Next 
Kill the path & amp; "Merge results. XLSX" 'prevent file already exists 
Err. Number=0 
On Error GoTo 0 
Name=Dir (path & amp; "*. *" xl, vbDirectory) 
The Set wb=Workbooks. Add 
The Do While Len (name) & gt; 0 
With Workbooks. Open (path & amp; Name) 
With the Sheets (" Sheet1 "). UsedRange 
. Copy wb. Sheets (" Sheet1 "). The Cells (index of 1) 
The index=. Rows. Count + index 
End With 
. Close False 
End With 
Name=Dir 
Loop 
Wb. SaveAs path & amp; "Merge results. XLSX 
"Wb. Close True 
End Sub 

CodePudding user response:

Can test in excel, performs tip in VB6.0 by use "wb As Workbook" this sentence "compiler error: user-defined types undefined", what's the solution?

CodePudding user response:

Vb6 did not contact, how do you try the following code to see

 Private Sub CommandButton1_Click () 
Dim path As a String, the name As a String, the index As Long, wb As Object, objExcel As Object 
The Set objExcel=CreateObject (" Excel. Application ") 
With objExcel. Application. FileDialog (msoFileDialogFolderPicker) 
The Title="select statements in the folder" 
. InitialFileName=Application. DefaultFilePath 
The Show 
If. SelectedItems. Count & gt; 0 Then 
Path=. SelectedItems (1) & amp; "" 
The Else 
The Exit Sub 
End the If 
End With 
The index=1 
On the Error Resume Next 
Kill the path & amp; "Merge results. XLSX" 'prevent file already exists 
Err. Number=0 
On Error GoTo 0 
Name=Dir (path & amp; "*. *" xl, vbDirectory) 
The Set wb=objExcel. Workbooks. Add 
The Do While Len (name) & gt; 0 
With objExcel. Workbooks. Open (path & amp; Name) 
With the Sheets (" Sheet1 "). UsedRange 
. Copy wb. Sheets (" Sheet1 "). The Cells (index of 1) 
The index=. Rows. Count + index 
End With 
. Close False 
End With 
Name=Dir 
Loop 
Wb. SaveAs path & amp; "Merge results. XLSX 
"Wb. Close True 
End Sub