用用户表单打开多个excel文件 [英] Open multiple excel files with user form

问题描述

在vba中编写一个代码，将一个excel文件的用户表单调用到所有其他10个excel文件，而不在这10个excel文件中提供任何参考。

它正在显示当前Excel文件中的输出，但不显示在目标文件中，并显示错误，因为Userform已经显示，并且显示窗体模式不可能

  Private Sub Workbook_OnClick（）
 Dim mypath As String 
 Dim file As Workbook 
 Dim wb As Workbook 
 Dim pat As String 
 Application.ScreenUpdating = False 
 ChDriveC：
 ChDirC：\Users\Administrator\Desktop\John
'john是桌面上的一个文件夹
 mypath = Range B1）。值
'mypath具有与chDir相同的值
 file = Dir（mypath&\&* .xlsx）
设置wb =应用程序。 Workbooks.Open（file）
如果（wb.Click）然后
 Application.Visible = False 
 userform1.Show 
结束如果
 End Sub 
  

提到chDir是因为使用dir（）功能是C：\Users\Administrator\Documents\但保存在桌面上的文件夹是C：\Users\Administrators\Desktop\John

主席先生，它正在显示运行时间错误 - 91是对象变量或块变量未设置，并突出显示file = Dir（mypath& \& * .xlsx）

解决方案

  Private Sub Workbook_OnClick（）
 Dim mypath As String 
 Dim file As String 
 Dim wb As Workbook 
 Dim pat As String 
 Application.ScreenUpdating = False 
 ChDriveC：
 ChDirC：\Users\Administrator\Desktop\John
'john是桌面上的一个文件夹
 mypath = Range（B1）。值
'mypath has与chDir相同的值
 file = Dir（mypath&\&* .xlsx）
尽管文件
设置wb =应用程序。 Workbooks.Open（file）
如果不是IsEmpty（wb）然后
 Application.Visible = False 
 userform1.Show 
 End If 
 wb.Close 
 file = Dir（）
循环
 End Sub 
  

Write a code in vba that, Calling user form of one excel file to all the other 10 excel files without having any reference in those 10 excel files.

It is displaying the output in current excel file but not in the destination files and shows the error as Userform is already shown and showing form modally is not possible

Private Sub Workbook_OnClick()
    Dim mypath As String
    Dim file As Workbook
    Dim wb As Workbook
    Dim pat As String
    Application.ScreenUpdating = False
    ChDrive "C:"
    ChDir "C:\Users\Administrator\Desktop\John"
    'john is a folder on the desktop
    mypath = Range("B1").Value
    'mypath has the same value as chDir
    file = Dir(mypath & "\" & "*.xlsx")
    Set wb = Application.Workbooks.Open(file)
    If (wb.Click) Then
        Application.Visible = False
        userform1.Show
    End If
End Sub 

chDir is mentioned because the default directory shown with the dir() function was C:\Users\Administrator\Documents\ but the folder saved in desktop and that is C:\Users\Administrators\Desktop\John

Sir, It is displaying the run time error - 91 that is "Object variable or with block variable is not set" and highlighting the line "file = Dir(mypath & "\" & "*.xlsx")"

解决方案

Private Sub Workbook_OnClick()
    Dim mypath As String
    Dim file As String
    Dim wb As Workbook
    Dim pat As String
    Application.ScreenUpdating = False
    ChDrive "C:"
    ChDir "C:\Users\Administrator\Desktop\John"
    'john is a folder on the desktop
    mypath = Range("B1").Value
    'mypath has the same value as chDir
    file = Dir(mypath & "\" & "*.xlsx")
    Do While file <> ""
        Set wb = Application.Workbooks.Open(file)
        If Not IsEmpty(wb) Then
            Application.Visible = False
            userform1.Show
        End If
        wb.Close
        file = Dir()
    Loop
End Sub

这篇关于用用户表单打开多个excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！