VBA：为另一个工作簿（不是来自）运行宏

debugcn 发表于 Dev

我有一本工作簿 (A)，其中有一个模块和一个子程序。该子程序从 Internet 下载一个 excel 文件（工作簿（B））并打开它。我面临的问题是找到一种方法从工作簿 (A) 中的子程序执行工作簿 (B) 中的子程序。

重申一下，我只在工作簿 (A) 中有我想要的子例程，并希望通过使用工作簿 (A) 中的 sub 将其应用于工作簿 (B)。

注意：在我的代码工作簿 (B) = Nuance Mobility JIRA.xls 中，工作簿 (B) 中需要执行的所需子例程是 removeColumns()。

我的代码可以在下面找到：

Public Sub DL()

Dim WebUrl As String
Dim x As Workbook
Dim z As Workbook
Dim nmjexcel As String
Dim xlApp As Excel.Application

' I check to see if the file exists and delete it if it does
nmjexcel = "C:\Users\" & [A2] & "\Downloads\Nuance Mobility JIRA.xls"

If Len(Dir(nmjexcel)) <> 0 Then
    SetAttr nmjexcel, vbNormal
    Kill nmjexcel
End If

'I open chrome and download the file from an URL
WebUrl = [J1]
Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url " & WebUrl)


Application.Wait (Now + TimeValue("0:00:3"))

'I create a new 'hidden' excel app and open workbook (B)
Set xlApp = New Excel.Application
xlApp.Visible = False

Set x = Workbooks.Open("C:\Users\" & [A2] & "\Downloads\Nuance Mobility JIRA.xls")

' I delete some rows, a picture and some columns. 
' It's here that i would like my other subroutine, removeColumns(), to take place !

With x.Sheets("general_report")

    .Rows("1:3").Delete
    .Shapes.Range(Array("Picture 1")).Delete
    .Cells.UnMerge
    .Range("A:A,D:D,E:E,F:F,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P").Delete Shift:=xlToLeft

End With

'Then I copy whats left and paste it into workbook (A)
Set z = ThisWorkbook

Application.ScreenUpdating = False

x.Sheets("general_report").Range("A1").CurrentRegion.Copy

z.Sheets(1).Range("A13").PasteSpecial xlValues


x.Save
x.Application.CutCopyMode = False
x.Close

End Sub

我想要执行的子程序如下

Sub removeColumns()

Dim rng As Range 'store the range you want to delete
Dim c 'total count of columns
Dim I 'an index
Dim j 'another index
Dim headName As String 'The text on the header
Dim Status As String 'This vars is just to get the code cleaner
Dim Name As String
Dim Age As String
Dim sht As Worksheet

Rows("1:3").Delete


Key = "Key"
Summary = "Summary"
Status = "Status"
Set sht = Sheets("general_report")

sht.Activate 'all the work in the sheet "Incidents"
c = Range("A1").End(xlToRight).Column
'From A1 to the left at the end, and then store the number
'of the column, that is, the last column
j = 0 'initialize the var
For I = 1 To c 'all the numbers (heres is the columns) from 1 to c
    headName = Cells(1, I).Value
    If (headName <> Key) And (headName <> Summary) And (headName <> Status) Then
    'if the header of the column is differente of any of the options
        j = j + 1 ' ini the counter
        If j = 1 Then 'if is the first then
            Set rng = Columns(I)
        Else
            Set rng = Union(rng, Columns(I))
        End If
     End If
Next I
rng.Delete 'then brutally erased from leaf
End Sub

非常感谢您提前！

进一步的问题：

1）有没有办法隐藏下载的excel？

我有：

Set xlApp = New Excel.Application

xlApp.Visible = False

Set x = Workbooks.Open("C:\Users\" & [A2] & "\Downloads\Nuance Mobility JIRA.xls")

但是如果我使用 x= xlApp .Workbooks.Open 它会给我一个错误“下标超出范围”并突出显示：

Set sht = Sheets("general_report")

我试着做

Dim xlApp as Excel.Application)
...
Set sht = xlApp.Sheets("general_report")

但它得到更多的错误

2) 更一般地说，他们是否可以将注意力集中在我的工作簿 (A) 上，以便当 chrome 下载工作簿 (B) 时，chrome 窗口不会在前面弹出？

蒂姆·施密特

您面临的问题是因为您没有直接解决所需的工作表/工作簿，而是始终使用不应该使用的Selected工作表。不清楚，如果直接引用也可以很简单。

参考worbookB我在 sub 中添加了一个参数removeColumns，因此您可以传递所需的工作簿。

在 sub 中，您只需要在使用工作表的任何地方使用参考。

所以，而不是仅仅写：

somVariable = Cells(1,1).Value 'This always refers to the 'Selected' worksheet

你必须写：

someVariable = myWorkbook.myWorksheet.Cells(1,1).Value

'or to use the parameter wb like i did in your code:

someVariable = wb.Sheets(1).Cells(1,1).Value
'Here the first sheet of this workbook will be used

'You also can use the 'With' statment here:
With wb.Sheets(1)
    someVariable = .Cells(1,1).Value 'Note the dot in font of the 'Cells'
End With

因此，要在您的示例中使用这些知识，您应该尝试更改代码，如下所示：

/////////////////////////////////////////////////////////////////////////  
Set xlApp = New Excel.Application

xlApp.Visible = False
xlApp.Workbooks.Open("C:\Users\" & [A2] & "\Downloads\Nuance Mobility JIRA.xls")

Set x = xlApp.Workbooks(1)

Call removeColumns(x)

/////////////////////////////////////////////////////////////////////////

Sub removeColumns(ByVal wb As Workbok)

...    

'Always when you are referring to the workbook, you have to use the reference passed as parameter
wb.Sheets("general_report").Rows("1:3").Delete
'In you code the first three rows will always be deleted from the 'Selected' sheet and not the one you are working on later, the 'general_report'
...

Set sht = wb.Sheets("general_report") 

'Also don´t activate() sheet here, youst directly refer to it later
'sht.Activate 'all the work in the sheet "Incidents"

'You can directly refer t it over the variable you created, like this:
c = sht.Range("A1").End(xlToRight).Column
'From A1 to the left at the end, and then store the number
'of the column, that is, the last column

j = 0 'initialize the var
For I = 1 To c 'all the numbers (heres is the columns) from 1 to c
    headName = sht.Cells(1, I).Value
    If (headName <> Key) And (headName <> Summary) And (headName <> Status) Then
    'if the header of the column is differente of any of the options
        j = j + 1 ' ini the counter
        If j = 1 Then 'if is the first then
            Set rng = sht.Columns(I)
        Else
            Set rng = Union(rng, sht.Columns(I))
        End If
     End If
Next I

rng.Delete 'then brutally erased from leaf
End Sub

希望我能帮上忙，如果还有什么不清楚的可以随时问。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。