Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GetOpenFileName (ExcelXP)

    Having a problem with a GetOpenFileName function I'm using in one of my sheets. The code below (I removed definitions and other routines) always worked (and still works under WIndows98). However when I run under WinXP there is a problem... The FIRST time I call the routine all is working fine. After that, each time I get run-time erors (which in itself is a mystery to me as I think I re-initialize each time).

    I'm not using the 'standard' GetOpenFileName functionality given by EXCEL-VBA as I want to set the starting diectory (and remember that the next time the call is made... variable sDIR).

    I've highlighted the location were the problem occurs (the error is a result of that and is in the next line). It appears that after things have worked as expected and documented (I checked) the first time, each next attempt returns a zero-value for the "<font color=red>uFileDlgData.nFileOffset</font color=red>" property.... Again, in Win98 it always worked!!
    The documentation I read does describe differences in implemenation of this call for Win98 and WinXP but I cannot lead that back to this variable being set to zero...

    I'd very much appreciate any help here.

    Erik Jan <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    PS. I'm not sure how I got the code below: I didn't write this myself (although I might have tweaked it), must have gotten it from somewhere (a long time ago)

    <font color=blue>Sub funOpenCommDlg(ByVal sFilter As String, ByVal sDlgTitle As String, ByVal sDir As String, ByVal sDefExt As String, ByVal bMustExist As Boolean, ByVal bMultiSelect As Boolean)
    '
    Dim sFullName As String, sFileName As String
    Dim lResult As Long, lFlags As Long, i As Integer, j As Integer, k As Integer
    Dim uFileDlgData As OpenFilename
    ' Define the filter string, converting all "|" to nulls
    sFilter = funSubstitute(sFilter, "|", Chr$(0))
    ' Allocate string space for the returned strings.
    sFullName = Space$(3000)
    sFileName = Space$(3000)
    '
    lFlags = OFN_HIDEREADONLY Or OFN_NOCHANGEDIR
    If bMustExist Then lFlags = lFlags Or OFN_FILEMUSTEXIST
    If bMultiSelect Then lFlags = lFlags Or OFN_ALLOWMULTISELECT
    lFlags = lFlags Or OFN_EXPLORER
    ' Set up the data structure before you call the GetOpenFilename
    With uFileDlgData
    .hwndOwner = FindWindow("XLMAIN", Application.Caption)
    .lpstrFilter = sFilter
    .iFilterIndex = 1
    .lpstrFile = sFullName & Chr$(0)
    .nMaxFile = Len(sFullName) + 1
    .lpstrFileTitle = sFileName & Chr$(0)
    .nMaxFileTitle = Len(sFileName) + 1
    .lpstrTitle = sDlgTitle
    .flags = lFlags
    .lpstrDefExt = sDefExt
    .hInstance = 0
    .lpstrCustomFilter = 0&
    .nMaxCustFilter = 0
    .lpstrInitialDir = sDir
    .nFileOffset = 0
    .nFileExtension = 0
    .lCustData = 0
    .lpfnHook = 0
    .lpTemplateName = ""
    .lStructSize = Len(uFileDlgData)
    End With
    ' This will pass the desired data structure to the Windows API, which will in turn use it to display the Open Dialog form.
    lResult = GetOpenFileName(uFileDlgData)
    ' Return the file selected
    If lResult = 0 Then
    ReDim FileResult(1)
    FileResult(1) = ""
    Else
    ReDim FileResult(400)
    k = 1
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">j = 1 + uFileDlgData.nFileOffset</span hi>
    FileResult(1) = Left(uFileDlgData.lpstrFile, j - 2)

    If Right(FileResult(1), 1) <> "" Then FileResult(1) = FileResult(1) & ""

    i = InStr(j, uFileDlgData.lpstrFile, Chr(0))
    Do
    k = k + 1
    FileResult(k) = Mid(uFileDlgData.lpstrFile, j, i - j)
    j = i + 1
    i = InStr(j, uFileDlgData.lpstrFile, Chr(0))
    Loop Until j = i Or i = Len(uFileDlgData.lpstrFile)
    ReDim Preserve FileResult(k)
    End If
    End Sub</font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetOpenFileName (ExcelXP)

    You can use ChDrive and ChDir to set the drive and folder path for GetOpenFileName. You can store the values used in string variables:

    Dim strDrive As String
    Dim strFolder As String
    Dim fileToOpen As Variant

    strDrive = "C"
    strFolder = "C:MyDocsExcel"

    ChDrive strDrive
    ChDir strFolder
    fileToOpen = Application.GetOpenFileName("Excel workbooks", "*.xls")
    If Not fileToOpen = False Then
    ...

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFileName (ExcelXP)

    Thanks Hans,

    I'm amost ashamed to admit but it seems that this should work too... I wonder why I ever chose to go for that 'extended' call?? I know I'm using MultiSelect too, but see that is also supported in the 'satdard' call. Maybe it's because this might not be available in this way in Win95/Excel97??? That's where half of the instances of my program run...

    Any more specific functionality that I might have overlooked?? I'm willing to make the change as you suggest but would like to prevent to do things first, only to find out afterwards why I didn't go that route before...

    Erik Jan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetOpenFileName (ExcelXP)

    According to MSKB article XL97: How to Use the GetOpenFilename Method, MultiSelect was already available in Excel 97.

    I don't know of other specific functionality - look up GetOpenFileName in the online help or in the object browser.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFileName (ExcelXP)

    It was in XL95/5 too.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFileName (ExcelXP)

    Ok, I can see this working (actually started) but see new clouds as well... Now I'll have to extract the directory from the returned string(s), see how to safely use CHdir and Chdrive with mapped or not mapped network drives...

    Without wanting to abondon this, I'm still wondering why my initial call behaves so strange... I must be doing something wrong. If I / you / we can solve that -simple?- call, I wouldn't have to re-do anything...

    Suggestions?

    Erik Jan

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetOpenFileName (ExcelXP)

    You left out all the declarations needed to make the code work; can you attach a workbook with working (though problematic) code?

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFileName (ExcelXP)

    Yep... sorry I didn't do that earlier... I've attached the subroutine and related procedures... (just import into a sheet as normal module should do the trick). The call should be added in a short sub and you might need one or two Public DIM statements. Let me know if this helps, if needed I can also supply a working example...

    PS. Had to rename the file to TXT, it is really originally a BAS...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetOpenFileName (ExcelXP)

    I am using Excel 2002 SP-2 on Windows XP Pro SP-1

    I put your code in a module, and added a few lines at the end of funOpenCommDlg to be able to see the result:

    For i = LBound(FileResult) To UBound(FileResult)
    Debug.Print FileResult(i)
    Next i

    and I wrote a little test procedure:

    Sub Test()
    funOpenCommDlg "Excel|*.xls", "Test", "F:Excel", "xls", True, True
    End Sub

    (F:Excel is a network folder containing Excel workbooks) I am able to call Test an unlimited number of times without running into a problem. So the problem doesn't seem to be in Windows XP per se. Since I can't reproduce it, I really don't know where to look. The Windows API functions are standard, they should work on any Windows version later than 3.x.

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFileName (ExcelXP)

    OK, I hear you... will try and isolate the problem in a separate Excel-sheet and post it here

    Erik Jan

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetOpenFileName (ExcelXP)

    Good idea. In the meantime I have also tested on Windows XP Home. No problem either, so it's not an XP Home vs XP Pro issue.

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFileName (ExcelXP)

    ...this is difficult <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>... now I'm at a stage where it works continuously unless I open the VB-Editor (Alt-F11).. then it fails... The application however doesn't act like that but fails after one good run each time... I'll spend some more time later trying to isolate the behavior.. As soon as I can reproduce something in the smallest and simplest possible code, I'll share

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: GetOpenFileName (ExcelXP)

    Hi Erik Jan,
    I get the same issue as you if I run the funOpenCommDlg routine from the immediate window (i.e. it runs once, then I get "Sub or Function not defined") but if I run it from a test procedure it works fine every time. Now to figure out why....
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFileName (ExcelXP)

    I started to look at this again (still a mystery), guess what I found...

    Here's -part of - my definitions:

    Private Type OpenFilename
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Long</span hi>
    ...

    It's the last line that triggered me... if you do a search on the net you'll find the above form but also the string-form:

    lpstrCustomFilter As <span style="background-color: #FFFF00; color: #000000; font-weight: bold">String</span hi>

    Now I'm sure this could mess-up things pretty much... and maybe explain the strange behavior I've been seeing.

    MS, seems to go for the string definition but are all other definitions wrong then????

    What is going on here??? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Erik Jan

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetOpenFileName (ExcelXP)

    The letters lpstr stand for Long Pointer to String. The "official" definition is As Long - the long integer is an address value (more or less) specifying where the string can be found. In C(++) it is common to think of a string as a memory location, but in VBA this is not usual. VBA allows you to specify a string, and will silently perform the necessary conversions. You should use the As String declaration in VBA.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •