Solved: openfile dialog retain openned name in variable

[mperrah]

I'm trying to use code to import data from a workbook,
into the open workbook with the updated VBA code

I found this code that opens the file great,
I need to save the openned files name to enter into a cell
to run a second sub that needs the file name to complete the import.

Sub versionUpdate()
Dim recent As String
Dim current As String
Dim nCount As Long
Dim fname As String

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)

.Show

For nCount = 1 To .SelectedItems.Count
Workbooks.Open .SelectedItems(nCount)
Exit For
Next nCount

' fname = .SelectedItems(nCount).Name ' this is not working, something like this maybe?
' MsgBox "old file chosen " & fname & ""
End With
Exit Sub

' second part using file name saved in cell "AX2"
Sub update()
recent = Worksheets("QCDetail").[AX2]
current = Worksheets("QCDetail").[AY2]
Windows(recent).Activate
Sheets("QCDetail").Select
Windows(recent).Activate
Sheets("QCDetail").Select
Range("AV2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(current).Activate
Sheets("QCDetail").Select
Range("AV2").Select
ActiveSheet.Paste
end sub

[lucas]

Hi Mark,
I'm not sure exactly but maybe...
Sub RetrieveFileName()
Dim sFileName As String
'Show the open dialog and pass the selected _
file name to the String variable "sFileName"
sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub
MsgBox sFileName
End Sub

[mikerickson]

How about

Dim myFileNames(1 to 10) as string
For nCount = 1 To .SelectedItems.Count
myFileNames(nCount)=.SelectedItems(ncount):Rem Added Line
Workbooks.Open .SelectedItems(nCount)
Exit For
Next nCount

[Charlize]

Is it multiselect or not. If yes, you are exiting the loop that gets the filenames after the first time. Loop through the selected items and write the value of each filename to a sheet and later on you can process each name on that sheet or open the file and do your stuff to every file in selected items.

So no exit for but a call update instead for each file in selected items.

[mperrah]

Sorry I haven't got back right away.
This is what I came up with that seems to work.
But I only need one file.
I don't need to loop for multiselect.
I tried modifying the code to just use the single selected file without succes.
I will try some of your suggestions.

Also, this file is starting to suffer from bloating.
I am updating the vba and want to make an update to the user as easy as possible.
I let them open this file and let them search for their most recent.
then I select the data from their old file and paste into mine.
then close the old file when done.
During debugging and development my file went from 700k to 2mb

I plan to keep it 97/2003 compatble. in 2007 it slims to 700k
but in 97/2003 it's back to 2+ mb
any ideas for de-bloating.
Also, updating the detail page sub runs very slow.
I'll try to shrink it enoough to post for any takers.
Sub versionUpdate()
Dim wbRecent As String
Dim wbCurrent As String
Dim nCount As Long
Dim w As Workbook
Dim versionUpdate As VbMsgBoxResult

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.Show
For nCount = 1 To .SelectedItems.Count
Workbooks.Open .SelectedItems(nCount)
Exit For
Next nCount
End With
Application.DisplayAlerts = False

ThisWorkbook.Worksheets("library").Visible = True

For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
ThisWorkbook.Worksheets("library").Range("N26").Value = w.Name
ThisWorkbook.Worksheets("library").Range("N28").Value = ThisWorkbook.Name
End If
Next w

wbRecent = ThisWorkbook.Worksheets("library").[N26]
wbCurrent = ThisWorkbook.Worksheets("library").[N28]

Windows(wbRecent).Visible = True

Windows(wbRecent).Activate
Sheets("library").Visible = True
Sheets("library").Select
Range("N29").Select
Selection.Copy
ThisWorkbook.Activate
Sheets("library").Select
Range("N27").Select
ActiveSheet.Paste
With Worksheets("library").[N27]
If .Value = Worksheets("library").[N29] Then
MsgBox "Your version is up to date", , "Version Update"
Exit Sub

Else
versionUpdate = MsgBox("Do you want to import old data to this new version?", _
vbYesNo, "Version Update")
If versionUpdate = vbYes Then

Call DeleteMenu
Call MasterUpdate

ElseIf versionUpdate = vbNo Then
Exit Sub

ElseIf versionUpdate = vbCancel Then
Exit Sub
End If
End If
End With

End Sub

[Charlize]

This is for only allowing a single file selection (Even if some idiot holds down ctrl it won't allow multiple selections.) ' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Filters.Add "Excel files", "*.xls", 1
.Show
If .SelectedItems.Count = 0 Then
MsgBox "No file selected ! Exiting this procedure", vbExclamation
Exit Sub
Else
MsgBox "File chosen : " & .SelectedItems(1)
'workbooks.open .selecteditems(1)
End If
End With

[mperrah]

Thanks Charlize
Works like a charm.
Do have any suggestions for bloat?
Mark

[Charlize]

Do have any suggestions for bloat?
MarkWell, it's not certain, but have you saved it under a different name to see how big it was. If it's smaller, delete old one and rename new one.

A link to a utility called codecleaner : http://www.bmsltd.ie/DLCount/DLCount.asp?file=CodeCleaner.exe or you can browse to www.bmsltd.ie/mvp (http://www.bmsltd.ie/mvp)