PDA

View Full Version : GetOpenFilename multiselect



Emily
05-22-2005, 05:05 AM
The code below does not work when the first With is marked as remark, any idea?

Thanks
Emily



Sub DisplayFiles()
Dim FileNames As Variant
Dim i As Integer
'With Application
' .DisplayAlerts = False
' .EnableEvents = False
' .ScreenUpdating = False
'End With

FileNames = Application.GetOpenFilename("Excel Files,*.xls", , , , True)

If IsArray(FileNames) Then
For i = LBound(FileNames) To UBound(FileNames)
MsgBox FileNames(i)
Next i
End If
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

Bob Phillips
05-22-2005, 05:27 AM
The code below does not work when the first With is marked as remark, any idea?

What doesn't work? I couldn't get it to not work with any combination of commented or non-commented code.

Is the code as posted that which doesn't work, and if so, in what way does it not work?

Emily
05-22-2005, 08:46 AM
It cannot show the message box twice when 2 files are selected.

WinMe + Office Xp

Norie
05-22-2005, 09:28 AM
Cross-post http://www.mrexcel.com/board2/viewtopic.php?t=147629

Bob Phillips
05-22-2005, 09:29 AM
It cannot show the message box twice when 2 files are selected.

WinMe + Office Xp

Don't get a problem, with or without the comments. I see 2, 3 or however many I select, MsgBox doisplays.

Don't have WinME, only NT4 , Win98 (somewhere) and XP, Office 2000 & XP

hairywhiterabbit
05-22-2005, 11:27 AM
Hi Emily,

I've had a problem with multi-select before.

Whenever multiple files were selected using this method an arrary wasn't passed and the first selected file was returned as a string.

It came down to a bug in Excel due to a corrupt work book. Try copying all your code into a new workbook and see if it helps.

Cheers,
Andrew

Emily
05-22-2005, 07:57 PM
Thanks in advance

Emily
05-23-2005, 09:16 PM
It needs to add Application.ScreenUpdating = False if the code is stored in a worksheet which have conditional formatting. Reason unknown.

Please read:
http://www.mcse.ms/message472480.html

Bob Phillips
01-02-2014, 02:59 AM
I had to change the code a bit as it was failing on checking for a Cancel on the attachments. I also added a bit more to stop the icons overlaying each other when more than one attachment is selected. Other than that, it works fine for me (Win 7 Ultimate, Excel 2010 Profession


Sub Attachment()
Dim filters As String
Dim filename1 As Variant
Dim f As Variant
Dim idx As Long

Sheets.Add.Name = "Attachments"
Sheets("SCR Form").Move Before:=Sheets("Attachments")

' Get the file name.

'filename1 = Application.GetOpenFilename(FileFilter:="Select Files(*.xls;*.xlsx;*.doc;*.docx;*.ppt),*.xls;*.xlsx;*.doc;*.docx;*.ppt", Title:="Select a file")
filename1 = Application.GetOpenFilename(FileFilter:="Select Files(*.xls;*.xlsx;*.doc;*.docx;*.ppt),*.xls;*.xlsx;*.doc;*.docx;*.ppt", Title:="Select a file", MultiSelect:=True)

If Not IsArray(filename1) Then

If filename1 = False Then

Application.DisplayAlerts = False
Worksheets("Attachments").Delete
Application.DisplayAlerts = True
Exit Sub
End If
Else

' Insert the file.
For Each f In filename1

idx = idx + 1
InsertPicture CStr(f), idx, Application.Selection
Next
End If
End Sub

Sub InsertPicture( _
ByVal filename1 As String, _
ByVal idx As Long, _
ByRef location As Range)
Dim objI As Object
Dim rngI As Range

Sheets("Attachments").Range("A2") = "Attachments Below"
Set myDocument = Sheets("Attachments")

Set objI = Sheets("Attachments").OLEObjects.Add(Filename:=filename1, Link:=False, DisplayAsIcon:=True, IconFileName:=filename1, IconLabel:=filename1) ', Width:=40, Height:=40)

objI.Top = idx * 30
objI.Left = 5
End Sub

Just tried closing it, and got an error there. Not sure what it was trying to do, but it was failing on Instr, doing a Left on that.