时间戳

首页 > 解决方案 > 打开以单元格中列出的特定字符串开头的 Excel 工作簿

excel - 打开以单元格中列出的特定字符串开头的 Excel 工作簿

问题描述

我想从特定文件夹打开工作簿，从 Excel 表中列出的特定字符串开始。

示例：我有一个 excel 列表 -

以这些数字开头的文件名如下：

存储在：C:\Users\Desktop\Testr\Excel_Files

下面是我的代码，但它只打开第一个文件，我试图添加循环但给出错误。

子宏1()

Dim wb As Workbook

Dim myPath As String

Dim myFile As String

Dim listFileName As String

Dim listName As String

Dim rowCount As Integer

rowCount = 1

listFileName = ActiveSheet.Range("A" & rowCount).Value

listName = listFileName & "*"

myPath = "C:\Users\Desktop\Test\Excel_Files"    

myFile = Dir(myPath & listName & ".xlsx", vbNormal)
If Len(myFile) = 0 Then
'(Here I Want to add such kind of part's list to a text file)

Else
Workbooks.Open myPath & myFile
MsgBox "Successfull", vbInformation, "Opened Sucessfully"
End If

结束子

请建议我如何为它创建一个循环或任何更好且简单的代码。此外 ，

  1. 我想搜索从 A1 到 A10 的名字
  2. Msg elert "Sucessfull" 不应该循环，它应该在进程结束时显示。
  3. 当未找到任何文件时，不应停止该过程，它将列出未找到的对象到文本文件中。

问候， Vivek Chotaliya

标签： excelvbaloops

解决方案

首先，您需要确定 A 列中使用的最后一行，我们使用这行代码执行此操作rowCount = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row，一旦完成，您可以使用For Next循环打开与 A 列 listName 匹配的所有文件。

在里面For Next我验证是否找到了文件，如果没有，那么它将调用一个小函数来创建一个 .txt 文件。

试试这个...

Option Explicit
Public Sub Open_Workbooks()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim listFileName As String
Dim rowCount As Long
Dim i As Long
Dim bool As Boolean

bool = False
rowCount = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To rowCount

listFileName = ActiveSheet.Cells(i, 1)

myPath = "C:\Users\" & Environ("Username") & "\Desktop\Test\Excel_Files\"
myExtension = "*.xlsx"

myFile = Dir(myPath & listFileName & myExtension)

If Not Len(myFile) = 0 Then

Set wb = Workbooks.Open(Filename:=myPath & myFile)

'do somenthing
'
'
'
'
'
'
'
wb.Close SaveChanges:=False

Else
Call Create_txt_Log(listFileName)
bool = True
End If

Next

If bool = False Then
MsgBox "Successfull", vbInformation, "Opened Sucessfully"
Else
MsgBox "Successfull but not all files where opened check text log file", vbInformation, "Opened Sucessfully"
End If

End Sub

功能...

Public Function Create_txt_Log(ByVal listFileName As String)

Dim Fileout As Object
Dim FSO As Object
Dim FolderPath As String
Dim myNotePadName As String
Dim myPath As String

Set FSO = CreateObject("Scripting.FileSystemObject")

myNotePadName = "Not_Found.txt"
myPath = "C:\Users\" & Environ("Username") & "\Desktop\Test\Files_Not_Found\"
FolderPath = myPath & myNotePadName

If FSO.FileExists(FolderPath) = False Then

Set Fileout = FSO.CreateTextFile(myPath & myNotePadName)
Fileout.Write listFileName
Fileout.Close

Else

Set Fileout = FSO.OpenTextFile(FolderPath, 8)
Fileout.Write vbCrLf & listFileName
Fileout.Close

End If

End Function

推荐阅读