itipu
07-17-2008, 03:51 PM
Hi All,
Would appreciate some tips or help.. Basically what my code in the sample does it on click of the button it asks user to open a folder, gets names of all excel files in the folder, dynamically creates a form and adds checkboxes with file names to that folder.
Now I add checkboxes as follows:
For lngFileIndex = lngLwrBnd_c To fs.FoundFiles.Count
Set wbcrnt = Excel.Workbooks.Open(fs.FoundFiles(lngFileIndex), False, _
False, Password:="foo")
Dim mycheckbox As Object
Set mycheckbox = frmSelect.Designer.Controls.Add("Forms.CheckBox.1")
With mycheckbox
.Caption = wbcrnt.Name
.Left = 10
.Top = 10 + (30 * i)
.Height = 20
.Width = 300
End With
wbcrnt.Close
i = i + 1
Next
So that creates as many checkboxes as there are files in the folder.
Ideally I want to than find out which are checked and their names, so that I can manipulate selected files afterwards.. So far I tried something like that but nothing seems to work:
Dim w As Integer
For w = 1 To i
With frmSelect.CodeModule
x = .CountOfLines
.InsertLines x + 1, "Public Sub ChBx()"
.InsertLines x + 2, " If frmSelect.controls("checkbox" & w).Value = True Then"
.InsertLines x + 3, " msgbox 7"
.InsertLines x + 4, " end if"
End With
MsgBox "checked"
End If
Next w
Any suggestion would be much appreciated..
The other small thing is, at the moment to get file names I use the following:
Set wbcrnt = Excel.Workbooks.Open(fs.FoundFiles(lngFileIndex), False, _
False, Password:="foo")
and
With mycheckbox
.Caption = wbcrnt.Name
However that also opend xls files in the folder, I was not able to figure out how to do the same and NOT open files but also NOT get the full path in the name, eg - "fs.FoundFiles"
Thanks a lot
mike
Would appreciate some tips or help.. Basically what my code in the sample does it on click of the button it asks user to open a folder, gets names of all excel files in the folder, dynamically creates a form and adds checkboxes with file names to that folder.
Now I add checkboxes as follows:
For lngFileIndex = lngLwrBnd_c To fs.FoundFiles.Count
Set wbcrnt = Excel.Workbooks.Open(fs.FoundFiles(lngFileIndex), False, _
False, Password:="foo")
Dim mycheckbox As Object
Set mycheckbox = frmSelect.Designer.Controls.Add("Forms.CheckBox.1")
With mycheckbox
.Caption = wbcrnt.Name
.Left = 10
.Top = 10 + (30 * i)
.Height = 20
.Width = 300
End With
wbcrnt.Close
i = i + 1
Next
So that creates as many checkboxes as there are files in the folder.
Ideally I want to than find out which are checked and their names, so that I can manipulate selected files afterwards.. So far I tried something like that but nothing seems to work:
Dim w As Integer
For w = 1 To i
With frmSelect.CodeModule
x = .CountOfLines
.InsertLines x + 1, "Public Sub ChBx()"
.InsertLines x + 2, " If frmSelect.controls("checkbox" & w).Value = True Then"
.InsertLines x + 3, " msgbox 7"
.InsertLines x + 4, " end if"
End With
MsgBox "checked"
End If
Next w
Any suggestion would be much appreciated..
The other small thing is, at the moment to get file names I use the following:
Set wbcrnt = Excel.Workbooks.Open(fs.FoundFiles(lngFileIndex), False, _
False, Password:="foo")
and
With mycheckbox
.Caption = wbcrnt.Name
However that also opend xls files in the folder, I was not able to figure out how to do the same and NOT open files but also NOT get the full path in the name, eg - "fs.FoundFiles"
Thanks a lot
mike