Importing multiple csv into excel for mac using VBA /applescript

Question

I'm on the hunt for a solution to this problem.

I have a large number of .csv files, and I'm looking for a way to import them into Excel, as separate sheets in single workbook

Now I've asked in two other forums and after some help was left with two un-finished codes.

first is VBA

Sub test()
Dim MacReturn As String
Dim FilePaths As Variant
MacReturn = MacScript(ScriptString)

FilePaths = Split(MacReturn, ",")

If UBound(FilePaths) = -1 Then
    MsgBox "chosen folder had no CSV files"
Else
    If FilePaths(0) = "false" Then
        MsgBox "cancel button pressed"
    Else
        MsgBox (UBound(FilePaths) + 1) & " CSV files in folder."
    End If
End If
End Sub

Function ScriptString() As String
ScriptString = "tell application ""Finder"""

ScriptString = ScriptString & vbCr & "    try"
ScriptString = ScriptString & vbCr & "        set CSVfiles to (files of (choose folder) whose    name extension = ""csv"")"
ScriptString = ScriptString & vbCr & "        set allPaths to {}"
ScriptString = ScriptString & vbCr & "        repeat with oneFile in CSVfiles"
ScriptString = ScriptString & vbCr & "            set OnePath to """""
ScriptString = ScriptString & vbCr & "            repeat until (name of oneFile = """")"
ScriptString = ScriptString & vbCr & "                set OnePath to (get name of (oneFile)) & "":"" & OnePath"
ScriptString = ScriptString & vbCr & "                set oneFile to (container of oneFile)"
ScriptString = ScriptString & vbCr & "            end repeat"
ScriptString = ScriptString & vbCr & "            copy (text 1 thru -2 of OnePath) to end of   allPaths"
ScriptString = ScriptString & vbCr & "        end repeat"
ScriptString = ScriptString & vbCr & "activate application ""Microsoft Excel"""
ScriptString = ScriptString & vbCr & "        return allPaths"
ScriptString = ScriptString & vbCr & "    on error"
ScriptString = ScriptString & vbCr & "activate application ""Microsoft Excel"""
ScriptString = ScriptString & vbCr & "        return false"
ScriptString = ScriptString & vbCr & "    end try"
ScriptString = ScriptString & vbCr & "end tell"

End Function

The VBA seems to work and asks you to choose a folder when you hit OK nothing imports???

The second is all applescript

tell application "System Events"
set CSVfiles to path of (files of (choose folder) whose name extension = "csv")
end tell

tell application "Microsoft Excel"
activate
open workbook workbook file name "HD:Users:<my name>:Documents:Office:SOLAR:converted:Daily solar Generation.xlsx"
set targetSheet to worksheet 1 of workbook (name of active workbook)
repeat with thisFile in CSVfiles

    open text file filename thisFile data type delimited

    tell active workbook
        open workbook workbook file name "HD:Users:<myname>:Documents:Office:SOLAR:converted:Daily solar Generation .xlsx"
        set sourceBookName to its name
        set sourceSheet to sheet 1
    end tell
    copy worksheet sourceSheet after targetSheet
    close workbook sourceBookName
end repeat
end tell

This one seems to hang and times out with the error code

error "System Events got an error: AppleEvent timed out." number -1712

I've been at this for almost a week and while it's fun. (I liked getting to know applescript especially), could really use a hand trying to figure out what is wrong with either or both!!

You might be more likely to get a response if you narrowed down to a few lines where the error seems to be occurring; you could update your response with that information. — Steve Westbrook
– Steve Westbrook, Commented Nov 1, 2013 at 17:42
And that's part of the problem. The VBA get's to the "choose folder" box clicking ok nothing imports. The Applescript Just hangs then the error code. So posted the lot but thanks I'll try and keep it brief from now on@StevenW — Francesco Bartiromo
– Francesco Bartiromo, Commented Nov 1, 2013 at 18:19

Darrick Herwehe · Accepted Answer · 2013-11-01 19:00:27Z

1

The Applescript is hanging at choose folder because it is in the System Events tell block, but System Events can't figure out what to do with it. Move the folder choosing out of the tell block and it should work.

set theFolder to choose folder
tell application "System Events"
    set CSVfiles to path of (files of theFolder whose name extension = "csv")
end tell

answered Nov 1, 2013 at 19:00

Darrick Herwehe

3,7631 gold badge25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Francesco Bartiromo Over a year ago

Microsoft Excel got an error: Can't continue open workbook. highlights open workbook workbook file name "HD:Users:my name:Documents:Office:SOLAR:converted:Daily solar Generation.xlsx"@Darrick

Darrick Herwehe Over a year ago

I'm not at a Mac right now, but I believe workbook file name is only valid for currently open files. Try open (alias "path:to:file").

Collectives™ on Stack Overflow

Importing multiple csv into excel for mac using VBA /applescript

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related