Excel从按钮问题运行宏 [英] Excel Run macro from button issue
问题描述
当我从宏菜单或宏菜单中运行宏时,我创建了一个处理目录中的多个文件并生成CSV文件的宏。步入我没有问题。它通过所有文件和所有行(在文件中)。
但是当我将宏分配给一个按钮时,它会遍历所有文件,但在某些情况下失败行和CSV中的结果是不同的。
作品
无效
GOOD CSV:D
code> NAME,GROUP1,25,13
NAME1,GROUP1,25,17
NAME2,GROUP2,27,14
NAME3,GROUP2,28,16
NAME2 ,GROUP2,23,12
错误CSV D:
NAME,GROUP1 ,25,13
NAME1,GROUP1,25,17
,GROUP2,27,14
,GROUP2,23,12
更少的行和格式错误
我不认为它应该是宏的一部分,没有按钮就可以正常工作,但是我需要它。
我用
Application.ScreenUpdating = False
和
Application.ScreenUpdating = True
PD:对不起我的英文。
EDIT
我将按钮设置为另一个宏,以运行其他,但结果是一样的。
起始单,没关系。 (测试)
代码:
(无编辑)
Public Sub ProcesarTodo()
Application.ScreenUpdating = False
Dim Filename,Pathname As String
Dim wb As Workbook
Pathname = ActiveWorkbook.path& \Inscripciones\
Exportpath = ActiveWorkbook.path& \CSV\
ExportpathE = ActiveWorkbook.path& \CSV_E\
Filename = Dir(Pathname&* .xls)
answer = MsgBox(Eliminar archivos de carpeta CSV?,vbYesNo + vbQuestion, VACIAR CSV)
如果answer = vbYes然后
错误恢复下一步
Kill Exportpath& * .csv
Kill ExportpathE& * .csv
错误GoTo 0
如果
a = 0
rows = 0
rowsE = 0
Dim Data 1到1)As String
Dim Hojas(1到2)As String
数据(1)=Z
Hojas(1)=A
Hojas(2 )=B
etapa = 3
Do While Filename<>
设置wb = Workbooks.Open(路径名和文件名)
'CREAR SI NO EXISTE HOJA PARA ATLETAS
Dim mySheetName As String,mySheetNameTest As String
mySheetName =Procesar
On Error Resume Next
mySheetNameTest = Worksheets(mySheetName).name
如果Err.Number = 0然后
工作表(mySheetName).Cells.Clear
Else
Err.Clear
Worksheets.Add.name = mySheetName
End If
'该函数从文件的名称获取数据。
get_data
n = 1
对于每个Hoja在Hojas
表(Hoja)。选择
对于i = Cells(9,7).Value To Cells(9, 9).Value步骤2
对于j =单元格(10,3).Value到单元格(10,5).Value
如果单元格(j,i).Value =T或单元格j,i).Value =tor Cells(j,i).Value =Ror Cells(j,i).Value =rThen
Sheets(Procesar)。Cells n,1).Value = Sheets(Hoja).Cells(j,2).Value
Sheets(Procesar)。Cells(n,2).Value = equipo
Sheets(Procesar ).Cells(n,3).Value = Sheets(Hoja).Cells(11,i).Value
Worksheets(Hoja).Cells(j,i + 1).copy
Worksheets Procesar)。Cells(n,4).PasteSpecial Paste:= xlPasteValuesAndNumberFormats,Operation:= _
xlNone,SkipBlanks:= False,Transpose:= False
Sheets(Procesar)。Cells(n,5).Value = Sheets(Hoja).Cells(j,i).Value
Sheets(Procesar)。Cells(n,6) .Value = Sheets(Hoja).Cells(12,i).Value
n = n + 1
End If
Next j
Next i
Next Hoja
n = n - 1
表格(Procesar)选择
范围(H1)选择
让x = 1
尽管x< ; = n
ActiveCell.FormulaR1C1 == PROPER(RC [-7])
Selection.Offset(1,0)。选择
x = x + 1
循环
范围(H1:H&选择
Selection.copy
范围(A1)。选择
Selection.PasteSpecial粘贴:= xlPasteValues,操作:= xlNone,SkipBlanks _
:= False, Transpose:= False
范围(H1:H& n)。选择
Selection.ClearContents
'Exportar Atletas
调用Exportar(Exportpath,wb.name,n)
wb.Close SaveChanges:= False
文件名= Dir()
a = a + 1
循环'下一个文件
Application.ScreenUpdating = True
bat
mensaje = MsgBox(Se procesaron&archivos& vbNewLine&El cual son&&atletas& vbNewLine&Y & rowE&Entrenadores。& vbNewLine&Programa realizado por Tomas Prado,Listo)
End Sub
函数Exportar(path,
equipo = Replace(name,.xlsx,)
equipo2 = Replace(equipo,.xls,)
让Rango =A1: & F& n
ActiveSheet.Range(Rango)。选择
Selection.copy
Worksheets.Add.name =Exportar
范围(A1)。选择
ActiveSheet .Paste
Application.CutCopyMode = False
'Formato antiguo xlCSV
ActiveWorkbook.SaveAs文件名:= _
path& equipo2& .csv,FileFormat:= xlCSV,_
CreateBackup:= False
rows =行+ n
结束函数
只有一种方法无法正常工作。这是方法的错误位置。
有一件事要知道,您正在使用 Active X控件。因此,该按钮的单击方法必须在工作表模块中具有。我的意思是,你把你的按钮放在 Sheet1 中,你还需要把该按钮的事件方法放在 Sheet1的模块中。
还有方法名称必须是 Private Sub buttonName_Click()。因此, buttonName 不应该是按钮标签(例如,在ProcesarTodo中)。它应该像CommandButton1。您还可以在VBA编辑器的属性标签中更改该名称。如果您更改名称,方法名称应为 Private Sub ProcesarTodo_Click()
。
所以,检查你的代码和模块。你隐藏你的证据中的模块名称,所以我可以用提示来表示。如果您清楚地显示,我可以确切地说。
如果您不知道如何做,只需按照以下步骤:
- 右键单击您的按钮。
- 从列表中选择查看代码。
- 所以,你可以看到你的按钮的正确方法,复制并粘贴所有的方法体,从旧的到新的。
- 然后,测试一下。它会很好的工作。
I made a macro that processes multiple files in a directory and generated CSV files to take them a database.
When I run the macro from the Macros menu or "step Into" I have no problem. it Gos through all the files and all rows (in the files).
But when I assign the macro to a button, it goes through all the files but fails in some rows And the result in CSV is different.
Works
Does Not work
GOOD CSV :D
NAME , GROUP1, 25,13
NAME1, GROUP1, 25,17
NAME2, GROUP2, 27,14
NAME3, GROUP2, 28,16
NAME2, GROUP2, 23,12
WRONG CSV D:
NAME , GROUP1, 25,13
NAME1, GROUP1, 25,17
, GROUP2, 27,14
, GROUP2, 23,12
fewer rows and the wrong format
I don't think it should go a part of the macro, it works perfectly without the button, but I need it.
I tested with
Application.ScreenUpdating = False
And
Application.ScreenUpdating = True
PD: sorry for my english.
EDIT I set the button to another macro, to run the other, but the result is the same. Start sheet, it does not matter. (tested) THE CODE: (NO EDIT)
Public Sub ProcesarTodo()
Application.ScreenUpdating = False
Dim Filename, Pathname As String
Dim wb As Workbook
Pathname = ActiveWorkbook.path & "\Inscripciones\"
Exportpath = ActiveWorkbook.path & "\CSV\"
ExportpathE = ActiveWorkbook.path & "\CSV_E\"
Filename = Dir(Pathname & "*.xls")
answer = MsgBox("Eliminar archivos de carpeta CSV?", vbYesNo + vbQuestion, "VACIAR CSV")
If answer = vbYes Then
On Error Resume Next
Kill Exportpath & "*.csv"
Kill ExportpathE & "*.csv"
On Error GoTo 0
End If
a = 0
rows = 0
rowsE = 0
Dim Data(1 To 1) As String
Dim Hojas(1 To 2) As String
Data(1) = "Z"
Hojas(1) = "A"
Hojas(2) = "B"
etapa = 3
Do While Filename <> ""
Set wb = Workbooks.Open(Pathname & Filename)
' CREAR SI NO EXISTE HOJA PARA ATLETAS
Dim mySheetName As String, mySheetNameTest As String
mySheetName = "Procesar"
On Error Resume Next
mySheetNameTest = Worksheets(mySheetName).name
If Err.Number = 0 Then
Worksheets(mySheetName).Cells.Clear
Else
Err.Clear
Worksheets.Add.name = mySheetName
End If
' This function get data from the file's name.
get_data
n = 1
For Each Hoja In Hojas
Sheets(Hoja).Select
For i = Cells(9, 7).Value To Cells(9, 9).Value Step 2
For j = Cells(10, 3).Value To Cells(10, 5).Value
If Cells(j, i).Value = "T" Or Cells(j, i).Value = "t" Or Cells(j, i).Value = "R" Or Cells(j, i).Value = "r" Then
Sheets("Procesar").Cells(n, 1).Value = Sheets(Hoja).Cells(j, 2).Value
Sheets("Procesar").Cells(n, 2).Value = equipo
Sheets("Procesar").Cells(n, 3).Value = Sheets(Hoja).Cells(11, i).Value
Worksheets(Hoja).Cells(j, i + 1).copy
Worksheets("Procesar").Cells(n, 4).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Procesar").Cells(n, 5).Value = Sheets(Hoja).Cells(j, i).Value
Sheets("Procesar").Cells(n, 6).Value = Sheets(Hoja).Cells(12, i).Value
n = n + 1
End If
Next j
Next i
Next Hoja
n = n - 1
Sheets("Procesar").Select
Range("H1").Select
Let x = 1
Do While x <= n
ActiveCell.FormulaR1C1 = "=PROPER(RC[-7])"
Selection.Offset(1, 0).Select
x = x + 1
Loop
Range("H1:H" & n).Select
Selection.copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H1:H" & n).Select
Selection.ClearContents
' Exportar Atletas
Call Exportar(Exportpath, wb.name, n)
wb.Close SaveChanges:=False
Filename = Dir()
a = a + 1
Loop ' Next file
Application.ScreenUpdating = True
bat
mensaje = MsgBox("Se procesaron " & a & " archivos" & vbNewLine & "El cual son " & rows & " atletas" & vbNewLine & "Y " & rowsE & " Entrenadores." & vbNewLine & "Programa realizado por Tomas Prado", , "Listo")
End Sub
Function Exportar(path, name, n)
equipo = Replace(name, ".xlsx", "")
equipo2 = Replace(equipo, ".xls", "")
Let Rango = "A1:" & "F" & n
ActiveSheet.Range(Rango).Select
Selection.copy
Worksheets.Add.name = "Exportar"
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Formato antiguo xlCSV
ActiveWorkbook.SaveAs Filename:= _
path & equipo2 & ".csv", FileFormat:=xlCSV, _
CreateBackup:=False
rows = rows + n
End Function
There is only one way which cannot work. That is wrong place of your method.
One thing to know is you are using Active X control. So, click method for that button must have inside the sheet's module. I means, you putted your button in Sheet1, you need to also put the event methods of that button in Sheet1's module.
And also method name must be Private Sub buttonName_Click(). In that, buttonName should not be button label(eg. in your "ProcesarTodo"). It should be like "CommandButton1". You can also change that name in VBA Editor's Properties tag. If you change the name, you method name should be Private Sub ProcesarTodo_Click()
.
So, check your code and module again. You hide names of module in your evidence, so, I can say by hint. If you show clearly, I can say exactly.
If you don't know, how to do, just following steps:
- Right Click on your button.
- From the list, choose "View Code".
- So, you can see the right method for your button, copy and paste all method body from your old to that new one.
- And then, test about it. It will work perfectly.
这篇关于Excel从按钮问题运行宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!