Mac Excel 2016 VBA-Workbook.open出现1004错误 [英] Mac Excel 2016 VBA - Workbook.open gives 1004 error

问题描述

我试图获得一个在Mac的Excel 2011中完美运行的宏，以在Mac的Excel 2016中运行.目标是让用户指定一个包含.csv文件的文件夹，然后该宏循环遍历所有.csvs，打开每个文件夹以将信息从其中复制到另一个工作簿中.

I'm trying to get a macro that works perfectly in Excel 2011 for Mac to work in Excel 2016 for mac. The goal is to have the user specify a folder with .csv files in it, and then the macro loops through all the .csvs, opening each to copy information out of it into another Workbook.

尝试打开用户选择的文件夹中的第一个.csv文件时，宏失败，出现1004错误，找不到文件.

The macro fails when trying to open the first .csv file in the user-chosen folder, with a 1004 error, file cannot be found.

(旁注:在宏的前面，workbooks.open与用户选择的FILE完美配合使用)

(side note: Earlier in the macro, workbooks.open works perfectly with a user selected FILE)

宏非常大，因此我克服了这个问题，制作了一个全新的较小的宏.这是较小的测试代码，具有相同的失败行为:

The macro is huge, so I made a brand new smaller one just to get past this problem. Here is the smaller, test code, which has the same failing behavior:

Sub Test()
Dim folderpath As Variant
Dim filename As Variant
Dim newfilename As Variant
Dim wb As Workbook
Dim newfolderpath As Variant

folderpath = MacScript("choose folder as string")

newfolderpath = Replace(folderpath, ":", "\")

MsgBox (newfolderpath)

filename = MacScript("Choose file as string")
newfilename = Replace(filename, ":", "\")
MsgBox (filename)
MsgBox (newfilename)
MsgBox (Dir(filename))
MsgBox (newfolderpath & Dir(filename))

Set wb = Workbooks.Open(newfolderpath & Dir(filename))
End Sub

所有msgbox提供期望值.

All the msgboxes provide expected values.

• newfolderpath =带有\分隔符的整个路径.
• filename =完整路径和文件名，带有:分隔符.
• newfilename =带有\分隔符的完整路径和文件名.
• Dir(filename)=仅文件名.
• newfolderpath&Dir(filename))=带有\的完整路径和文件名分隔符.

设置wb行会给出:

Run-time error '1004'
'Hard DRIVE\USERS\DAVE\DESKTOP\CSVS\1.CSV'

找不到.

很明显，文件在那里，可以打开并且可以正常工作.

Clearly the file is there, openable and works.

非常感谢您的帮助.

推荐答案

好吧，我想我找到了答案，或者至少找到了适合我项目的答案.

Well, I think I found the answer, or at least an answer that works for my project.

我从宏的较早位置返回到workbooks.open(用户选择的文件)，发现它正在读取它为"/users/Dave/Desktop/csvs/1.csv".因此，即使debug和msgbox返回了反斜杠，还是以某种方式想要正斜杠(蒂姆，您的观点是正确的).此外，它显然会截断Mac硬盘驱动器的名称，并从users目录开始.因此，我只修改了变量以匹配此格式，瞧，它起作用了.对于那些感兴趣的人，下面是我使用的修改后的代码:

I went back to the workbooks.open(user-selected FILE) from earlier in the macro, and discovered it was reading it as "/users/Dave/Desktop/csvs/1.csv". So, even though debug and msgbox were returning backslashes, it was somehow wanting forward slashes (You were partially right, Tim). Additionally, it clearly truncates the name of the Mac hard drive, and starts with the users directory. So, I just modified the variables to match this format, and voila, it worked. For those who are interested, here's the modified code I wound up using:

Sub Test()

Dim folderpath As Variant
Dim newfilename As Variant
Dim wb As Workbook
Dim newfolderpath As Variant
Dim newfp As Variant

folderpath = MacScript("choose folder as string")
newfolderpath = Replace(folderpath, ":", "/")
newfp = Right(newfolderpath, Len(newfolderpath) - InStr(newfolderpath, "/") + 1)

newfilename = Dir(newfp)

Set wb = Workbooks.Open(newfp & newfilename)

Exit Sub

感谢所有提供帮助的人.

Thanks to everyone that helped.

这篇关于Mac Excel 2016 VBA-Workbook.open出现1004错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！