Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionDim m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CheminFichier & " " & _ ";Extended Properties=""Excel 8.0;HDR=YES""" Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1) Dim s_entete As String = "" Dim s_ligne As String = "" s_entete = "CREATE TABLE Feuil1 (" For i As Integer = 0 To dttRequete.Columns.Count - 1 If LCase(Left(dttRequete.Columns(i).DataType.Name, 3)) "int" Or LCase(Left(dttRequete.Columns(i).DataType.Name, 4)) "deci" Then s_entete = s_entete & "[" & Replace(dttRequete.Columns(i).ColumnName, " ", "_") & "] float" Else If Left(dttRequete.Columns(i).ColumnName, 4) = "xml_" Then Dim t_colonne() As String = Split(dttRequete.Columns(i).ColumnName, "_") s_entete = s_entete & "[" & Replace(Replace(Replace(t_colonne(UBound(t_colonne)), " ", "_"), "**", ""), "/", "_") & "] text" Else s_entete = s_entete & "[" & Replace(dttRequete.Columns(i).ColumnName, " ", "_") & "] text" 's_entete = s_entete & "[" & Replace(dttRequete.Columns(i).ColumnName, " ", "_") & "] char(255)" End If End If If i < dttRequete.Columns.Count - 1 Then s_entete = s_entete & "," Next s_entete = s_entete & " )" Dim cmdCreate As New System.Data.OleDb.OleDbCommand(s_entete, conn1) cmdCreate.ExecuteNonQuery() cmdCreate.Dispose() s_ligne = "INSERT INTO Feuil1 VALUES (" For i As Integer = 0 To dttRequete.Columns.Count - 1 If Not row(i) Is System.DBNull.Value Then If LCase(Left(dttRequete.Columns(i).DataType.Name, 3)) "int" Or LCase(Left(dttRequete.Columns(i).DataType.Name, 4)) "deci" Then s_ligne = s_ligne & Replace(row(i), ",", ".") Else If Left(dttRequete.Columns(i).ColumnName, 4) = "xml_" Then '' balises xml Dim tmp As String = Replace(Replace(row(i), Chr(10), ""), Chr(13), " ") Dim xml As New System.Xml.XmlDocument Try tmp = Left(tmp, tmp.LastIndexOf("</root>") + Len("</root>")) ' on enleve les caracteres apres la racine "</root>", par exemple les " " xml.LoadXml(tmp) Dim t_colonne = Split(dttRequete.Columns(i).ColumnName, "_") If Left(t_colonne(UBound(t_colonne)), 2) = "**" Then tmp = xml.SelectSingleNode(t_colonne(1) & "[@" & t_colonne(2) & "='" & Right(t_colonne(UBound(t_colonne)), Len(t_colonne(UBound(t_colonne))) - 2) & "']").Attributes("Valeur").InnerText Else tmp = xml.SelectSingleNode(t_colonne(1)).InnerText End If 's_ligne = s_ligne & "'" & Replace(Replace(Replace(tmp, vbLf, " "), vbTab, " "), "'", "''") & "'" s_ligne = s_ligne & "'" & Replace(Replace(Replace(tmp, vbLf, " "), vbTab, " "), "'", "''") & "'" Catch ex As Exception s_ligne = s_ligne & "' '" End Try Else s_ligne = s_ligne & "'" & Replace(row(i), "'", "''") & " '" End If End If Else If LCase(Left(dttRequete.Columns(i).DataType.Name, 3)) "int" Or LCase(Left(dttRequete.Columns(i).DataType.Name, 4)) "deci" Then s_ligne = s_ligne & "0" Else s_ligne = s_ligne & "''" End If End If If i < dttRequete.Columns.Count - 1 Then s_ligne = s_ligne & "," End If Next s_ligne = s_ligne & ")" Dim lignes As New System.Data.OleDb.OleDbCommand(s_ligne, conn1) lignes.ExecuteNonQuery() lignes.Dispose() Next
Sub Delete_PrefixCharacters() Dim rngCell ' Loop through all used cells in the active worksheet. For Each rngCell In Spreadsheet1.ActiveSheet.UsedRange ' If a prefix character exists, delete it. If rngCell.PrefixCharacter <> "" Then rngCell.Value = rngCell.Value End If Next End Sub
Mais je ne sais pas comment je pourrai l'utiliser avec mon code que j'ai posté avant.