Results 1 to 5 of 5

Thread: msoFileDialogFilePicker problem [RESOLVED]

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2004
    Location
    Lucenec, Slovakia
    Posts
    154

    Resolved msoFileDialogFilePicker problem [RESOLVED]

    Hi guys,

    I would like to set colors of active workbook equal to a selected xls file. To do this I would like to use msoFileDialogFilePicker. I have the following code:

    VB Code:
    1. Private Sub CommandButton11_Click()
    2. If ActiveWorkbook Is Nothing Then
    3. MsgBox "At first open any .xls file!"
    4. Else
    5. Dim filename
    6. With Application.FileDialog(msoFileDialogFilePicker)
    7. .AllowMultiSelect = False
    8. .Filters.Clear
    9. .Filters.Add "Excel Files", "*.xls"
    10. .Show
    11. End With
    12. filename = Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Item(1)
    13. Application.EnableEvents = False
    14. Application.ScreenUpdating = False
    15. Workbooks.Open filename:=filename
    16. ActiveWindow.Visible = False
    17. ActiveWorkbook.Colors = Workbooks(filename).Colors
    18. Dim i, j
    19. i = 56
    20. For j = 1 To i
    21. UserForm2.Controls("label" & j).BackColor = ActiveWorkbook.Colors(j)
    22. Next j
    23. Workbooks(filename).Close
    24. Application.EnableEvents = True
    25. Application.ScreenUpdating = True
    26. End If
    27. End Sub


    This code fails me at:
    VB Code:
    1. ActiveWorkbook.Colors = Workbooks(filename).Colors

    Has anybody idea, why this code fails?

    Boris


    I have already done it. This code works:

    VB Code:
    1. Private Sub CommandButton11_Click()
    2. If ActiveWorkbook Is Nothing Then
    3. MsgBox "At first open any xls file!"
    4. Else
    5. Dim actwrbk
    6. actwrbk = ActiveWorkbook.Name
    7. With Application.FileDialog(msoFileDialogFilePicker)
    8. .AllowMultiSelect = False
    9. .Filters.Clear
    10. .Filters.Add "Select any excel file", "*.xls"
    11. .Show
    12. End With
    13. filename = Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Item(1)
    14. Application.EnableEvents = False
    15. Application.ScreenUpdating = False
    16. Workbooks.Open filename:=filename
    17. Workbooks(actwrbk).Sheets(1).Range("A1").Value = filename
    18.  
    19. 'last position of "\"
    20. Dim p, lenth1, lenth2, k, l
    21. lenth1 = Len(filename)
    22. l = Left(filename, 1)
    23. If l = "\" Then p = 1
    24. For k = 2 To lenth1
    25. l = Left(filename, k)
    26. l = right(l, 1)
    27. If l = "\" Then p = k
    28. Next k
    29. lenth2 = Len(filename)
    30. filename = right(filename, lenth2 - p)
    31. lenth1 = Len(filename)
    32. filename = Left(filename, lenth2 - 4)
    33.  
    34. Dim j
    35. For j = 1 To 56
    36. Workbooks(actwrbk).Colors(j) = Workbooks(filename).Colors(j)
    37. UserForm2.Controls("label" & j).BackColor = Workbooks(actwrbk).Colors(j)
    38. Next j
    39. label_control_tip
    40. Workbooks(filename).Close Savechanges:=False
    41. Application.EnableEvents = True
    42. Application.ScreenUpdating = True
    43. End If
    44. End Sub

    I had to attach thefe small routine which will find the last "\". After that I was able generate the name of file without path and extension.
    Last edited by bolcskei; Jun 28th, 2006 at 04:10 AM. Reason: [RESOLVED]
    Boris

    the mistake must be between the keyboard and the chair!

  2. #2
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: msoFileDialogFilePicker problem

    May have to do with the fact that your hiding/making invisible the opened workbook with a ActiveWindow.Visible = False yet it could be hiding your main workbook as the opened one may not be completely opened at that point. Using Active anything is usually flakey as the focus can change for any number of reasons.

    If you dont hide the workbook then does it work because a ActiveWorkbook.Colors = Workbooks(filename).Colors does work if the workbook is opened already. Switch to named workbooks instead of Active
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Nov 2004
    Location
    Lucenec, Slovakia
    Posts
    154

    Re: msoFileDialogFilePicker problem

    Quote Originally Posted by RobDog888
    May have to do with the fact that your hiding/making invisible the opened workbook with a ActiveWindow.Visible = False yet it could be hiding your main workbook as the opened one may not be completely opened at that point. Using Active anything is usually flakey as the focus can change for any number of reasons.

    If you dont hide the workbook then does it work because a ActiveWorkbook.Colors = Workbooks(filename).Colors does work if the workbook is opened already. Switch to named workbooks instead of Active
    I was checking the problem. I think the problem is, after I open the file I can just use the name of the file, without the filename path.
    thefore I have to from this:
    C:\Documents and Settings\bolcskei\Dokumenty\fa\TV\CF\CF 2006 1.0.xls
    make this
    CF 2006 1.0

    has you or anybody idea how can I do it. For example may I try it with FindLast and I will searching the last "/"?
    Boris

    the mistake must be between the keyboard and the chair!

  4. #4
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: msoFileDialogFilePicker problem [RESOLVED]

    You can parse teh filepath to get just the filename.
    VB Code:
    1. Dim sString As String
    2. sString = "C:\Documents and Settings\bolcskei\Dokumenty\fa\TV\CF\CF 2006 1.0.xls"
    3. sString = Mid$(sString, InStrRev(sString, "\") + 1)
    4. MsgBox sString 'CF 2006 1.0.xls
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Nov 2004
    Location
    Lucenec, Slovakia
    Posts
    154

    Re: msoFileDialogFilePicker problem [RESOLVED]

    Quote Originally Posted by RobDog888
    You can parse teh filepath to get just the filename.
    VB Code:
    1. Dim sString As String
    2. sString = "C:\Documents and Settings\bolcskei\Dokumenty\fa\TV\CF\CF 2006 1.0.xls"
    3. sString = Mid$(sString, InStrRev(sString, "\") + 1)
    4. MsgBox sString 'CF 2006 1.0.xls
    Thanks for a tip I will try it. Somethimes I am asking primitive things, because I am not a programmer.
    Boris

    the mistake must be between the keyboard and the chair!

Posting Permissions

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



Click Here to Expand Forum to Full Width