I can start it for you:
Option Explicit
Sub BuildUpload()
Dim fso, foldername, f, file, files, ext
'Dim msg
Set fso = CreateObject("Scripting.FileSystemObject")
foldername = "C:\temp3\"
Set f = fso.GetFolder(foldername)
Set files = f.files
msg = ""
For Each file In files
ext = UCase(fso.getextensionname(file.Path))
If ((ext = "XLS") _
Or (ext = "XLSX") _
Or (ext = "XLSM")) Then
'msg = msg & Chr(13) & fso.getextensionname(file.Path) & " : " &
file.Path
Workbooks.Open file.Path
'------------------------------------
' Add your code here
'------------------------------------
End If
Next file
'MsgBox msg
End Sub
This will open all excel files in folder: C:\temp3
----- Original Message ----
> From: TahoeSalmon <mikesalmon...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Monday, January 12, 2009 12:54:40 AM
> Subject: $$Excel-Macros$$ many files to one file consolidation
>
>
> This novice is stumped. I have reviewed the blogs etal on vba and
> cannot get over this hurdle.
>
> Objective - vba code to open up 50 files in a directory, one at a time
> (no on linksupdate), copy a consistent named range "deptupload" to a
> master worksheet which contains this vba macro., then close file.
> FileName = 75 aUpload Dbase.xls is master consolidating 'to' file,
> which contains this vba. Sheet name for compiling the data is
> 'upload' in this master file.
> ....Needs to perform the copy/pastevalues at end of range from the
> previous copy/pastevalues.
>
> Below is my humble attempt...erroring at the Workbooks.Open command,
> which requires filename, which is the next line. However, I clearly
> have not coded correctly.
>
> I could easily place the 50 specific file names in a list in the
> master file if this simplifies matters....with or without the full
> directory path.
>
> fyi....I am in Excel 2003....(after finding after 2hrs the
> Application.FileSearch will not work in Excel 2007).
>
> Thanks so much, Mike.
>
>
> Sub BuildUpload()
> '
> ' BuildUpload Macro
> ' Macro recorded 1/11/2009 by Mike Salmon
> '
> ' Keyboard Shortcut: Ctrl+y
> '
> With Application.FileSearch
> .LookIn = "C:\Documents and Settings\HP_Administrator\My Documents
> \Salmon Michael\DBMs"
> .FileType = msoFileTypeExcelWorkbooks
> .SearchSubFolders = True
> .Execute
> End With
> cnt = Application.FileSearch.FoundFiles.Count
> For i = 1 To cnt
> Workbooks.Open
> Filename = Application.FileSearch.FoundFiles.Item(i), UpdateLinks
> = 2
> Range(deptupload).Select
> Selection.Copy
> Windows("75 aUpload Dbase.xls").Activate
> Range("B" & i + 1).Select
> Selection.PasteSpecial Paste = xlPasteValues
> Operation = xlNone
> ActiveWindow.ActivateNext
> ActiveWindow.Close
> Next i
>
>
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
