是否有替代Scripting.FileSystemObject在Excel 2011 VBA的mac？ [英] Is there an alternative to Scripting.FileSystemObject in Excel 2011 VBA for the mac?

问题描述

如何在Excel 2011 for MAC中安装/使用Scripting.FileSystemObject？似乎表明在Excel 2010中使用 Scripting.FileSystemObject 是不可能。

还有其他替代方案可用，以便我可以：

• 通过每个文件中的每个工作表，在特定目录


• iterate 中获取所有Excel文件的集合，并将其导出为 .csv 文件

目前这是一个六步每个文件：

   - 如何为文件中的所有工作表创建CSV文件：
 1.打开文件
 2.点击开发人员
 3.点击编辑器
 4.点击ThisWorkbook 
 5.复制：
 Sub save_all_csv（）
 On Error Resume Next 
 Dim ExcelFileName As String 
 ExcelFileName = ThisWorkbook.Name 
对于每个objWorksheet在ThisWorkbook.Worksheets 
 Filename =FILE-& ExcelFileName& -WORKSHEET-& objWorksheet.Name& .csv
 objWorksheet.SaveAs文件名：=Macintosh HD：用户：爱德华：文件：temporaryNoBackup：&文件名，FileFormat：= xlCSV，CreateBackup：= False 
下一个
 Application.DisplayAlerts = False 
 Application.Quit 
 End Sub 
 6.点击运行本身）
  

我正在寻找一种方法来自动化 Mac，理想情况下，（cron job？service？）将每10分钟打开一次excel文件，然后依次查找一个目录，将所有其他Excel文件转换为.csv文件，然后自己关闭。 p>

没有Scripting.FileSystemObject，如何在Mac上完全自动完成这个Excel到CSV的转换？

解决方案

我想到的唯一方法是使用Dir功能。由于mac在其文件名中支持额外的字符，所以通配符不能与Dir功能一起使用。以下是一个示例。

 函数GetFileList（folderPath As String）作为集合
'mac vba不支持通配符DIR函数
 
 Dim file As String 
 Dim returnCollection作为新集合
 
如果右$（folderPath，1）<> \然后
 folderPath = folderPath& \
 End If 
 
 file = Dir $（folderPath）'setup initial file 
 
 Do While Len（file）
 returnCollection.Add folderPath&文件
文件=目录$ 
循环
 
设置GetFileList = returnCollection 
结束函数
  

The answers to How can I install/use "Scripting.FileSystemObject" in Excel 2011 for MAC? seem to indicate that using Scripting.FileSystemObject in Excel 2010 for the mac is not possible.

What other alternative is available so I can:

• get a collection of all Excel files in a specific directory
• iterate through each worksheet within each file and export it to a .csv file

Currently this is a six-step process for each file:

--how to create CSV files for all worksheets in a file:
1. open file
2. click "Developer"
3. click editor
4. click ThisWorkbook
5. copy in:
Sub save_all_csv()
    On Error Resume Next
    Dim ExcelFileName As String
    ExcelFileName = ThisWorkbook.Name
    For Each objWorksheet In ThisWorkbook.Worksheets
        Filename = "FILE-" & ExcelFileName & "-WORKSHEET-" & objWorksheet.Name & ".csv"
        objWorksheet.SaveAs Filename:="Macintosh HD:Users:edward:Documents:temporaryNoBackup:" & Filename, FileFormat:=xlCSV, CreateBackup:=False
    Next
    Application.DisplayAlerts = False
    Application.Quit
End Sub
6. click run (it closes by itself)

I'm looking for a way to automate this on the Mac, ideally, a (cron job?, service?) would open the excel file every 10 minutes, which would in turn look in a directory, convert all the other Excel files to .csv files, and then close by itself.

Without Scripting.FileSystemObject, how can I make this Excel-to-CSV conversion fully automatic on the Mac?

解决方案

The only way I can think of is using the "Dir" function. Since mac supports extra characters in their filenames, wildcards do not work with the "Dir" function. Here is a sample.

Function GetFileList(folderPath As String) As Collection
'mac vba does not support wildcards in DIR function

    Dim file As String
    Dim returnCollection As New Collection

    If Right$(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If

    file = Dir$(folderPath) 'setup initial file

    Do While Len(file)
        returnCollection.Add folderPath & file
        file = Dir$
    Loop

    Set GetFileList = returnCollection
End Function

这篇关于是否有替代Scripting.FileSystemObject在Excel 2011 VBA的mac？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！