Database Search
If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.
TechHome
OLE with Excel
 |
- Access OLE Query export to Excel
- Access table query to Excel
- Accessing an Existing Copy of Excel
- Add Comment Excel
- Add New Worksheet
- Add Worksheet
- Animate Excel office assistant
- Automating Excel Tutorial
- Basics with Excel OLE
- Change Currency Formats in Excel
- Check for Empty Cell in Excel
- Check if Column is Hidden
- Close Excel Object
- COM Test Use with Excel WorksheetFunction Object
- Consolidate Excel Workbooks
- convert the HISTOGRAM sheet to BMP or GIF
- CopyFromRecordset Loss of Formatting
- Create Excel Addin
- Create XL Graph
- Cutting and Pasting a Cell in Excel
- Delete Column
- Determine Excel File Format Type
- Determine First and Last Row and Column Used
- Determine Version That An Excel File Was Saved In
- Determine Workbook or WorkSheet Protection
- Determine Worksheet Names in Excel Workbook
- Disable Security Warning
- Display Color Chart
- Display Column and Row Data
- Display Users on Excel Workbook
- Easy CrossTabbed Spreadsheet Creation
- Enumerate Fonts in Excel
- Enumerating Excel Commandbar Controls
- Excel - constants
- Excel - Signature Worksheet
- Excel - Simple Conditional Column Hiding
- Excel 2000 - Inserting Hyperlinks with OLE
- Excel 2003 Tips
- Excel 2007 and Windows Server 2008 x64 Problem
- Excel and SaveAs Method
- Excel AutoFilter Method
- Excel Custom Variables
- Excel Dynamic Range Simple Example
- Excel File Conversion Using SaveAs
- Excel Filtering Function
- Excel Get Formatted Tables From Web Pages
- Excel Move Sheet
- Excel ObjectGet and UAC
- Excel OLE - List Named Ranges
- Excel Paste Special
- Excel Power Point Example
- Excel Print Landscape Scaled problem
- Excel Proper Case
- Excel Replace String
- Excel Time Format
- Excel to PDF
- Excel unlock some cells in a protected worksheet
- Excel Worksheet Change
- Export Excel Data to Tab Delimited Text File
- Exporting Excel Pictures
- Extract Data from specific Range in Closed Excel WorkBook
- Fastest Way to Copy Data To Excel
- Find Method in Excel
- Finding Data in Excel Cells
- Format Excel Column
- Get All Rows of Data in a Column
- Get Data Row by Row in Excel
- Get SaveAs to Close without Alert Box
- Getting Rowcount from Excel Database
- Grab Data from All WOrksheets in a Workbook
- Handle Errors in Cells
- Hide - Unhide Columns in Excel
- How to Execute Excel Function via OLE - COM
- Import a text file into an Excel Column
- Import CSV file into Excel
- Import Tab Delimited Text File
- Insert a Tracking Module in Excel Workbooks
- Inserting Pagebreaks into Excel Sheet
- Load WIL Array into Excel
- Making a Chart in Excel
- Number of pages in Excel worksheets
- ObjectGet and Excel
- OLE and Excel - Adding Cells to Workbook
- OLE and Excel - Spreadsheet Calculation
- OLE and Excel - The Basics
- OLE and Excel Animation Example
- OLE and Excel BuiltinDocumentProperties
- OLE Excel - Cutting and pasting cells problem
- OLE Excel and Outlook Examples
- OLE Excel Example that reads text from cells
- OLE with Excel and Named Parameters
- OLE with Parameters and Excel
- Open Existing Excel File And Insert Row
- Open Password Protected Excel file via OLE
- Playing Around with ADO and Updating dBase Files
- Playing Around with ADO and Updating Excel Columns
- Print All Sheets in a WorkBook
- Print Excel Worksheets
- Printing Workbooks from Excel
- Push Code into Excel using OLE
- Put Excel Column into an Array
- Reference a Specific Excel Workbook Instance
- Rename Existing Excel Worksheet
- Resize child windows in Excel
- Row Count
- Run an Excel Macro
- Run Excel Macro
- Save XLS file as a CSV file
- Search Excel for Cells and Items
- Search Excel Spreadsheet
- Set AutoSize for Cell Comment
- Set Cell Borders and Font
- Set Excel Default Path on Start-up
- Set Number of Sheets in Workbook
- Setting Proper Case in Range of Cells
- Showing the Value of Excel Dynamic Ranges
- Simple Excel Read
- Size of an EXCEL spreadsheet
- Sort Columns in XLS
- Sorting a Variable Range in Excel
- Sorting Multiple Columns
- Sorting Worksheets inside a Workbook
- SumProduct Function in Excel
- Testing the WorkSheetFunction Object
- TextToColumns FieldInfo Embedded Array Parameter Issue
- UDF for Excel Constant References
- UDF to Determine Worksheet Names in Excel Workbook
- Update Excel Cells
- Updating Worksheet Links
- Wait for Excel Add-in to Load
- Winbatch ADO Script - For EXCEL
- Working with Excels Built-in Dialogs
- Wrap Text in Excel Cell
- Write Data to an XLS file
Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Fastest Way to Copy Data To Excel
Question:
I've got a routine which fills an Excel-sheet with data from a recordset. It does that cell by cell but it is very slow. The routine looks like:
While ! RS.eof
oCell1 = oWS1.Range("A%Counter%")
oCell1.Select
oCell1.Value = rs.fields("portnr")
oCell1 = oWS1.Range("B%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Relatie")
oCell1 = oWS1.Range("C%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Benchmark")
oCell1 = oWS1.Range("D%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Mabelcode")
oCell1 = oWS1.Range("E%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Fonds")
oCell1 = oWS1.Range("F%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Aantal")
oCell1 = oWS1.Range("G%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Val_OV")
oCell1 = oWS1.Range("H%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Koers_OV")
oCell1 = oWS1.Range("I%Counter%")
oCell1.Select
oCell1.Value = rs.fields("MW_OV")
oCell1 = oWS1.Range("J%Counter%")
oCell1.Select
oCell1.Value = rs.fields("GKP_OV")
oCell1 = oWS1.Range("K%Counter%")
oCell1.Select
oCell1.Value = rs.fields("KP_OV")
oCell1 = oWS1.Range("L%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Perc_OV")
oCell1 = oWS1.Range("M%Counter%")
oCell1.Select
oCell1.Value = rs.fields("MW_CV")
oCell1 = oWS1.Range("N%Counter%")
oCell1.Select
oCell1.Value = rs.fields("KP_CV")
oCell1 = oWS1.Range("O%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Res_CV")
oCell1 = oWS1.Range("P%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Perc_CV")
oCell1 = oWS1.Range("Q%Counter%")
oCell1.Select
oCell1.Value = rs.fields("PercMW_CV")
oCell1 = oWS1.Range("R%Counter%")
oCell1.Select
oCell1.Value = rs.fields("OR_CV")
oCell1 = oWS1.Range("S%Counter%")
oCell1.Select
oCell1.Value = rs.fields("Dep")
Counter = Counter + 1
rs.MoveNext
EndWhile
Is there a way to speed-up things, maybe inserting a whole row instead of cell for cell?
Answer:
You should be able to comment all the .select statements without hurting your script. see if that's any faster.Depends on where the data is coming from. If you have the data in MS Access (or another database) you can use excel's .CopyFromRecordset to put in the data in one shot. .CopyFromRecordset works both for MS Access's basic and with ADO. Otherwise, you might try putting the data into a CSV file and importing it into excel via OLE.
Article ID: W16618
File Created: 2005:02:18:12:21:38
Last Updated: 2005:02:18:12:21:38