Is this proper syntax?

  • Thread starter Thread starter Shelby Haynes
  • Start date Start date
S

Shelby Haynes

Sub GetMGAM_Files()


MGAMFileName = Application.GetOpenFilename("Excel Files (*.xls),*.xls",
, Title:=" Select MGAM File to Import")

' Exit if dialog box canceled
If MGAMFileName = False Then
MGAMFileStop = MsgBox(" No file was selected. Would you like to end
the reconciliation process.?", vbYesNo, "Question")
If MGAMFileStop = vbNo Then
GetMGAM_Files
Else
RecBook.Close (False)
End
End If
End If


The reason I am considering doing it this way is that sometimes when I
insert my USB flash, though it is recognized by the CPU, if I bring up
the open dialog box in Excel immediately after, the drive letter won't
be in the dialog box. But the second time I bring up the open dialog
box the drive is there.
 
I don't think it's a VBA/Excel problem--maybe it just takes a bit for the USB
flash drive to get recognized by Windows.

But I would stay away from using "End" by itself--not "End If" or "end sub". It
can do somethings that you may not like -- like resetting global variables.

I'm not sure what should follow this little snippet, but I'd use something more
like:

Option Explicit
Sub GetMGAM_Files()

Dim MGAMFileName As Variant
Dim MGAMFileStop As Long
Dim RecBook As Workbook

Set RecBook = Workbooks.Add 'just so I can close it later!

MGAMFileStop = vbNo
Do
MGAMFileName = Application.GetOpenFilename _
("Excel Files (*.xls),*.xls", _
Title:="Select MGAM File to Import")

' Exit if dialog box canceled
If MGAMFileName = False Then
MGAMFileStop = MsgBox("No file was selected." & _
" Would you like to end the reconciliation process.?", _
vbYesNo, "Question")
If MGAMFileStop = vbNo Then
'keep asking...
Else
RecBook.Close savechanges:=False
Exit Do
End If
End If
Loop

End Sub
 
Dave Peterson shared this with us in microsoft.public.excel:
I don't think it's a VBA/Excel problem--maybe it just takes a bit for
the USB flash drive to get recognized by Windows.
Click to expand...

Don't doubt, that is exactly what is happening.
An already open file dialog or explorer window won't show the new drive
letter. A new file dialog or explorer window will show the new drive
letter when the USB drive is fully recognized. This may take anywhere
between 10 seconds and 10 minutes, depending on system speed and USB
drive content.
Why?
Because Windows scans the content of a new drive to determine what kind
of media it has. If Windows finds a lot of pictures, it may propose you
a slideshow.
I have observed this behaviour with a USB 2.0 160 GB external hard
drive on a much slower USB 1.1 port.
 
Back
Top