Collect data from CSV files in several folders and paste it in one XLS

[EDN Admin]

Hello !
I get problem with my program. My program should do:
-Explore a main folder (LOGS) that contains folders with sometimes CSV files in it
-Collect data from CSV files, and paste it in one XLS (one sheet for one CSV, all informations in CSV are on the first column, the name of the sheet should be the name of the CSV file)
My program finds correctly all the files in the folder (I use recursivity, with a threeview). If there is CSV, I put the name of the path in a listbox.
But I am not able to create a XLS file, I have a COM exception error :/
Thank you by advance,
<pre class="prettyprint lang-vb Imports System.IO
Imports System.Net.Mime.MediaTypeNames
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Public Class Form1


Dim name_path_csv As String
Const RepertoireALister As String = "Cocuments and SettingsuyttenhoMy DocumentsLOGS"


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Définit le premier noeud

Me.TV_Arborescence.TopNode = Me.TV_Arborescence.Nodes.Add(RepertoireALister, RepertoireALister)

Arborescence du premier noeud

For Each Repertoire As String In Directory.GetDirectories(RepertoireALister)

Me.TV_Arborescence.TopNode.Nodes.Add(Repertoire, Path.GetFileName(Repertoire))

Récursif

ListeArborescenceDossier(Repertoire, Me.TV_Arborescence.TopNode)

Next

Fichiers du premier noeud

For Each Fichier As String In Directory.GetFiles(RepertoireALister)

Me.TV_Arborescence.TopNode.Nodes.Add(Path.GetFileName(Fichier))

Next



End Sub

Sub ListeArborescenceDossier(ByVal RepertoireActuel As String, ByVal NodeActuel As TreeNode)

Recupère le node dans lequel on est

Dim Node As TreeNode = NodeActuel.Nodes(RepertoireActuel)

Répertoires de ce noeud

For Each Repertoire As String In Directory.GetDirectories(RepertoireActuel)

Node.Nodes.Add(Repertoire, Path.GetFileName(Repertoire))

Récursif

ListeArborescenceDossier(Repertoire, Node)

Next

Fichiers de ce noeud

For Each Fichier As String In Directory.GetFiles(RepertoireActuel)

Node.Nodes.Add(Path.GetFileName(Fichier))

If InStr(Path.GetFileName(Fichier), ".CSV") Then

Me.LB_ListeCSV.Items.Add(Path.GetFileName(Fichier))


End If

Next

End Sub

Private Sub LB_ListeCSV_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LB_ListeCSV.SelectedIndexChanged

End Sub

Private Sub B_Copiercollercsv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Copiercollercsv.Click


Dim appExcel, appExcel2 As New Microsoft.Office.Interop.Excel.Application
Dim wbExcel, wbExcel2 As Microsoft.Office.Interop.Excel.Workbook
Dim wsExcel, wsExcel2 As Microsoft.Office.Interop.Excel.Worksheet





Dim strFileName As String = "fichierlog"
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try

If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If

wbExcel = New Workbook

wbExcel.SaveAs(strFileName)
appExcel.Workbooks.Open(strFileName)
appExcel.Visible = True

appExcel = CType(CreateObject("Excel.Application"), Excel.Application)
wbExcel = CType(appExcel.Workbooks.Add, Excel.Workbook)
wsExcel = CType(wbExcel.Worksheets(1), Excel.Worksheet)


For I As Integer = 0 To Me.LB_ListeCSV.Items.Count

Dim J As Integer = 0

appExcel2 = New Excel.Application création nouveau fichier xls

name_path_csv = Me.LB_ListeCSV.Items.ToString on récupère le chemin du fichier contenu dans ListeCSV

wbExcel2 = appExcel2.Workbooks.Open(name_path_csv) on ouvre le csv avec le nom du chemin

wsExcel = New Worksheet
wbExcel2 = New Workbook
wsExcel2 = New Worksheet
wsExcel2 = wbExcel.Worksheets(1) on sélectionne la première page (je pense que cest pas nécessaire)

wbExcel.Sheets.Add(After:=I) on ajoute une feuille dans le XLS APRES les autres

wbExcel.ActiveSheet.Name = wbExcel2.Sheets(name_path_csv) on donne au nom de la feuille excel, le nom du chemin

J = wbExcel2.rows.Count on compte le nombre de lignes dans le CSV

wbExcel = wbExcel2.Sheets(name_path_csv).Range("A1" & ":A" & J).value on copie colle ce qui se trouve sur le CSV


Sauvegarder le fichier XLS



Next




End Sub
End Class

[/code]
<br/>


View the full article