-
Jun 28th, 2006, 12:12 AM
#1
Thread Starter
Addicted Member
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:
Private Sub CommandButton11_Click()
If ActiveWorkbook Is Nothing Then
MsgBox "At first open any .xls file!"
Else
Dim filename
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls"
.Show
End With
filename = Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Item(1)
Application.EnableEvents = False
Application.ScreenUpdating = False
Workbooks.Open filename:=filename
ActiveWindow.Visible = False
ActiveWorkbook.Colors = Workbooks(filename).Colors
Dim i, j
i = 56
For j = 1 To i
UserForm2.Controls("label" & j).BackColor = ActiveWorkbook.Colors(j)
Next j
Workbooks(filename).Close
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
This code fails me at:
VB Code:
ActiveWorkbook.Colors = Workbooks(filename).Colors
Has anybody idea, why this code fails?
Boris
I have already done it. This code works:
VB Code:
Private Sub CommandButton11_Click()
If ActiveWorkbook Is Nothing Then
MsgBox "At first open any xls file!"
Else
Dim actwrbk
actwrbk = ActiveWorkbook.Name
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Select any excel file", "*.xls"
.Show
End With
filename = Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Item(1)
Application.EnableEvents = False
Application.ScreenUpdating = False
Workbooks.Open filename:=filename
Workbooks(actwrbk).Sheets(1).Range("A1").Value = filename
'last position of "\"
Dim p, lenth1, lenth2, k, l
lenth1 = Len(filename)
l = Left(filename, 1)
If l = "\" Then p = 1
For k = 2 To lenth1
l = Left(filename, k)
l = right(l, 1)
If l = "\" Then p = k
Next k
lenth2 = Len(filename)
filename = right(filename, lenth2 - p)
lenth1 = Len(filename)
filename = Left(filename, lenth2 - 4)
Dim j
For j = 1 To 56
Workbooks(actwrbk).Colors(j) = Workbooks(filename).Colors(j)
UserForm2.Controls("label" & j).BackColor = Workbooks(actwrbk).Colors(j)
Next j
label_control_tip
Workbooks(filename).Close Savechanges:=False
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
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!
-
Jun 28th, 2006, 01:45 AM
#2
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Jun 28th, 2006, 02:37 AM
#3
Thread Starter
Addicted Member
Re: msoFileDialogFilePicker problem
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!
-
Jun 28th, 2006, 12:48 PM
#4
Re: msoFileDialogFilePicker problem [RESOLVED]
You can parse teh filepath to get just the filename.
VB Code:
Dim sString As String
sString = "C:\Documents and Settings\bolcskei\Dokumenty\fa\TV\CF\CF 2006 1.0.xls"
sString = Mid$(sString, InStrRev(sString, "\") + 1)
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Jun 29th, 2006, 12:03 AM
#5
Thread Starter
Addicted Member
Re: msoFileDialogFilePicker problem [RESOLVED]
Originally Posted by RobDog888
You can parse teh filepath to get just the filename.
VB Code:
Dim sString As String
sString = "C:\Documents and Settings\bolcskei\Dokumenty\fa\TV\CF\CF 2006 1.0.xls"
sString = Mid$(sString, InStrRev(sString, "\") + 1)
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|