vbscript,Code片段,代码实例,代码示例,代码片段库

vbscript abrir excel desde vb y bucle para recorrerlo y rango de filas del excel

openExcel

Dim Aplicacion As Excel.Application
Dim Libro As Excel.Workbook
Dim Hoja As Excel.Worksheet

Aplicacion = New Excel.Application
Libro = Aplicacion.Workbooks.Open("C:\Test.xlsx")
Hoja = Libro.Worksheets(1)
 
 Dim FinalRow As Integer
 FinalRow = Hoja.Range("B30").End(Excel.XlDirection.xlUp).Row
  For i = 2 To FinalRow
      
  Next i

vbscript AutoWidth

Anchoautomáticaparalas columnas en ese rango。

autoWidth

xlWorkSheet.Range("A1:G1").EntireColumn.AutoFit()

vbscript Negrita en excel

En un excel dede especifica un rango de celdasqueestaránnenegrita

negritaExcel

xlWorkSheet.Range("A1:G1").Font.Bold = True

vbscript Crear excel y rellenarlo

Códigoprancrear un excel en vb，con la posibilidad que se guarde deformaautomáticaomanual

create excel

Private Sub Button1_Click(ByVal sender As System.Object,_
	 ByVal e As System.EventArgs) Handles Button1.Click
  Dim xlApp As Excel.Application
  'xlApp.Visible = True 'con esta linea se ve el excel
  'xlApp.Workbooks.Open(Path) 'para abrir un excel
  Dim xlWorkBook As Excel.Workbook
  Dim xlWorkSheet As Excel.Worksheet
  Dim misValue As Object = System.Reflection.Missing.Value
  
  xlApp = New Excel.Application
  xlWorkBook = xlApp.Workbooks.Add(misValue)
  xlWorkSheet = xlWorkBook.Sheets("sheet1")
  xlWorkSheet.Cells(1, 1) = "http://vb.net-informations.com"
  xlWorkSheet.SaveAs("C:\vbexcel.xlsx") 'se puede comentar esta linea si se quiere ver el excel y guardar de forma manual
  xlWorkSheet.SaveAs("C:\vbexcel.xlsx", Excel.XlFileFormat.xlExcel8) ' con formato
  xlWorkBook.Close()
  xlApp.Quit()

  releaseObject(xlApp)
  releaseObject(xlWorkBook)
  releaseObject(xlWorkSheet)

  MsgBox("Excel file created , you can find the file c:\")
End Sub

Private Sub releaseObject(ByVal obj As Object)
  Try
      System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
      obj = Nothing
  Catch ex As Exception
      obj = Nothing
  Finally
      GC.Collect()
  End Try
End Sub

vbscript Union de Excels

Uniónde2 Excels en un tercero。 Copia las celdas recorderriendo las quetieneinformaciónycon un doble for las copia donde Corresponde。

joinExcels

Dim nombreExcelTPV As String = "export tpv.xls"
        Dim nombreExcelCajero As String = "export cajero.xls"
        Dim carpetaUnir As String = "Excels para unir"
        Dim CarpetaOdin As String = "Excel exportado desde odin"
        Dim carpetaExcel As String = ConfigurationManager.AppSettings("carpetaExcel").ToString()
        Dim diUnir As New DirectoryInfo(carpetaExcel + "\" + carpetaUnir + "\")
        Dim fiArr As FileInfo() = diUnir.GetFiles()
        If fiArr.Length > 2 Then
            MsgBox("Hay más de 2 ficheros en '" + carpetaUnir + "'.")
            Exit Sub
        ElseIf fiArr.Length = 1 Then
            MsgBox("Hay menos de 2 ficheros en '" + carpetaUnir + "'.")
            Exit Sub
        ElseIf fiArr.Length = 0 Then
            MsgBox("No hay ficheros en '" + carpetaUnir + "'.")
            Exit Sub
        End If
        'creamos excel export
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets(1)
        
        'leemos excel TPV
        Dim AplicacionTPV As Excel.Application
        Dim LibroTPV As Excel.Workbook
        Dim HojaTPV As Excel.Worksheet

        AplicacionTPV = New Excel.Application
        LibroTPV = AplicacionTPV.Workbooks.Open(carpetaExcel + "\" + carpetaUnir + "\" + nombreExcelTPV)
        HojaTPV = LibroTPV.Worksheets(1)

        Dim FinalRowTPV As Integer
        FinalRowTPV = HojaTPV.Range("A1").End(Excel.XlDirection.xlDown).Row
        'copia excel TPV en excel export
        For i = 1 To FinalRowTPV
            For j = 1 To 7
                xlWorkSheet.Cells(i, j) = HojaTPV.Cells(i, j)
            Next
        Next

        'leemos excel Cajeros
        Dim AplicacionCajeros As Excel.Application
        Dim LibroCajeros As Excel.Workbook
        Dim HojaCajeros As Excel.Worksheet

        AplicacionCajeros = New Excel.Application
        LibroCajeros = AplicacionCajeros.Workbooks.Open(carpetaExcel + "\" + carpetaUnir + "\" + nombreExcelCajero)
        HojaCajeros = LibroCajeros.Worksheets(1)

        Dim FinalRowCajero As Integer
        FinalRowCajero = HojaCajeros.Range("A1").End(Excel.XlDirection.xlDown).Row

        Dim FinalRowExport As Integer
        FinalRowExport = xlWorkSheet.Range("A1").End(Excel.XlDirection.xlDown).Row
        'copia excel cajero en excel export
        For i = 2 To FinalRowCajero
            For j = 1 To 7
                xlWorkSheet.Cells(i + FinalRowExport - 1, j) = HojaCajeros.Cells(i, j)
            Next
        Next

        xlWorkSheet.SaveAs(carpetaExcel + "\" + CarpetaOdin + "\" + "export.xls", Excel.XlFileFormat.xlExcel8) 'excel con formato 97-2003

        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        LibroTPV.Close()
        AplicacionTPV.Quit()

        releaseObject(AplicacionTPV)
        releaseObject(LibroTPV)
        releaseObject(HojaTPV)

        LibroCajeros.Close()
        AplicacionCajeros.Quit()

        releaseObject(AplicacionCajeros)
        releaseObject(LibroCajeros)
        releaseObject(HojaCajeros)

vbscript AlgoritmoRecorridoVBA

Algoritmo de recorrido de las filas de un Excel。 Ordena el Excel y latre las filas con un valor inicial y un valor actual que es el que va leyendo de cada fila。 Cuando hay un corte de valores（que cambia y no son el mismo），latre esa franja para darle un valor concreto a esas lineas que tienen un mismo valor。

AlgoritmoRecorridoOrdenado

Dim contratoInicial As String
    Dim contratoActual As String
    Dim auxClave As String
    Dim auxSegmentos As String
    Dim NumRows As Long
    Dim contador As Long
    
    NumRows = Range("B2", Range("B2").End(xlDown)).Rows.Count
    
    Range("A1", Range("L1").End(xlDown)).Select
    Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Key2:=Range("L1"), Order2:=xlAscending, Key3:=Range("E1"), Order3:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
    For x = 2 To NumRows + 1
        contratoInicial = Cells(x, "L")
        contador = 0
        auxClave = ""
        auxSegmentos = ""
        For j = x To NumRows + 1
             contratoActual = Cells(j, "L")
             If contratoInicial = contratoActual Then
                 contador = contador + 1
                 auxClave = auxClave & Cells(j, "E")
                 auxSegmentos = auxSegmentos & Cells(j, "D")
             ElseIf contratoInicial <> contratoActual Then
                 For k = x To x + contador - 1
                    Cells(k, "M") = "'" & auxClave
                    Cells(k, "N") = "'" & auxSegmentos
                Next
                x = x + contador - 1
                Exit For
             End If
             If j = NumRows + 1 Then
                For k = x To x + contador - 1
                    Cells(k, "M") = "'" & auxClave
                    Cells(k, "N") = "'" & auxSegmentos
                Next
                x = x + contador - 1
                Exit For
             End If
         Next
               
    Next

vbscript SaveExcelXSL

excel xsl

xlWorkBook.SaveAs(carpetaExcel + "\Excel creado importado\FicheroParaImportar.xls", Excel.XlFileFormat.xlExcel8)

vbscript Recorrido DataTables

Recorre dos DataTables comparando contenido y guardando ambos en un DataGridView en la misma fila。 Esintensacióndelsnippet“Excel to DataTable”

recorridoDataTables

If dtOdin.Rows.Count <> dtCreado.Rows.Count Then
    MsgBox("¡Los excel tienen distinto número de filas!")
End If

Dim encontrado As Boolean
For Each rowOdin As DataRow In dtOdin.Rows
    encontrado = False
    For Each rowCreado As DataRow In dtCreado.Rows
        If IIf(rowCreado(0) Is DBNull.Value, "", Convert.ToString(rowCreado(0))) = IIf(rowOdin(0) Is DBNull.Value, "", Convert.ToString(rowOdin(0))) AndAlso IIf(rowCreado(1) Is DBNull.Value, "", Convert.ToString(rowCreado(1))) = IIf(rowOdin(1) Is DBNull.Value, "", Convert.ToString(rowOdin(1))) AndAlso IIf(rowCreado(2) Is DBNull.Value, "", Convert.ToString(rowCreado(2))) = IIf(rowOdin(2) Is DBNull.Value, "", Convert.ToString(rowOdin(2))) AndAlso IIf(rowCreado(3) Is DBNull.Value, "", Convert.ToString(rowCreado(3))) = IIf(rowOdin(3) Is DBNull.Value, "", Convert.ToString(rowOdin(3))) AndAlso IIf(rowCreado(5) Is DBNull.Value, "", Convert.ToString(rowCreado(5))) = IIf(rowOdin(5) Is DBNull.Value, "", Convert.ToString(rowOdin(5))) Then
            'poner en data grid los datos
            Dim row As String() = New String() {IIf(rowOdin(0) Is DBNull.Value, "", rowOdin(0)), IIf(rowOdin(1) Is DBNull.Value, "", rowOdin(1)), IIf(rowOdin(2) Is DBNull.Value, "", rowOdin(2)), IIf(rowOdin(3) Is DBNull.Value, "", rowOdin(3)), IIf(rowOdin(4) Is DBNull.Value, "", rowOdin(4)), IIf(rowOdin(5) Is DBNull.Value, "", rowOdin(5)), IIf(rowOdin(6) Is DBNull.Value, "", rowOdin(6)), IIf(rowCreado(0) Is DBNull.Value, "", rowCreado(0)), IIf(rowCreado(1) Is DBNull.Value, "", rowCreado(1)), IIf(rowCreado(2) Is DBNull.Value, "", rowCreado(2)), IIf(rowCreado(3) Is DBNull.Value, "", rowCreado(3)), IIf(rowCreado(4) Is DBNull.Value, "", rowCreado(4)), IIf(rowCreado(5) Is DBNull.Value, "", rowCreado(5)), IIf(rowCreado(6) Is DBNull.Value, "", rowCreado(6))}
            DataGridView1.Rows.Add(row)
            encontrado = True
            Exit For
        End If
    Next
    If encontrado = False Then
        Dim row As String() = New String() {IIf(rowOdin(0) Is DBNull.Value, "", rowOdin(0)), IIf(rowOdin(1) Is DBNull.Value, "", rowOdin(1)), IIf(rowOdin(2) Is DBNull.Value, "", rowOdin(2)), IIf(rowOdin(3) Is DBNull.Value, "", rowOdin(3)), IIf(rowOdin(4) Is DBNull.Value, "", rowOdin(4)), IIf(rowOdin(5) Is DBNull.Value, "", rowOdin(5)), IIf(rowOdin(6) Is DBNull.Value, "", rowOdin(6)), "", "", "", "", "", "", ""}
        DataGridView1.Rows.Add(row)
    End If
Next

vbscript Excel到DataTable

Excel一个dataTable。 Tener en cuenta el nombre de la hoja excel（Sheet0）。 Si da error de que no encuentra Microsoft.Jet ... poner la cpu del compilar a x86

ExcelToDataTable

'carpetas donde estan los excels y nombre de los excels
            Dim carpetaCreado As String = "Excel creado importado"
            Dim CarpetaOdin As String = "Excel exportado desde odin"
            Dim carpetaExcel As String = ConfigurationManager.AppSettings("carpetaExcel").ToString()
            Dim diCreado As New DirectoryInfo(carpetaExcel + "\" + carpetaCreado + "\")
            Dim diOdin As New DirectoryInfo(carpetaExcel + "\" + CarpetaOdin + "\")

            Dim fiArr As FileInfo() = diCreado.GetFiles()
            Dim fiArrOdin As FileInfo() = diOdin.GetFiles()
            Dim friCreado As FileInfo
            Dim friOdin As FileInfo
            friCreado = fiArr(0)
            friOdin = fiArrOdin(0)
'excel odin
            Dim connectionStringOdin As String
            If friOdin.Extension = ".xls" Then
                connectionStringOdin = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Extended Properties='Excel 8.0;HDR=Yes';" & _
                 "Data Source=" & diOdin.ToString() + friOdin.Name.ToString()
            Else
                connectionStringOdin = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "Extended Properties='Excel 12.0;HDR=Yes';" & _
                 "Data Source=" & diOdin.ToString() + friOdin.Name.ToString()
            End If

            Dim cnn As New OleDbConnection(connectionStringOdin)
            sql = "SELECT * FROM [Sheet0$]"

            Dim da As New OleDbDataAdapter(sql, cnn)

            Dim dtOdin As New DataTable()

            da.Fill(dtOdin)

            DataGridView1.DataSource = dtOdin

vbscript abrir excel desde vb y bucle para recorrerlo y rango de filas del excel

openExcel

Dim Aplicacion As Excel.Application
Dim Libro As Excel.Workbook
Dim Hoja As Excel.Worksheet

Aplicacion = New Excel.Application
Libro = Aplicacion.Workbooks.Open("C:\Test.xlsx")
Hoja = Libro.Worksheets(1)
 
 Dim FinalRow As Integer
 FinalRow = Hoja.Range("B30").End(Excel.XlDirection.xlUp).Row
  For i = 2 To FinalRow
      
  Next i