R
Ralf Meuser
Hi there
I'm importing a csv-file to Excel 2003 with a vbs script.
Once imported, I want to create a new sheet and extract datas from sheet1.
Here I have two problems.
1. problem
Creation sheet is ok but I want it to be added after the last sheet and not
as the first sheet
....
Set objWb = objXl.WorkBooks.Add
Set objWb = objXL.ActiveWorkBook.WorkSheets(1)
Set objWb2 = objXL.ActiveWorkBook.WorkSheets(2)
Set objWb3 = objXL.ActiveWorkBook.WorkSheets(3)
Set objWb4 = objXL.ActiveWorkBook.WorkSheets.Add
objWb4.Name = "Feuil4"
objWb4.Select
objWb4.Move After=objXL.ActiveWorkBook.WorkSheets(3) ' the error is here
....
This doesn't work.
2. problem
In the Sheet1 I have a column week and would like to add all numbers by
week.
I tryed this but it doesn't work. The error I get is:
Ligne: 160 Caract.: 1 Error: Error not specified Code: 80004005 Source
(null)
....
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\uti\KPI\KPI01.xls; Extended Properties=""Excel 8.0;HDR=Yes;"";"
rs.Open "SELECT LIG,sum(case when WEEK=27 then PT else 0 end) as m1,sum(case
when WEEK=28 then PT else 0 end) as m2,sum(case when WEEK=29 then PT else 0
end) as m3,sum(case when WEEK=30 then PT else 0 end) as m4 From [Sheet1$]
Group BY LIG",cnn,3,3,&H0001 ' here is my error
'rs.Open "SELECT LIG,WEEK,sum(PB) as M1,sum(PT) as M2,sum(COUT) as
M3,sum(CA) as M4 From [Sheet1$] Group BY LIG,WEEK",cnn,3,3,&H0001 ' This
one is OK
i = 2
Do While Not rs.EOF
objWb3.Cells(i, 1) = rs("LIG")
objWb3.Cells(i, 2) = rs("M1")
objWb3.Cells(i, 3) = rs("M2")
objWb3.Cells(i, 4) = rs("M3")
objWb3.Cells(i, 5) = rs("M4")
rs.MoveNext
i = i + 1
Loop
....
Thanks in adavnce for any help
Best regards
Ralf
I'm importing a csv-file to Excel 2003 with a vbs script.
Once imported, I want to create a new sheet and extract datas from sheet1.
Here I have two problems.
1. problem
Creation sheet is ok but I want it to be added after the last sheet and not
as the first sheet
....
Set objWb = objXl.WorkBooks.Add
Set objWb = objXL.ActiveWorkBook.WorkSheets(1)
Set objWb2 = objXL.ActiveWorkBook.WorkSheets(2)
Set objWb3 = objXL.ActiveWorkBook.WorkSheets(3)
Set objWb4 = objXL.ActiveWorkBook.WorkSheets.Add
objWb4.Name = "Feuil4"
objWb4.Select
objWb4.Move After=objXL.ActiveWorkBook.WorkSheets(3) ' the error is here
....
This doesn't work.
2. problem
In the Sheet1 I have a column week and would like to add all numbers by
week.
I tryed this but it doesn't work. The error I get is:
Ligne: 160 Caract.: 1 Error: Error not specified Code: 80004005 Source
(null)
....
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\uti\KPI\KPI01.xls; Extended Properties=""Excel 8.0;HDR=Yes;"";"
rs.Open "SELECT LIG,sum(case when WEEK=27 then PT else 0 end) as m1,sum(case
when WEEK=28 then PT else 0 end) as m2,sum(case when WEEK=29 then PT else 0
end) as m3,sum(case when WEEK=30 then PT else 0 end) as m4 From [Sheet1$]
Group BY LIG",cnn,3,3,&H0001 ' here is my error
'rs.Open "SELECT LIG,WEEK,sum(PB) as M1,sum(PT) as M2,sum(COUT) as
M3,sum(CA) as M4 From [Sheet1$] Group BY LIG,WEEK",cnn,3,3,&H0001 ' This
one is OK
i = 2
Do While Not rs.EOF
objWb3.Cells(i, 1) = rs("LIG")
objWb3.Cells(i, 2) = rs("M1")
objWb3.Cells(i, 3) = rs("M2")
objWb3.Cells(i, 4) = rs("M3")
objWb3.Cells(i, 5) = rs("M4")
rs.MoveNext
i = i + 1
Loop
....
Thanks in adavnce for any help
Best regards
Ralf