Private Sub CommandButton10_Click ()
Dim the fs, f, f1, fc, s' untyped variables, namely the variant type
Set the fs=CreateObject (" Scripting. FileSystemObject ") file system objects' call
Dim fd As FileDialog
The Set of fd=Application. FileDialog (msoFileDialogFolderPicker) 'open dialog
Fd. Show 'Show open dialog
S=fd. SelectedItems (1) 'choice to deal with the file
The Set f=fs. GetFolder (s) 'access to the folder
File was generated Set fc=f.F iles'
Dim n, k, k1, I, j, m, k2
'n=0' ready to iterate through all the files, starting from zero, n for pointer
'm=1 m for the control pointer
For Each formula In fc 'loop' file from file set fc f1
If LCase (Right (f1. Name, 3)) & lt;> "XLS" Then GoTo nt 'if not excel file, ignore it. Turn LCase big lowercase
Workbooks. Open f1. The Name 'Open it. Open (closed) under the selected folder each file
K1=NO_SPACE_HANG (" 9 ") 'custom function
'the Public Function NO_SPACE_LIE (k As String) As an Integer
K2=NO_SPACE_LIE (" 9 ") '
' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
'k=3' 1, 2 columns in this book have assignment, starting from the third column do
For I=2 To k1 'final value can only replace with approximate values, because no suitable variable
M=m + 1
For j=1 To k2
Workbooks (1) Sheets (" 9 ".) Cells (m, j + 2)=Workbooks. (2) Sheets (" 9 ".) Cells (I, j) 'Name to extract the needed data: the data in a new open file
Next j
Workbooks (1) Sheets (" 9 ".) Cells (m, 1)=Workbooks (2). The Name 'to extract the needed data: open a new file Name
Workbooks (1) Sheets (" 9 ".) Cells (m, 2)=Workbooks. (2) Sheets (" 9 "). The Name 'new open files to extract the needed data: 1 table Name
Next I
' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
Workbooks (2). The Close SaveChanges:=False 'Close the new open file, do not save
Nt: 'labels, line number
Next, 'the Next file
End Sub
1, a runtime error 1004 application definition or object definition error
Dim the fs, f, f1, fc, s' untyped variables, namely the variant type
2, clearly find file shows the file name and say that can't find the file
Workbooks. Open f1. The Name 'Open it. Open (closed) under the selected folder each file
CodePudding user response:
Run in Excel 2003,