title image


Smiley Re: Visual Basic und Excel????
hi;ich hatte mir da mal ein musterprojekt gemacht, vielleicht hilft es dir ja beim einstieg - du brauchst nur eine form mit einem CommandButton:Option Explicit' Definitions for Excel Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet 'Dim oRng As Excel.Range' Definitions for Database Dim conn As Connection Dim dbConn As String Dim SQLString As String Dim rs As Recordset Dim Fld As Field' Definitions for Array Dim SomeArray() As Variant' Other Definitions Dim Row As Long Dim Col As Long Dim Pos As LongPrivate Sub Command1_Click()' --------------------------------------------------' Start Excel and get Application object Set oXL = CreateObject("Excel.Application")' --------------------------------------------------' Open an existing Workbook ... 'Set oWB = oXL.Workbooks.Open("c:\temp\test.xls")' ... or add a new Workbook Set oWB = oXL.Workbooks.Add' --------------------------------------------------' Add a new Worksheet ... 'Set oSheet = oWB.Sheets.Add' ... or delete an existing Worksheet 'oWB.Sheets(2).Delete' ... also works with Sheetname 'oWB.Sheets("Tabelle2").Delete' --------------------------------------------------' Set Worksheet to active Sheet ... 'Set oSheet = oWB.ActiveSheet' ... or to any other Sheet ... 'Set oSheet = oWB.Sheets(1)' ... also works with Sheetname Set oSheet = oWB.Sheets("Tabelle1")' --------------------------------------------------' Rename the active Sheet ... 'oSheet.Name = "Test"' ... or any other Sheet ... 'oWB.Sheets(1).Name = "Test"' ... also works with Sheetname oWB.Sheets("Tabelle1").Name = "Test"' --------------------------------------------------' Fill Cells with Values 'oSheet.Range("A1") = "1" 'oSheet.Range("B1") = "1"' Fill Cell with Formula 'oSheet.Range("C1").Formula = "=A1 + B1"' --------------------------------------------------' Add Recordset to Excel (using Array) ' Connect to Database and fill Recordset Set conn = New Connection dbConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\temp\test.mdb;" conn.Open dbConn Set rs = New Recordset SQLString = "SELECT test.[feld1], " & _ "test.[feld2], " & _ "test.[feld3], " & _ "FROM test " & _ "ORDER BY test.[feld2], test.[feld3] " rs.CursorLocation = adUseClient rs.Open SQLString, conn, adOpenStatic, adLockOptimistic, adCmdText ' Resize Array ReDim SomeArray(rs.RecordCount + 1, rs.Fields.Count) ' Copy Fieldnames as Column-Header to Array Col = 0 For Each Fld In rs.Fields SomeArray(0, Col) = Fld.Name Col = Col + 1 Next ' Copy Recordset to Array rs.MoveFirst For Row = 1 To rs.RecordCount - 1 For Col = 0 To rs.Fields.Count - 1 SomeArray(Row, Col) = rs.Fields(Col).Value ' Excel doesn't allow a cell to be NULL If IsNull(SomeArray(Row, Col)) Then SomeArray(Row, Col) = "" End If ' Replace CR/LF in Array For Pos = 1 To Len(SomeArray(Row, Col)) If Trim(Mid(SomeArray(Row, Col), Pos, 1)) = Chr(13) Then Mid(SomeArray(Row, Col), Pos, 2) = " " End If Next Next rs.MoveNext Next ' Fill Excel-Table with Array oSheet.Range(oSheet.Cells(1, 1), _ oSheet.Cells(1 + rs.RecordCount + 1, _ 1 + rs.Fields.Count)).Value = SomeArray ' Resize the Columns in Excel-Table oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1 + rs.RecordCount + 1, 1 + rs.Fields.Count)).EntireColumn.AutoFit ' Release Recordset and Database rs.Close Set rs = Nothing conn.Close Set conn = Nothing' --------------------------------------------------' Copy Recordset to Excel (only works with Excel 2000!!!) 'oSheet.Range("A1").CopyFromRecordset rs' --------------------------------------------------' Save Workbook ... 'oWB.Save' ... or save Workbook as 'oWB.SaveAs "c:\temp\test2.xls"' --------------------------------------------------' Show Excel ... 'oXL.Visible = True' ... or not 'oXL.Visible = False' --------------------------------------------------' Give the user control to Excel ... 'oXL.Interactive = True' ... or not 'oXL.Interactive = False' --------------------------------------------------' Exit Excel oXL.Quit Set oXL = Nothing' --------------------------------------------------End Subgruß jimmy

geschrieben von

Login

E-Mail:
  

Passwort:
  

Beitrag anfügen

Symbol:
 
 
 
 
 
 
 
 
 
 
 
 
 

Überschrift: