Collect Data from CSV files and paste it in one xls file

[EDN Admin]

Hello,
I get a problem with my program. My program should do :
-Explore folders from a main folder (LOGS)
-See if there are csv files
-Copy the data from CSV (all informations are on the first column)
-Paste it in one xls file (with a sheet for every csv)


My problem is that I can not create the XLS file :/
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/>
<br/>


View the full article