VBA代码可打开文件夹中的所有Excel文件 [英] VBA code to open all excel files in a folder

问题描述

我正在使用vba，并且尝试根据单元格值打开文件夹(大约8-10)中的所有excel文件.我想知道这是否是打开它的正确方法，它使我在编写目录时始终出现语法错误.当我重写该部分时，vba只发射了msgbox，这意味着它必须循环播放并执行正确的操作?但没有打开任何文件.任何信息都会有所帮助.非常感谢你们花费时间以任何方式帮助我.

I was working with a vba and I'm trying to open all excel files in a folder (about 8-10) based on cell values. I was wondering if this is the right approach to opening it, it keeps giving me syntax error where I wrote the directory. and when I rewrote that section, the vba only shot out the msgbox which meant it had to have looped and did something right? but didn't open any files. Any information will help. Thank you guys so much for taking the time to help me in any way.

Sub OpenFiles()

Dim search As Worksheet
Dim customer As Range
Dim customerfolder As Range

Dim QualityHUB As Workbook

'setting variable references
Set QualityHUB = ThisWorkbook
Set search = Worksheets("Search")
Set customer = Worksheets("Search").Range("$D$1")
Set customerfolder = Worksheets("Search").Range("$D$3")


With QualityHUB

If IsEmpty((customer)) And IsEmpty((customerfolder)) Then

MsgBox "Please Fill out Customer Information and search again"

Exit Sub

End If

End With

With QualityHUB


Dim MyFolder As String
Dim MyFile As String
Dim Directory As String

Directory = "O:\LAYOUT DATA\" & customer & "\" & customerfolder"


MyFile = Dir(Directory & "*.xlsx")


Do While MyFile <> ""

Workbooks.Open Filename:=MyFile

MyFile = Dir()


Loop


MsgBox "Files Open for " + customerfolder + " complete"


End With


End Sub

推荐答案

这非常适合我

Sub OpenFiles()

Dim search As Worksheet
Dim customer As Range
Dim customerfolder As Range

Dim QualityHUB As Workbook

'setting variable references
Set QualityHUB = ThisWorkbook
Set search = Worksheets("Search")
Set customer = Worksheets("Search").Range("$D$1")
Set customerfolder = Worksheets("Search").Range("$D$3")


With QualityHUB

If IsEmpty((customer)) And IsEmpty((customerfolder)) Then

    MsgBox "Please Fill out Customer Information and search again"

Exit Sub

End If

End With

With QualityHUB


Dim MyFolder As String
Dim MyFile As String
Dim Directory As String

Directory = "O:\LAYOUT DATA\" & customer & "\" & customerfolder & "\"


MyFile = Dir(Directory & "*.xlsx")

Do While MyFile <> ""

Workbooks.Open Filename:=Directory & MyFile

MyFile = Dir()


Loop


MsgBox "Files Open for " + customerfolder + " complete"


End With


End Sub

问题之一是，您必须写

one of the issue was, you had to write

Workbooks.Open Filename:=Directory & MyFile

代替

Workbooks.Open Filename:=MyFile

这篇关于VBA代码可打开文件夹中的所有Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！