Wsh to import Csv and create a second sheet with sql

[Ralf Meuser]

Hi there

I want to import into Excel a csv file and afterwards create a second sheet
summing datas.

The first part I found on the net and is now working.
For the second part I found a Macro which I tryed to ajust in my script.
Since I'm new to Wsh I really don't know where to look what is wrong.

I hope someboby can help me out.

Thanks in avance for any help.
Best regards
Ralf


Option Explicit
Const vbNormal = 1 ' window style

DIM objXL, objWb, objR, objTab ' Excel object variables
DIM Title, Text, tmp, i, j, file, name, FileName

Title = "WSH sample - by G. Born"

' here you may set the name of the file to be imported
file = "S:\Uniface\KPI04.TXT" ' must be located in the script folder

' create an Excel object reference
Set objXL = WScript.CreateObject ("Excel.Application")

' set the Excel window properties (not absolutely necessary)
objXL.WindowState = vbNormal ' Normal
objXL.Height = 300 ' height
objXL.Width = 400 ' width
objXL.Left = 40 ' X-Position
objXL.Top = 20 ' Y-Position
objXL.Visible = true ' show window

' Create new Workbook (needed for import the CSV file=
Set objWb = objXl.WorkBooks.Add

' Get the first loaded worksheet object of the current workbook
Set objWb = objXL.ActiveWorkBook.WorkSheets(1)
objWb.Activate ' not absolutely necessary (for CSV)
' Now invoke the import wizard

'Set objTab = objWb.QueryTables.Add ("TEXT;"+GetPath + file,
objWb.Range("A1"))
Set objTab = objWb.QueryTables.Add ("TEXT;"+file, objWb.Range("A1"))


' here comes the mumbo jumbo to set all the properties for the wizard
' Oh Microsoft, how do I wish to has a With feature or a possibility to
' pass named arguments to methods ....
objTab.Name = "Names"
objTab.FieldNames = True
objTab.RowNumbers = False
objTab.FillAdjacentFormulas = False
objTab.PreserveFormatting = True
objTab.RefreshOnFileOpen = False
objTab.RefreshStyle = 1 'xlInsertDeleteCells
objTab.SavePassword = False
objTab.SaveData = True
objTab.AdjustColumnWidth = True
objTab.RefreshPeriod = 0
objTab.TextFilePromptOnRefresh = False
objTab.TextFilePlatform = 2 'xlWindows
objTab.TextFileStartRow = 1
objTab.TextFileParseType = 1 'xlDelimited
objTab.TextFileTextQualifier = -4142 ' xlTextQualifierNone
objTab.TextFileConsecutiveDelimiter = False
objTab.TextFileTabDelimiter = False ' ### my delimiters
objTab.TextFileSemicolonDelimiter = True
objTab.TextFileCommaDelimiter = False
objTab.TextFileSpaceDelimiter = False
objTab.TextFileColumnDataTypes = Array(1,1,1,2,1,2,1,1,1,1,1)
'xlGeneralFormat General 1
'xlTextFormat Text 2
'xlMDYFormat Month-Day-Year 3
'xlDMYFormat Day-Month-Year 4
'xlYMDFormat Year-Month-Day 5
'xlMYDFormat Month-Year-Day 6
'xlDYMFormat Day-Year-Month 7
'xlYDMFormat Year-Day-Month 8
'xlSkipColumn Skip 9
objTab.Refresh False

'WScript.Echo "We have loaded the worksheet"

' demonstrate how to read the column header values
Text = "Worksheet " + objWb.name + vbCRLF
Text = Text + "Column titles" + vbCRLF
Text = Text + CStr(objWb.Cells(1, 1).Value) + vbTab
Text = Text + CStr(objWb.Cells(1, 2).Value) + vbCRLF

' show some cell values (using the "hard coded method")
Text = Text + CStr(objWb.Cells(2, 1).Value) + vbTab
Text = Text + CStr(objWb.Cells(2, 2).Value) + vbCRLF
Text = Text + CStr(objWb.Cells(3, 1).Value) + vbTab
Text = Text + CStr(objWb.Cells(3, 2).Value) + vbCRLF
Text = Text + CStr(objWb.Cells(4, 1).Value) + vbTab
Text = Text + CStr(objWb.Cells(4, 2).Value) + vbCRLF

'' Show results
'MsgBox Text, vbOkOnly+ vbInformation, Title
'objXL.ActiveSheet.PrintOut ' print Worksheet
'WScript.Echo "We are printing, close after printing"

'************************************************
' starting second part
'************************************************
WScript.Echo "We start now our SQL staff"
ActiveWorkbook.Names.Add Name:="MaBd",
RefersTo:=Sheets(1).[A1].CurrentRegion
Dim cnn,rs,sql
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cnn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.Path & "\" & ThisWorkbook.Name
cnn.Open
Sql = "SELECT ANNEE,MOIS,CLIENT,NOM,GRP_CLI,sum(CA) as TCA,sum(MB) as
TMB,sum(NBR_POS) as TNBR_POS,sum(PB) as TPB,sum(PT) as TPT From MaBD Group
BY ANNEE,MOIS,CLIENT,NOM,GRP_CLI"
Set rs = cnn.Execute(Sql)
i = 2
Do While Not rs.EOF
Sheets(3).Cells(i, 1) = rs("ANNEE")
Sheets(3).Cells(i, 2) = rs("MOIS")
Sheets(3).Cells(i, 3) = rs("CLIENT")
Sheets(3).Cells(i, 4) = rs("NOM")
Sheets(3).Cells(i, 5) = rs("GRP_CLI")
Sheets(3).Cells(i, 6) = rs("TCA")
Sheets(3).Cells(i, 7) = rs("TMB")
Sheets(3).Cells(i, 8) = rs("TNBR_POS")
Sheets(3).Cells(i, 9) = rs("TPB")
Sheets(3).Cells(i, 8) = rs("TPT")
rs.MoveNext
i = i + 1
Loop
rs.Close
cnn.Close
Set rs = Nothing
'************************************************
' end of second part
'************************************************

WScript.Echo "We save now"
' save as now
FileName = GetPath + "KPI04.xls"
objXL.ActiveWorkbook.SaveAs FileName
'WScript.Echo "We are saving now"


' I like to prevent the warning message about the unsaved data
' during closing Excel
objXL.DisplayAlerts = False ' prevent all message boxes

objXl.Quit() ' Quit Excel
Set objXL = Nothing

WScript.Quit()

'##########################
Function GetPath
' Retrieve the script path
DIM path
path = WScript.ScriptFullName ' Script name
GetPath = Left(path, InstrRev(path, "\"))
End Function
'*************************************************
'*** End -> WSH-VBScript ***
'*************************************************