2 problems with my vbs script

[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

 

[Jim Rech]

You can't use named arguments in a script.

Set objWb = objXl.WorkBooks.Add
Set objWs = objWb.WorkSheets(1)
Set objWs2 = objWb.WorkSheets(2)
Set objWs3 = objWb.WorkSheets(3)
Set objWs4 = objWb.WorkSheets.Add
objWs4.Name = "Feuil4"
objWs4.Move ,objWs3

Your code assumes the user hasn't changed the defualt 3 worksheets in a new
workbook. Probably safe but it wasn't with me.

--
Jim
| 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
|
|
|

 

[Brian Withun]

1. problem

Try placing the new sheet where it belongs upon creation, rather than
moving it.


Sub myfunc()
Sheets.Add after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "Feuil4"
End Sub


I can't give you the actual code that you need without seeing your
full sub/function definition

Good luck!

Brian Herbert Withun

 

[Guest]

For problem 1:

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(after:=objWb.Worksheets(objWb.Worksheets.count)
objWb4.Name = "Feuil4"

Plus you are making an assumption that a new workbook has 3 sheets. The
number of sheets in a new workbook can be set by the user (0r by you, but
only if a workbook is open and then it would be for subsequent workbooks).

The property is a property of the application and is

SheetsInNewWorkbook