quickie - how to open multiple spreadsheets?[RESOLVED]

[pickarooney]

I'm just looking for a line or two of code that opens all the .xls files in a directory (input as String by user).

i.e. a working version of this:

Dim SourcePath As String
    Dim fName As String
    
    SourcePath = InputBox("Enter Path to File(s)")
    
    ChDir SourcePath
        
    Workbooks.Open Filename:=*.*

I assume it needs some sort of loop in the form of for all file in sourcepath, workbook.open filename:= file

[trisuglow]

Option Explicit
 
Sub OpenAllFiles()
    Dim vPath As String
    Dim vFile As String
    vPath = InputBox("Path?")
    If Right(vPath, 1) <> "\" Then vPath = vPath & "\"
    vFile = Dir(vPath & "*.xls")
    While vFile <> ""
        Workbooks.Open vPath & vFile
        vFile = Dir
    Wend
End Sub

[pickarooney]

Meur ras trisuglow

What does the Option Explicit bit do?

[trisuglow]

Throws up hands in horror!!

Option Explicit is an instruction to VB to tell you if you haven't declared your variables. It also prevents you making a load of other basic errors.

Everyone should always use Option Explicit. It might seem like hard work having to declare all your variables to begin with, but once you get used to it you'll find its well worth the effort.

While I'm ranting, I don't want to see anybody using the Variant data type unless they have a very good excuse. Too many people use Variant because they can't be bothered to work out what they're really trying to do.

[pickarooney]

Ah, it's for people who make mistakes...

[trisuglow]

Like me

PS missed the Cornish reference. A wodhes kewsel Kernewek? (sadly I don't).

[pickarooney]

Na gewsav, ny gewsav Kernewek yn ta (or even at all). Just googled it for a laugh

[pickarooney]

Sorry to reopen this, but I'm writing a new macro (in word) and need to be able to open all DOC and all RTF files in a directory. Is it possible to do that in one statement or do I need to make a second loop? I'm just asking now because I'm playing around with creating my own dialogs and currently have one which prompts the user to enter a path and pick either DOC, RTF or BOTH from a set of radio buttons, and BOTH currently returns the string *.doc, *.rtf, which I then use to open all the files of each type. If I'd be better opening each set of files in a separate loop, I think it's best if I change the three radio buttons to two tick boxes and just return a simple *.doc or *.rtf or both.

[trisuglow]

Does this Word macro sample help you at all?

Sub OpenAll()
'
' OpenAll Macro
' Macro created 21/04/2005 by
'
    Dim str As String
    Dim vFiles As String
    With Application.FileSearch
        .FileName = "*.doc;*.txt"
        .LookIn = "C:"
        .Execute
        For i = 1 To .FoundFiles.Count
            str = str & .FoundFiles(i) & vbCrLf
        Next i
        MsgBox str
    End With
End Sub

[pickarooney]

Yep, cheers again