PDA

View Full Version : Solved: Need to have GetOpenFilename( to work in code.



Shazam
01-22-2008, 10:20 AM
Hi everyone,


I would like to modified this code below. Right now its combinding multiple workbooks with a specific file path. But I would like to choose the path manually. So I thought by change this line below:

Path = "Z:\Foundry Performance\0 - Archives\Core Incentive\2007\"

To this


FilesToOpen = Application.GetOpenFilename(FileFilter:= _
"Excel files (*.xls), *.xls", MultiSelect:=True)


But it does not work. It only open and closes the files. Is there a way to modifed it to work?



Option Explicit
Sub CombineFiles()


Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet


Dim FilesToOpen




'Application.ScreenUpdating = False


Path = "Z:\Foundry Performance\0 - Archives\Core Incentive\2007\" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)


For Each WS In Wkb.Worksheets
If InStr(1, WS.Name, "Incentive") <> 0 Then
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
FileName = Dir()
Loop

End Sub

rory
01-22-2008, 10:44 AM
You could use:
Function GetFolder() As String
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
If dlg.Show = -1 Then
GetFolder = dlg.SelectedItems(1)
End If
End Function

then use:
Path = GetFolder

I'm not sure what you mean about GetOpenFilename opening and closing files - it doesn't actually do anything other than return a list of file names.

Shazam
01-22-2008, 11:09 AM
Hi rory,


I tried your code and it works. But it only works if I select a specific folder. Can you modify it so I could select what excel files I would like to choose?

Bob Phillips
01-22-2008, 12:56 PM
Sub CombineFiles()
Dim mpCount As Long
Dim Wkb As Workbook
Dim WS As Worksheet

With Application.FileDialog(msoFileDialogOpen)

.AllowMultiSelect = True
.Show

If .SelectedItems.Count > 0 Then

For mpCount = 1 To .SelectedItems.Count

Set Wkb = Workbooks.Open(Filename:=Path & "\" & .SelectedItems(mpCount))

For Each WS In Wkb.Worksheets

If InStr(1, WS.Name, "Incentive") <> 0 Then

WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
Next mpCount
End If
End With
End Sub

Shazam
01-22-2008, 01:41 PM
Thank you very much for the code. I changed this line below to get it to work properly


Set Wkb = Workbooks.Open(Filename:=Path & "\" & .SelectedItems(mpCount))



To:



Set Wkb = Workbooks.Open(Filename:=Path & "" & .SelectedItems(mpCount))



I took out the slash in the code and everything is working fine now.

Once again thank you!

Bob Phillips
01-22-2008, 02:21 PM
Actually, you should only need



Set Wkb = Workbooks.Open(Filename:= .SelectedItems(mpCount))

Shazam
01-22-2008, 02:27 PM
Actually, you should only need



Set Wkb = Workbooks.Open(Filename:= .SelectedItems(mpCount))



Even better!!

Thanks xld!

sindhuja
01-23-2008, 07:15 PM
Hi Xld,

Just a query !!

Is it possible to access the folders or files which are stored in the sharepoint uisng the above code.

Regards,
Sindhuja

Bob Phillips
01-24-2008, 03:56 AM
Don't know, I am not SharePoint familiar yet. But aren't they just directories like any other (That may be my ignorance asking :D)?

sindhuja
01-25-2008, 07:56 PM
Hi Xld,

url like.... ex, http:\\sharepoint.ace.com\\

regards,
Sindhuja

Bob Phillips
01-26-2008, 03:12 AM
I don't think I can help you here Sindhuja. As I understand, Sharepoint maintains directories within an Intranet, but I don't have one to test it out.