Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a macro to open spreadsheets
I would really appreciate some help with this...
I am coding a macro to open all the workbooks in a folder, copy data from a specific page in each workbook (the page in each workbook has the same name) and paste that data into an open workbook. What code would I use to tell my macro to open each spreadsheet in a specific folder? How would I then tell it to close all the spreadsheets it has opened? Many thanks for your help, Tristan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a macro to open spreadsheets
Ron de Bruin has lots of sample code:
http://www.rondebruin.nl/tips.htm Tristan wrote: I would really appreciate some help with this... I am coding a macro to open all the workbooks in a folder, copy data from a specific page in each workbook (the page in each workbook has the same name) and paste that data into an open workbook. What code would I use to tell my macro to open each spreadsheet in a specific folder? How would I then tell it to close all the spreadsheets it has opened? Many thanks for your help, Tristan -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a macro to open spreadsheets
Tristan Try this
Sub CombineExcelFiles() Dim FilesToOpen Dim x As Integer Dim wkbAll As Workbook Dim wkbTemp As Workbook Dim sDelimiter As String On Error GoTo ErrHandler Application.ScreenUpdating = False sDelimiter = "," FilesToOpen = Application.GetOpenFilename _ (FileFilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="excel Files to Open") If TypeName(FilesToOpen) = "Boolean" Then MsgBox "No Files were selected" GoTo ExitHandler End If x = 1 Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x)) wkbTemp.Sheets(1).Copy Set wkbAll = ActiveWorkbook wkbTemp.Close (False) wkbAll.Worksheets(x).Columns("A:A").TextToColumns _ Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, _ Tab:=False, Semicolon:=False, _ Comma:=False, Space:=False, _ Other:=False, OtherChar:="|" x = x + 1 While x <= UBound(FilesToOpen) Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x)) With wkbAll wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.count) .Worksheets(x).Columns("A:A").TextToColumns _ Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, _ Other:=False, OtherChar:=sDelimiter End With x = x + 1 Wend ExitHandler: Application.ScreenUpdating = True Set wkbAll = Nothing Set wkbTemp = Nothing Exit Sub ErrHandler: MsgBox Err.Description Resume ExitHandler End Sub -- Jake "Dave Peterson" wrote: Ron de Bruin has lots of sample code: http://www.rondebruin.nl/tips.htm Tristan wrote: I would really appreciate some help with this... I am coding a macro to open all the workbooks in a folder, copy data from a specific page in each workbook (the page in each workbook has the same name) and paste that data into an open workbook. What code would I use to tell my macro to open each spreadsheet in a specific folder? How would I then tell it to close all the spreadsheets it has opened? Many thanks for your help, Tristan -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open multiple spreadsheets | Excel Discussion (Misc queries) | |||
How can I get my spreadsheets to open faster? | Excel Worksheet Functions | |||
How do I get spreadsheets to open from my docs again | Setting up and Configuration of Excel | |||
i try to open Excel spreadsheets, and they open in wordpad-- | Excel Discussion (Misc queries) | |||
Iterate though all open spreadsheets | Excel Worksheet Functions |