A
Alex St-Pierre
Hi,
I would like to have a tool that allow the user to selected an excel file
and update informations inside the file. The process is in two steps.
1- When you click on a button inside the Form, the user select the file.
When this is done, all the sheets are added to a ListView. The user can
select which sheets he want to copy inside the active workbook. (This work
fine)
2- When the Update button is clicked, the process begin.
At step 1, I want something that is very fast. Opening the workbook is very
long. So , I decided to use function "ListSheet". The problem is that I would
like to add information to monDicoSource. i.e. the "Type" of the sheet. To
know what is the type, I most do a: Sheets(i).Evaluate("Type") Is there a way
to add the type using the following macro or should I open the workbook
inside excel to be able to evaluate it.
At step 2, I open the workbook using standard code. What is the fastest way
to open a workbook, make treatment and close it? Is is to open a new Excel
App and make an Application.Visible = False?
Treatment is: Copy Sheet and Copy some named range value.
Thanks a lot!
Alex
Dim monDicoSource
Function ListSheet(FullPathFile$) As Boolean
Dim Con As Object, Cat As Object, tbl As Object
Dim sTemp As String, c As String, pos As Long
Set Con = CreateObject("ADODB.Connection")
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" _
& FullPathFile & ";" & "Extended Properties=Excel 8.0;"
Set Cat = CreateObject("ADOX.Catalog")
Set Cat.ActiveConnection = Con
For Each tbl In Cat.Tables
c = tbl.Name
pos = InStrRev(tbl.Name, "$")
If pos = Len(tbl.Name) Then
sTemp = Left$(tbl.Name, pos - 1) 'ou bien, $ CBool est convert
boolean
sTemp = Replace(sTemp, "#", ".")
monDicoSource.Add sTemp, sTemp
End If
Next tbl
Set Cat = Nothing: Con.Close: Set Con = Nothing: Set tbl = Nothing
End Function
I would like to have a tool that allow the user to selected an excel file
and update informations inside the file. The process is in two steps.
1- When you click on a button inside the Form, the user select the file.
When this is done, all the sheets are added to a ListView. The user can
select which sheets he want to copy inside the active workbook. (This work
fine)
2- When the Update button is clicked, the process begin.
At step 1, I want something that is very fast. Opening the workbook is very
long. So , I decided to use function "ListSheet". The problem is that I would
like to add information to monDicoSource. i.e. the "Type" of the sheet. To
know what is the type, I most do a: Sheets(i).Evaluate("Type") Is there a way
to add the type using the following macro or should I open the workbook
inside excel to be able to evaluate it.
At step 2, I open the workbook using standard code. What is the fastest way
to open a workbook, make treatment and close it? Is is to open a new Excel
App and make an Application.Visible = False?
Treatment is: Copy Sheet and Copy some named range value.
Thanks a lot!
Alex
Dim monDicoSource
Function ListSheet(FullPathFile$) As Boolean
Dim Con As Object, Cat As Object, tbl As Object
Dim sTemp As String, c As String, pos As Long
Set Con = CreateObject("ADODB.Connection")
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" _
& FullPathFile & ";" & "Extended Properties=Excel 8.0;"
Set Cat = CreateObject("ADOX.Catalog")
Set Cat.ActiveConnection = Con
For Each tbl In Cat.Tables
c = tbl.Name
pos = InStrRev(tbl.Name, "$")
If pos = Len(tbl.Name) Then
sTemp = Left$(tbl.Name, pos - 1) 'ou bien, $ CBool est convert
boolean
sTemp = Replace(sTemp, "#", ".")
monDicoSource.Add sTemp, sTemp
End If
Next tbl
Set Cat = Nothing: Con.Close: Set Con = Nothing: Set tbl = Nothing
End Function