时间戳

首页 > 解决方案 > 在多个模块中使用相同的工作簿

excel - 在多个模块中使用相同的工作簿

问题描述

我有一个带有两个按钮的工作簿。因此用户可以按下它们来打开两个工作簿（输入和输出）。然后他将数据从输入复制到输出。我的问题是我不知道如何为我的潜艇定义相同的工作簿。 在此处输入图像描述

这是打开文件的代码：

Public wb1 As Workbook
Public wb2 As Workbook
Public result As Integer
Public fDialog As FileDialog

Public Sub inputs()
Set fDialog = Application.FileDialog(msoFileDialogOpen)
Set control = Workbooks("Control.xlsm").Worksheets("Control")
fDialog.Title = "Select a file"

fDialog.Title = "Select a file"

 If fDialog.Show = -1 Then
    If Right(fDialog.SelectedItems(1), 5) = ".xlsx" Or Right(fDialog.SelectedItems(1), 4) = ".xls" Then
        Set wb1 = Workbooks.Open(fDialog.SelectedItems(1))
        control.Cells(6, 2) = fDialog.SelectedItems(1)
    Else
        MsgBox ("Please select an excel file")
        Exit Sub
    End If
 End If
End Sub

这是复制数据的另一个子：

Public Sub stack()
 For Each ws In wb1.Worksheets
  '---here is the code for copying data---
 Exit For 
Exit Sub

当我运行这段代码时，它当然会给我这个错误“对象变量或未设置块”。你知道我该如何解决这个问题吗？如何为两个潜艇使用相同的 wb1？

标签： excelvba

解决方案

最好尽可能限制变量的范围，因此您可以这样做：

Public Sub stack()
    Dim wbControl As Worksheet
    Set wbControl = Workbooks("Control.xlsm").Worksheets("Control")

    'Check if there is possible input path in cell B6
    If wbControl.Cells(6, 2).Value2 = vbNullString Then
        MsgBox "Provide the Input workbook path first!"
        Exit Sub
    End If
    
    'Check if there is possible output path in cell B6
    If wbControl.Cells(6, 2).Value2 = vbNullString Then
        MsgBox "Provide the Output workbook path first!"
        Exit Sub
    End If
    
    'More error checking - e.g. check if both path are valid (file exist?)
            
    Dim wbInput As Workbook
    Set wbInput = Workbooks.Open(wbControl.Cells(6, 2).Value2) 'Input path in cell B6
    
    Dim wbOutput As Workbook
    Set wbOutput = Workbooks.Open(wbControl.Cells(6, 5).Value2) 'Output path in cell E6
    
    Dim ws As Worksheet
    For Each ws In wbInput.Worksheets
        'Do whatever you want in here
    Next ws
    
    'Remember to close the workbook if not needed later
End Sub

从您的子程序中删除此行inputs（以及用于选择输出文件的类似行）

Set wb1 = Workbooks.Open(fDialog.SelectedItems(1))

推荐阅读