如何在文件名以变量开头的特定文件夹中打开文件？ [英] How can I open a file in a specific folder with file name starting with a variable?

问题描述

我正在尝试在特定文件夹中查找文件。文件名由一个唯一变量和一些随机数组成，并且始终为.XLS格式。

唯一变量可以在运行宏的文件中找到。

用户将通过对话框屏幕选择该文件夹。



这些是我的代码中使用的声明：

  Dim  wbIT 作为工作簿
  Dim  wbSAP 作为工作簿
  Dim  wbPGTL 作为工作簿
  Dim  DataFile  As  字符串 
  Dim  LastRow 作为 长 
  Dim  LastCol  As  长 
  Dim  LastRowIO  As  长 
  Dim  LastColIO  As  长 
  Dim  LastRowIOP  As  长 
  Dim  LastRowFY  As  长 
  Dim  i  As  长 
  Dim  w  As  长 
  Dim  x  As  长 
  Dim  y 作为 长 
  Dim  z 作为 长 
  Dim 文件夹作为 字符串 
  Dim  StrFile 作为 字符串 
  Dim  findrow  As 范围
  Dim  findrownumber 作为 长 
  Dim  LastRowPGTL 作为 长 
  Dim  LastRowPO 作为 长 
  Dim  LastRowUnique  As  长 
  Dim  LastRowBewerking  As  长 
  Dim  LastRowUqPO  As  长 
  Dim  LastRowUqMat  As  长 
  Dim  LastRowUnq  As  长 
  Dim  LastRowUnq2  As  长 
  Dim  FirstRowSum  As  长 
  Dim  LastRowSum  As  长 
  Dim  PotentialI ssue 作为 字符串 
  Dim  rngDupl（） As 范围
  Dim  rngCell  As 范围
  Dim  rngCheck 作为范围
  Dim  lduplicates  As   Long  
  Dim  myPath  As   String  
  Dim  myFile  As   String  
  Dim  fileType  As   String  

代码本身（我已复制到有关'的所有活动的地步找到'文件停止'：

 ' 询问用户定义PGTL文件夹 
 LastRowIO = wbIT.Sheets（ 发票概述）。范围（  G& Rows.Count）。结束（xlUp）。
 对于 x =  4    LastRowIO 
 ' 从用户获取目标文件夹路径 
 使用 Application.FileDialog（msoFileDialogFolderPicker）
 .Title =  选择源文件夹 
 .AllowMultiSelect =  False  
。显示
 myPath = .SelectedItems（ 1 ）&   \& wbIT.Sheets（ 发票概述）。范围（   G& x）.Value&   * 
 结束 使用 
 ' 指定文件类型 
 fileType =   * .xls * 
 ' 目标路径文件类型 
 myFile = Dir（myPath）
  while （myFile<>  ）
 < span class =code-keyword> If  InStr（myFile，wbIT.Sheets（  Invoice overview ）。范围（  G& x）.Value）>  0  然后 
 ' < span class =code-comment>定义PGTL工作簿并打开它 
 设置 wbPGTL = Workbooks.Open（myFile）
 结束 如果 
 myFile = Dir 
  Wend  
 ' 复制到发票跟踪工作簿 
 wbPGTL。表格（ 1 ）。范围（  A1：AL30000 ）。复制
 wbIT.Sheets（  PGTL）。范围（   A1）。PasteSpecial 
 delay（ 10 ）
 ' 关闭PGTL工作簿 
 Application.DisplayAlerts = 错误 
 wbPGTL.Close SaveChanges：= False 
 Application.DisplayAlerts =  True  

每次尝试，MyPath变量都会获得正确填充，但是当使用Dir（MyPath）时，MyFile变量保持为空，这很奇怪，因为以该值开头的文件出现在所选文件夹中（并且未打开）。



如果有人可以帮我解决这个问题吗？



如果您需要任何澄清，我将非常乐意提供我能提供的任何信息。



提前谢谢！



我的尝试：



我试过以不同的方式设置变量，通过不同的变量添加通配符，尝试其他文件夹，其他文件等......但从未设法让它滚动。

决方案

解决的问题。解决方案包括为错误处理添加一些IF语句。

Hi,

I'm trying to find a file in a specific folder. The file name consists of a unique variable and then some random numbers and will always be in .XLS format.
The unique variable can be found in the file from where the macro will be run.
The folder will be selected by the user via a dialog screen.

These are the declarations used in my code:

Dim wbIT As Workbook
Dim wbSAP As Workbook
Dim wbPGTL As Workbook
Dim DataFile As String
Dim LastRow As Long
Dim LastCol As Long
Dim LastRowIO As Long
Dim LastColIO As Long
Dim LastRowIOP As Long
Dim LastRowFY As Long
Dim i As Long
Dim w As Long
Dim x As Long
Dim y As Long
Dim z As Long
Dim Folder As String
Dim StrFile As String
Dim findrow As Range
Dim findrownumber As Long
Dim LastRowPGTL As Long
Dim LastRowPO As Long
Dim LastRowUnique As Long
Dim LastRowBewerking As Long
Dim LastRowUqPO As Long
Dim LastRowUqMat As Long
Dim LastRowUnq As Long
Dim LastRowUnq2 As Long
Dim FirstRowSum As Long
Dim LastRowSum As Long
Dim PotentialIssue As String
Dim rngDupl() As Range
Dim rngCell As Range
Dim rngCheck As Range
Dim lduplicates As Long
Dim myPath As String
Dim myFile As String
Dim fileType As String

The code itself (I have copied up to the point where all activities regarding the 'to be found' file stops):

'Ask user to define PGTL folder
LastRowIO = wbIT.Sheets("Invoice overview").Range("G" & Rows.Count).End(xlUp).Row
For x = 4 To LastRowIO
    'Get target folder path from user
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Source Folder"
        .AllowMultiSelect = False
        .Show
    myPath = .SelectedItems(1) & "\" & wbIT.Sheets("Invoice overview").Range("G" & x).Value & "*"
    End With
    'Specify file type
    fileType = "*.xls*"
    'target Path with file type
    myFile = Dir(myPath)
    While (myFile <> "")
        If InStr(myFile, wbIT.Sheets("Invoice overview").Range("G" & x).Value) > 0 Then
            'Define PGTL workbook and open it
            Set wbPGTL = Workbooks.Open(myFile)
        End If
        myFile = Dir
    Wend
    'Copy to invoice tracking workbook
    wbPGTL.Sheets(1).Range("A1:AL30000").Copy
    wbIT.Sheets("PGTL").Range("A1").PasteSpecial
    delay (10)
    'Close PGTL workbook
    Application.DisplayAlerts = False
    wbPGTL.Close SaveChanges:=False
    Application.DisplayAlerts = True

With every try, the MyPath variable gets filled correctly, but when using the Dir(MyPath), the MyFile variable remains empty, which is strange as the file starting with that value is present in the selected folder (and unopened).

If somebody can assist me with this issue?

Should you require any clarification, I would be more than happy to provide any information I can give.

Thank you in advance!

What I have tried:

I have tried setting up the variables in different ways, adding wildcards via different variables, tried other folders, other files etc ... but never managed to get it rolling.

解决方案

Issue resolved. Solution included adding some IF statements for 'error handling'.

这篇关于如何在文件名以变量开头的特定文件夹中打开文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！