VBA关闭并重新打开Excel后无法读取文件 [英] VBA unable to read files after closing and reopen excel

问题描述

假设我在同一个文件夹中有3个文件(item.xlsx，master.xlsx，transfer.xlsm)，主要目的是将数据从项目传输到主文件.

Assuming I have 3 files all in same folder (item.xlsx, master.xlsx, transfer.xlsm) Main purpose is to transfer data from item to master.

我执行transfer.xlsm中的所有代码，并允许用户输入文件名和列映射.我已经花了几个小时编写代码，并且已经测试了好几次，并且工作得非常好.只需单击一下按钮，即可根据列映射从item.xlsx读取数据并将其复制到master.xlsx.

I do all the codes inside transfer.xlsm and allow users to input file name and column mappings. I have been doing few hours for the codes and have tested several times and is working perfectly fine. With a click of a button is able to read data from item.xlsx and copy over to master.xlsx according to the column mapping.

但是，当我关闭所有3个文件并再次重新打开时，就会出现问题.我打开所有3个文件，当我单击传输按钮时，xlsm显示未找到文件，这是我所做的错误处理.我确实尝试过在桌面上创建一个新文件夹，并在其中创建一个全新的transfer.xlsm，然后将项目和主文件复制过来，并将代码复制到新按钮中.它实际上可以工作，但是当我关闭并在新文件夹中重新打开后，该工作不起作用

However problem arise when i close all 3 files and reopen again. I open up all 3 file, when i click on the button on transfer,xlsm it show file not found which is the error handling i did. I did tried creating a new folder on my desktop and create a brand new transfer.xlsm inside, i copy the item and master file over and my code into my new button. It actually able to work but when i close and reopen in that new folder is not working,

当我处理它时，基本上可以正常工作，当我完全将其关闭然后重新打开时，它无法检测到这两个文件.

Basically is working fine when i am working on it, when i close it totally and reopen it is unable to detect the 2 files.

单元格值根据用户输入在transfer.xlsm中输入

source = Cells(5, 2)
sourceSheet = Cells(6, 2)
sourceSheetRow = Cells(7, 2) - 1

destination = Cells(8, 2)
destinationSheet = Cells(9, 2)
destinationSheetRow = Cells(10, 2) - 1

source = source + ".xlsx"
destination = destination + ".xlsx"

rows = Cells(11, 2)

If FileExists(source) = False Or FileExists(destination) = False Then
    MsgBox "File not found, please double check file name and make sure is in the same folder"
Exit Sub
End If

For i = 1 To rows
...
Next i

Function FileExists(FilePath As String) As Boolean
Dim TestStr As String
    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
        FileExists = False
    Else
        FileExists = True
    End If
End Function

我创建了这个transfer.xlsm，这样我就可以将其发送给人们，如果他们想将数据块从一个Excel复制到另一个Excel，而不是逐行复制粘贴.希望有人能给我一些指导

I created this transfer.xlsm so that i can send it to people if they want to copy chunks of data from one excel to another instead of copy paste row by row. Hope someone can give me some guidance

推荐答案

基于您提供的信息，我假设用户提供的信息只是不带扩展名的文件名: item 或 master ，而不是完整文件路径 C:\ SampleFolder \ item.xlsx 或 C:\ SampleFolder \ master.xlsx .另外，我假设当您运行此代码时，所有三个文件都必须位于同一文件夹中.

Based on the information you provided, I am assuming that the information provided by the users is just the file name without the extension: item or master, and not the full file path C:\SampleFolder\item.xlsx or C:\SampleFolder\master.xlsx. Additionally, I am assuming when you run this code all three files must be in the same folder.

在这种情况下，请尝试使用 ThisWorkbook.Path ，您可以将其应用于源和目标路径，以确保正在使用适当的文件路径.

If this is the case, try using ThisWorkbook.Path, you can apply this to your Source and Destination paths to ensure that the appropriate file path is being used.

Dim sPath as String
sPath = ThisWorkbook.Path + "\"

source = Cells(5, 2)
sourceSheet = Cells(6, 2)
sourceSheetRow = Cells(7, 2) - 1

destination = Cells(8, 2)
destinationSheet = Cells(9, 2)
destinationSheetRow = Cells(10, 2) - 1

source = source + ".xlsx"
destination = destination + ".xlsx"

If FileExists(sPath + source) = False Or FileExists(sPath + destination) = False Then
    MsgBox "File not found, please double check file name and make sure is in the same folder"
Exit Sub
End If
...

这篇关于VBA关闭并重新打开Excel后无法读取文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！