Solved: Open & copy from files with differing names

[JeffT]

Hi

I need to write a macro which will copy data from files which will have differing names into a master file. I know how to get the data from one file with a known name into the master, but don't know how to pick up the filename of the open file to use in the macro.

Perhaps GetOpenFileName or something similar or can the data be obtained from an open file nomatter what its name is?

I'm using the following at present:-

[VBA] Windows("Sharing test 1.xls").Activate
Range("C11:G21").Select
Selection.Copy
Windows("Sharing test Master.xls").Activate
ActiveSheet.Paste
[/VBA]
The filename Sharing test 1 may be anything.

Thanks

Jeff T

[JimmyTheHand]

Hi Jeff

GetOpenFilename allows you to select one or more files in runtime, and returns the filenames in an array. It doesn't open anything (except a dialog), but you can do with the filenames as you wish. The sample code below lists the filenames that were selected.
[vba]Sub Get_Multiple_File_Names()
Dim FileNameArray
Dim FileCounter As Long, i As Long


FileNameArray = Application.GetOpenFilename(, , , , True)
FileCounter = UBound(FileNameArray)
If FileCounter = 0 Then Exit Sub

For i = 1 To FileCounter
MsgBox FileNameArray(i)
Next
End Sub[/vba]
If you have open workbooks, you can access their names through the Workbooks collection. This sample code below lists names of all open workbooks, except the one that contains the macro.
[vba]Sub List_Opened_Workbooks()
Dim Wkb As Workbook
For Each Wkb In Workbooks
If Wkb.Name <> ThisWorkbook.Name Then
MsgBox Wkb.Name
MsgBox Wkb.Path & "\" & Wkb.Name
End If
Next
End Sub[/vba] Note that the name of the workbook doesn't include the path of the file, while GetOpenFilename returns filename with full path.

Finally, copying a range from one sheet to another doesn't require activation or selection of sheets and ranges. The sample code below shows how it is done
[vba]Sub Copy_Range()
Workbooks("Source.xls").Sheets("Source Sheet").Range("C11:G21").Copy _
Workbooks("Target.xls").Sheets("Target Sheet").Range("C11")
End Sub
[/vba] Note that workbook name, including the xls extension, is case sensitive.

Jimmy

[JeffT]

Thanks JTH

Sorry for the lack of description. I can get most of your code to work and the last one is very elegant.

However what I need to do is copy some code out of a file, the name of which isn't known until it is about to be opened. A collegue at work is doing a survey where he's e-mailing an excel file to lots of people & he's asking for them to save the files on the server anonymously
in individual excel files with different names. He then wants to extract the data into a master file.

So using your last paragraph of code I need to be able to extract the name of a file as its opened and use it in place of the "Source.xls" name automatically. I'm sure there's a way of getting "Wkb.Name" from your other code and using this in place of "Source.xls", I've tried lots of things but can't make it work.

Any solutions gratefully received.

regards

JeffT

[JeffT]

Thanks JTH

Sorry for the lack of description. I can get most of your code to work and the last one is very elegant.

However what I need to do is copy some code out of a file, the name of which isn't known until it is about to be opened. A collegue at work is doing a survey where he's e-mailing an excel file to lots of people & he's asking for them to save the files on the server anonymously
in individual excel files with different names. He then wants to extract the data into a master file.

So using your last paragraph of code I need to be able to extract the name of a file as its opened and use it in place of the "Source.xls" name automatically. I'm sure there's a way of getting "Wkb.Name" from your other code and using this in place of "Source.xls", I've tried lots of things but can't make it work.

Any solutions gratefully received.

regards

JeffT

[mdmackillop]

[VBA]Option Compare Text
Sub List_Opened_Workbooks()
Dim WB As Workbook
Dim wkb As Workbook
Set WB = ActiveWorkbook
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name And wkb.Name <> "Personal.xls" Then
Copy_Range wkb, WB
End If
Next
End Sub

Sub Copy_Range(wkb As Workbook, WB As Workbook)
Dim WS As Worksheet
Set WS = WB.Sheets.Add
WS.Name = Left(wkb.Name, Len(wkb.Name) - 4)
wkb.Sheets(1).Range("C11:G21").Copy WS.Range("C11")
End Sub
[/VBA]

[JeffT]

Thanks for that mdm

However (Although its a lot better than the method I used on another project) I don't want to add another sheet in the master file or rename the 1st sheet. I Just want to paste the cells to the existing first sheet (I may put it elsewhere once I've finished) from where I copy the data to various other places in the Master file. The first sheet in the Master file is just a "holding" sheet which changes each time a new page is imported.

I've had a try at amending the code but I just keep getting errors. I'd like to know where "Personal.xls" comes into it as well just for learning. This is way beyond my Excel book.

Thanks

JeffT

[Norie]

Why do you need the name of the workbook?

You can easily create a reference to a workbook that can then be used in subsequent code.

For example:

Set wbThis = ThisWorkboook ' create reference to workbook the code is in

Set wbAct  = ActiveWorkbook ' create reference to the active workbook

Set wbOpen = Workbooks .Open("C:\MyPath\MyBook.xls") ' open workbook and create a reference to it

[mdmackillop]

This will copy data from an open workbook excluding the Target book (containing the code) and Personal.xls (which contains other macros and is generally hidden, but open, when Excel is open). As the code loops through all open workbooks and overwrites to the same target, you can only have one source file open, to avoid overwriting previous data. The code can be modified to offset the target range.

[VBA]Option Compare Text
Sub List_Opened_Workbooks()
Dim wkb As Workbook
Dim Tgt As Range
Set Tgt = Workbooks("Target.xls").Sheets("Target Sheet").Range("C11")
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name And wkb.Name <> "Personal.xls" Then
wkb.Sheets(1).Range("C11:G21").Copy Tgt
End If
Next
End Sub
[/VBA]

[JeffT]

Thanks MDM

I think it now does exactly what I want it to.

Norie

THanks for your input.

The first code could be useful to save "naming" the master file so the code would be transportable or would still work if someone changed it's name.

The second was what I was sort of trying to do originally but MDM's does more.

The third only works if you know what the name of the file is. I tried it with *.xls to see if it brought up the open dialog box but it doesn't. Unfortunately in this case others could give it any name.

Thanks as usual for all the help.

Jeff T