vba to open many workbooks

Microsoft Office excel 2007
September 8, 2010 at 18:35:45
Specs: Windows XP
Hi all I need to create another vba to open up as many workbook as i want until i click the cancel button.
And by doing so , i will actually copy the worksheet (sheet1.xls) of each of my opened workbook and save it to a new worksheet...My code can only open one workbook..

Dim Filename As Variant
Dim InLine As String
Dim Row As Integer


Dim NewName As String
Dim nm As Name
'Dim ws As Worksheet
Dim wb As Workbook
Dim Index As Integer
Dim IndexOf As Integer
Dim c As Range
Dim SrchRng
Dim wsO As Worksheet
Dim wbOpen As Workbook, wksNew As Worksheet, strWbk As String
Dim firstName As String
Dim lastName As String
Dim rowNum As Integer
Dim colNum As Integer
Dim intOnes As Integer
If MsgBox("Create Workbook" & vbCr & _
"New sheets will be pasted as values" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub
With Application
.ScreenUpdating = False


'Copy specific sheets Array("Sheet Name", "Another sheet name", "And Another"))
On Error GoTo ErrCatcher

strWbk = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls),*.xls,All Files (*.*),*.*")
Set wbOpen = Workbooks.Open(strWbk)


' Cells.Select
'Cells.EntireColumn.AutoFit


Sheets(Array("Sheet1")).Copy

On Error GoTo 0
Sheets("Sheet1").Select
Range("A4:E4").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("C10").Select
ActiveSheet.Paste
Columns("C:C").EntireColumn.AutoFit
Cells.Select
Sheets("sheet1").Select
Range("E33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("I10").Select
ActiveSheet.Paste
Range("J14").Select



Dim myFilename As String
'Give your user an input box to enter the name of the file
myFilename = InputBox("Enter name you wish to assign to the file with the extension .xls")
'Save the document using the file name assigned
ActiveWorkbook.SaveAs Filename:=myFilename
ActiveWorkbook.Close SaveChanges:=False
.ScreenUpdating = True
End With
Exit Sub

ErrCatcher:
MsgBox "Did You Forget To Name Your Worksheet?"
End Sub


See More: vba to open many workbooks

Report •


#1
September 8, 2010 at 20:35:05
re: until i click the cancel button.

I assume you mean "until i click No on the MsgBox"

Try adding a Label and a Go To Statement.

Note: I didn't test your code, so I'm just offering a way to get you back to the top of your code after it does it's thing. Place the GoTo and Label where you feel it fits best.

GetFileName:
  If MsgBox("Create Workbook" etc...)

Code
Code
Code

 GoTo GetFileName
Exit Sub


P.S. You should probably take a close look at your code and eliminate the unnecessary steps. You're doing a lot of "selecting" of cells and sheets which don't appear to serve any purpose.

Keep in mind that lines like these:

Sheets("sheet1").Select
Range("E33").Select
Selection.Copy

can usually be condensed to this:

Sheets("sheet1").Range("E33").Copy

BTW: Is that the entire macro? You seem to have Dimmed a lot of variables that never get used in the code.


Report •

#2
September 8, 2010 at 20:40:03
mmm nope that is not the entire code i deleted a lot of those away as it is not really relavant to my question here.

yea i understand tat but haven't really get around to clean up those codes...haha


Report •

#3
September 8, 2010 at 21:11:57
i have manage to edit my code and it is able to open as many workbook as i select within a folder but now the problem is how do i get all of these to go into one single workbook...

right now, my coding save a worksheet for every workbook i have selected..so if i choose four workbook, i will end up with another four workbook with my result in there..


Dim vaFiles As Variant
Dim i As Long
Dim wbkToCopy As Workbook

vaFiles = Application.GetOpenFilename("Excel Files (*.xls), *.xls", _
Title:="Select files", MultiSelect:=True)

If IsArray(vaFiles) Then
For i = LBound(vaFiles) To UBound(vaFiles)
Set wbkToCopy = Workbooks.Open(Filename:=vaFiles(i))

Sheets(Array("ACTUAL DATA")).Copy
Sheets("ACTUAL DATA").Select
Range("A4:E4").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("C10").Select
ActiveSheet.Paste
Columns("C:C").EntireColumn.AutoFit
Cells.Select
Sheets("ACTUAL DATA").Select
Range("E33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("I10").Select
ActiveSheet.Paste
Range("J14").Select
Dim myFilename As String
'Give your user an input box to enter the name of the file
myFilename = InputBox("Enter name you wish to assign to the file with the extension .xls")
'Save the document using the file name assigned
ActiveWorkbook.SaveAs Filename:=myFilename
ActiveWorkbook.Close savechanges:=False
wbkToCopy.Close savechanges:=False
Next i

End If


Report •

Related Solutions

#4
September 9, 2010 at 04:00:51
re: "but haven't really get around to clean up those codes"

You know your code is sloppy, you know you need to clean it up, and yet you want us to wade through it and figure how to make it better for you?

Why not clean it up now so that it is easier for us to follow thus making it easier to help you?


Report •

#5
September 9, 2010 at 06:14:26
Hi,

From your description in response #3 is this what you are trying to do:

Create a new workbook
Ask user to name the new workbook
Save the new workbook

Select several existing workbooks.
Open each one in turn

For each existing workbook:
copy cells "A4:E4" on a worksheet named "ACTUAL DATA"
Create a new worksheet in the new workbook and paste these cells to it, starting at cell "C10"
and
copy cell "E33" on a worksheet named "ACTUAL DATA" and paste it to cell "I10" on a worksheet named "Sheet2"

Then close the existing workbook without saving it, before opening the next one
and finally Save and Close the new workbook.

Questions:
1. Is each new worksheet in the new workbook named with its default name e.g. Sheet1, Sheet2
2. You can't keep copying cell E33 to Sheet2 cell I10 - it will overwrite the previous copy. Should E33 be pasted to the same new worksheet as cells A4:E4
3. The line: 'Sheets(Array("ACTUAL DATA")).Copy' ...
3a. The use of Array is not required - if you copy a worksheet, just copy it: Worksheets("ACTUAL DATA").copy
3b. If you look at the help file for Worksheet Copy you will see that it copies the worksheet to a new workbook - which you probably don't want to do:
If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.
3c. Why are you copying the whole worksheet and then selecting only a few cells to copy & paste

Comments:
1. As DerbyDad03 said - you need to tidy up this code first - it makes it hard for you and for others to work on it.
2. You really should include comments for every step - using comments when you write the code not only helps when changes are to be made at a later date, it helps you to define what each step is intended to do.
If you had said:
'Copy whole worksheet to new workbook - keep its default name
Worksheets("ACTUAL DATA").Copy
then you and others would know what the line was for.
3. In VBA you rarely need to select anything before acting on it.
4. When posting, to maintain the indents in your code, put your data between <pre> and </pre> tags that you can insert using the 'Pre' icon above the reply box.

Regards


Report •

#6
September 13, 2010 at 19:43:10
Hi i hope this helps.Actually i want to copy cell A3 and last cell of column E:E for every workbook that i attempt to open and paste it to a new workbook.

let's say i have open four workbook..and each of the cell A3 and E:E has these data,it should show like this in my end result.I don;t want them to overwrite each other

( what i copy from A3) (What i copy from E:E)
data 1 12.00
data 2 1000.00
data 3 50.00
data 4 200.00


Sub Macro2()

    Dim vaFiles As Variant
    Dim i As Long
    Dim wbkToCopy As Workbook
    Dim wsAD As Worksheet, wsNew As Worksheet
    Dim counter As Integer
    
    For Each WS In Sheets
         If InStr(WS.Name, "ACTUAL DATA") > 0 Then
            counter = counter + 1
        End If
    Next WS
    
    vaFiles = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
              Title:="Select files", MultiSelect:=True)
                  
    If IsArray(vaFiles) Then
        
        Application.ScreenUpdating = False
    
        For i = LBound(vaFiles) To UBound(vaFiles)
        
            Set wbkToCopy = Workbooks.Open(Filename:=vaFiles(i))
            
            Set WS = Nothing
            On Error Resume Next
                Set wsAD = wbkToCopy.Sheets("ACTUAL DATA")
            On Error GoTo 0
            
            If wsAD Is Nothing Then
                MsgBox "Could not find Sheet: ""ACTUAL DATA"" in" & vbCr & "Workbook: " & wbkToCopy.Name
            Else
                Set wsNew = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
                counter = counter + 1
                wsNew.Name = "ACTUAL DATA (" & counter & ")"
                wsAD.UsedRange.Copy Destination:=wsNew.Range("A1")

            End If
            

' Copy cell A3 AND LAST CELL OF Column E:E in every "ACTUAL DATA" worksheet TO add a new worksheet
'LOOP

  
  






ThisWorkbook.SaveAs ThisWorkbook.path & "\" & NewName & "1234.xls", FileFormat:=xlWorkbookNormal

            wbkToCopy.Close SaveChanges:=False
            'ThisWorkbook.Close SaveChanges:=False
        
        Next i
        
        Application.ScreenUpdating = True
        

                
     End If
     
End Sub


Report •

#7
September 14, 2010 at 04:33:58
Hi,

From your description in response #6 and the code you posted, it appears that you want to do the following:

1. In the Workbook containing the macro, count the number of Worksheets that contain "ACTUAL DATA" in their names.
Note: This Workbook will be referred to as the "Destination" Workbook
2. Open a series of Excel workbooks.
3. Test if each one has a Worksheet named "ACTUAL DATA"
4. If there is a Worksheet named "ACTUAL DATA" copy two cells from it to a new Worksheet in the "Destination" Workbook
5. The new Worksheet will be named "ACTUAL DATA xx" where xx is a sequential number.

Question: Every time the macro is run it will create a new Worksheet named "ACTUAL DATA xx" for each opened Workbook that contains a Worksheet named "ACTUAL DATA" - is this what you intended,
or did you want a new "ACTUAL DATA xx" worksheet for each group of Workbooks opened (assuming that at least one of them contains a Worksheet named "ACTUAL DATA"

Regards


Report •

#8
September 14, 2010 at 18:25:20
Hi Humar

actually what my code will do is that

1)be able to open up as many workbook as i choose
2)check if the worksheet "actual data" exist in all these workbook...if it doesn't it will throw out a message
3) If it does, it will then copy and paste each of them and rename them as actual data (1) , actual data(2) and so on in the same workbook as my macro
4) then it will attempt to save this workbook with my result with a name call 1234.xls.

now the problem i have is that..
1) the worksheet where my macro is at always exits
2) i need to copy cell A3 and last cell of column E:E for every workbook that i attempt to open and paste it to a new workbook.

let's say i have open four workbook..and each of the cell A3 and E:E has these data,it should show like this in my end result.I don;t want them to overwrite each other

( what i copy from A3) (What i copy from E:E)
data 1 12.00
data 2 1000.00
data 3 50.00
data 4 200.00


Report •

#9
September 16, 2010 at 20:46:43
Hi,

Your descriptions remain confusing.

It did not help that you used false data in some of your posts. This results in different posts referring to the same item withe different names, such as Sheet1 and a worksheet named "actual data" (or "ACTUAL DATA")

You have said: Copy cell A3 AND LAST CELL OF Column E:E in every "ACTUAL DATA" worksheet TO add a new worksheet but you then say i need to copy cell A3 and last cell of column E:E for every workbook that i attempt to open and paste it to a new workbook.

Do you mean that you want to copy - in each opened workbook that contains a worksheet named "actual data" - cell A3 and the last cell in column E and then paste them to a new workbook - or do you want to paste them to a new worksheet in the workbook that contains the macro (i.e. a new worksheet each time you run the macro, or do you want to paste them to one worksheet in the the workbook that contains the macro, and create a single master workbook in the workbook that contains the macro.

In your last post you introduced for the first time a workbook named 1234.xls.
Is 1234.xls the workbook that contains the macro.
Is 1234.xls a real workbook name or just one you made up.
Do you want to create a different workbook to save results in each time - such as 1235.xls and 1236.xls

Can you explain what you mean by the worksheet where my macro is at always exits
When does it 'exit'. Do you mean that the 'workbook' that contains it exits (closes).

Please use real information in your reply.
Please re-read your reply before sending it to make sure that the information is accurate, including the correct use of the terms workbook and worksheet.

As I am sure you know, programming has to be very specific. If the program refers to a workbook, then it will not work 'as expected' if it should have referred to a worksheet.

My current understanding is this:
You have a workbook (but I don't know its name)
This workbook contains the macro
and it contains a 'master' worksheet that consists of the data from two locations in workbooks containing worksheets name "actual data" that you have opened (cells A3 and the last cells containing data in column E).
and it contains copies of the complete worksheets named "actual data" from each opened workbook that contained a worksheet named "active data", with the copies of these worksheets named with a sequential number: "actual data xx"

After adding the data the 'master' workbook is saved and closed.

At the ed of the process there is one 'master' workbook containing copies of the 'active data' worksheets in many Excel files that you selected and a single worksheet containing the A3 and column E cell values.

Regards


Report •


Ask Question