macro to open multiple text files

**ccloak** · 02-05-2013, 06:25 PM

I am new to creating macros and would like to open many (100s) space delimited text files into Excel (Mac 2011). The following works for one file (123test.txt) but is there some way to open all .txt files within a specified folder (in this case "MRStexts") so I can do this in batches instead of one at a time? Thanks in advance!

Sub OpenMRStext()
'
' OpenMRStext Macro
' Open space delimited MRS text files
'
' Keyboard Shortcut: Option+Cmd+t
'
    Workbooks.OpenText Filename:= _
        "Macintosh HD:Users:ccc:Desktop:MRStexts:123test.txt" _
        , Origin:=xlMacintosh, StartRow:=1, DataType:=xlDelimited, TextQualifier _
        :=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False _
        , Comma:=False, Space:=True, Other:=False
    
    
End Sub

‹

**Alf** · 02-06-2013, 03:32 AM

First of all a warning the commands I've used works in Excel 2007 and 2003 but as I do have Excel 2011 (Mac) I really don't know if this will work for you.

Sub OpenMRStext()
'
' OpenMRStext Macro
' Open space delimited MRS text files
'
' Keyboard Shortcut: Option+Cmd+t

Dim sPath As String
Dim sFile As String
    sPath = "Macintosh HD:Users:ccc:Desktop:MRStexts:"     
    sFile = Dir(sPath & "*.txt")
    Application.ScreenUpdating = False
        Do While sFile <> ""        
Workbooks.OpenText Filename:= sPath & sFile, _
Origin:=xlMacintosh, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False _
, Comma:=False, Space:=True, Other:=False

‘ your code to import text file to an Excel sheet
‘ then you must close the text file
    sFile = Dir        
    Loop 
Application.ScreenUpdating = True  
End Sub

Alf

**ccloak** · 02-07-2013, 06:10 PM

Alf, thanks but I get a Run-time error '68': Device unavailable at

 sFile = Dir(sPath & "*.txt")

I think it does not like the *
When I substitute an actual file name for the * it opens that file just fine...
Any suggestions?

**arlu1201** · 02-06-2013, 04:48 AM

ccloak,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

**Alf** · 02-08-2013, 03:22 AM

I think it does not like the *

Oh dear the "*" was supposed to be the wildcard that should make it possible to loop through all the files with ".txt" ending in sPath folder.

If you can find out what the wildcard character for Excel Mac version is try to substitute "*" with that. Sorry but as I don't use Excel Mac nor know anybody who does so I can't help you any further.

Alf

**Alf** · 02-09-2013, 02:49 PM

Found a link at Roin de Bruin with macros and example to dowload for Excel 2011 (Mac). Amost at the bottom of the page there is a description of how to "Loop through files in a folder on a Mac"

http://www.rondebruin.nl/mac.htm#Loop

Alf