quickie - how to open multiple spreadsheets?[RESOLVED]
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:
VB Code:
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
Re: quickie - how to open multiple spreadsheets?
VB Code:
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
Re: quickie - how to open multiple spreadsheets?
Meur ras trisuglow :)
What does the Option Explicit bit do?
Re: quickie - how to open multiple spreadsheets?
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.
Re: quickie - how to open multiple spreadsheets?
Ah, it's for people who make mistakes... :D
Re: quickie - how to open multiple spreadsheets?
Like me :wave:
PS missed the Cornish reference. A wodhes kewsel Kernewek? (sadly I don't).
Re: quickie - how to open multiple spreadsheets?
Na gewsav, ny gewsav Kernewek yn ta (or even at all). Just googled it for a laugh :)
Re: quickie - how to open multiple spreadsheets?
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.
Re: quickie - how to open multiple spreadsheets?
Does this Word macro sample help you at all?
VB Code:
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
Re: quickie - how to open multiple spreadsheets?