|
Hi everyone
I have the following to extract data from a SQL database and write it to an Excel table. I would likte to change the loop so that the data is copied to Excel per column rather than single field (to speed up th import):
'Create the connection string
sConnString = "Provider=SQLOLEDB.1;Data Source=SRV002982;" & _
"Initial Catalog=HFMDSmartFDM_QMR211;" & _
"Password=password;Persist Security Info=True;User ID=User"
'Create the Connection and Recordset objects
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'Open the connection and execute
conn.Open sConnString
Set rs = conn.Execute(CommandString)
'Check data
If Not rs.EOF Then
nRow = nRow + 1
'Transfer result to WS
Do While Not rs.EOF
For Each elem In rs.Fields
nCol = nCol + 1
ws.Cells(nRow, nCol) = elem
Next
rs.movenext
Loop
End If
There are reasons I can't just splash the entire table. I need to import column by column. Any help is greatly appreciated
|
|
|
|
|
Member 10666698 wrote: Do While Not rs.EOF
That's how a reader works; a record at a time. One line per iteration, not a column.
What you want can be easily achieved by changing the query you execute, returning only one column (as opposed to all of them, as it prolly does now). Also probably means re-executing the query for each column, unless you save it in a temporary location, in which case you'd be worrying about concurrency.
Alternatively, you could find a lighter export-format. (Like write it out as a CSV)
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Writing by column will have the effect of SLOWING your process as Eddy has said.
I would define whether it is the query or the writing to excel that is the costly process.
Optimising the query can be done in the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi guys,
thanks for your replies. What you are saying makes sense. I stronlgy supsect that writing to Excel is what is slowing down the process. Can you recommend a more efficient way of doing this, i.e. writing to an array first? I will run some tests in the meantime
|
|
|
|
|
Hi
runnning performance tests confirms what you suspected: writing to Excel is slow. In the loop I have soemthing on the lines of
ws(row, col) = elem
I have to write to an existing Excel template so CSV is not an option. Perhaps it would be faster to store to an array first and then write everything to Excel. I will test and get back after
|
|
|
|
|
Writing to excel is always a dog if you do it by cell, I have not tried writing an array but it would probably work.
Here is an idea, try copy paste - DataGridView Copy and Paste[^] you could construct the data structure to mimic the copy array, select a cell and paste the content. I have no idea how it will perform 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks, I used an array and it works for my purposes (i.e. write to array, copy array to Excel). Only other solution I can think of is separate queries per column
|
|
|
|
|
Which version of Excel are you targetting? If it supports the .xlsx format, then you might want to open that file and see how Excel stores it's data. If you don't like the idea of mucking around in the file, then try finding a tool that let's you interact with the data without requiring Excel.
Try a few ideas in a console-app and time them 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
My Excel version supports xlsx. I will try a few options and report back once I have some results. Storing data in an array presents a significant time improvement though
|
|
|
|
|
Hi, Iam using VS2010, while generating RDLC report, Iam using IIf statement for calculations.
But I found a typical problem which is very different.
Because from the Code.Functions for calculation, simply it's working fine, when I give the
below command directly
=Code.AddMyNet(Sum(ReportItems!itm_value.Value))
But I wish to give like the below command, It's giving error "local report processing"
=IIf((ReportItems!Textbox15.Value ="CARRY FORWARDED "), Code.AddMyNet(Sum(ReportItems!itm_value.Value)), Nothing)
Actually Function AddMyNet() used to calculate the pagewise running value total.
And hence any guidances will be helpful
Thanks & Regards
Paramu
|
|
|
|
|
Hi All,
I am looking for expert advice on printing the pdf files at client machine.
I have created a sample project named ClientPrint as
Imports System.Drawing
Imports System.Drawing.Printing
Imports System.Runtime.InteropServices
Namespace ActiveX
Public Interface IActiveXPrintLib
Sub PrintFile(filePath As String)
End Interface
<ProgId("ActiveX.PrintFile")>
<ClassInterface(ClassInterfaceType.AutoDual)>
<ComVisible(True)>
Public Class ActiveXPrintLib
Implements IActiveXPrintLib
<ComVisible(True)>
Public Sub PrintFile(filePath As String) Implements IActiveXPrintLib.PrintFile
Dim AcroPdf As New AcroPDFLib.AcroPDF()
AcroPdf.LoadFile(filePath)
AcroPdf.printAll()
End Sub
End Class
End Namespace
I have created the dll and register the dll using regasm command on client machine.
Now at time of consuming the print method at client side using javascript as
<object id="myControl1" name="myControl1" classid="clsid:ActiveX.ActiveXPrintLib"> </object>
var axComponent = new ActiveXObject("ActiveX.ActiveXPrintLib");
alert(axComponent);
if (axComponent == null) {
alert('Probably not installed');
}
else {
yourAxComponent.PrintFile('<%= Page.ResolveUrl("~/PdfTemp/0d818b57-9384-4f52-ae89-8ef5868064b7.pdf") %>');
alert('yes');
}
I am getting the error that automation server can't create object, using IE I have enabled the active x and permisssions to download the activex. but it still gives me same error.
I am not sure whether my code is correct or not.
Any advice is really helpful.
Thanks in advance.
Sachin Mehndiratta
mehndi.sachin@gmail.com
|
|
|
|
|
Hello !
I have a login form .
When this login form is still open , i open my main form , and on the load event of main form i give the instruction to close the login form.
But i have a problem :
My idea is to close the login form when the main form is full loaded. ( On my main form i have some controls that should be filled from a database )
But when the login form is closed , my main form is not full loaded.Some controls are still filling with records , and after some second the form is full loaded.
I try to put the close command for the login form at the end of load event (or shown event ) of main form , but the result is the same and the login form is closed before the main form is full loaded.
What can i do ?
Thank you !
|
|
|
|
|
dilkonika wrote: My idea is to close the login form when the main form is full loaded.
Keep in mind that such won't work nicely with lazy-loading.
dilkonika wrote: What can i do ?
How do you load the data? Close the form when that's done.
(how do you know when you're done loading all records? You'd need to select the count before executing your query)
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I load all the data in form's load event.
At the end of this sub I put the close instruction for the login form.
But the login form is closed before the main form is full loading and after the login form is closed , the controls on main form need some time to finish loading.
|
|
|
|
|
dilkonika wrote: At the end of this sub I put the close instruction for the login form.
Can you post that code here?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
It sounds as though the WM_PAINT messages for the main form are not being processed because, well, you're loading data and setting control values, hogging the UI (startup) thread, preventing it from processing the incoming window messages.
Those messages won't get processed, and hence, the main window painted, until the Load method is done executing.
Really, I think you're nit-picking over something that's inconsequential to the functionality of the application.
|
|
|
|
|
Do you have a solution ?
Because for me is important this thing.
|
|
|
|
|
Perhaps you better explain why having the login form stick around until after the main form grid is filled this is so important.
After 35 years of writing code, this has to be the most ridiculous requirement I've ever heard.
|
|
|
|
|
dilkonika wrote: Do you have a solution ?
Keep the loginform in front of the main-form. Don't have ANY long-running code in the constructor or the Load event of the mainform. Start a new thread when the form is shown to fetch the data, invoke when ready to display. You can additionally stop the painting if you don't want to paint while you're still updating, but that would leave a white background if the region is invalidated.
..quite an effort, with little reward.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Guys,
This time am struck with the below scenario please assist.
Am copying few files to the below location using my script.
%AppData%\Mozilla\Firefox\Profiles\6zejl8ne.default\
i need to copy files inside 6zejl8ne.default folder.
But the issue is this folder name is not constant, folder name changes for every user.
for e.g:
user 1 will have: %AppData%\Mozilla\Firefox\Profiles\6zejl8ne.default\
user 2 will have: %AppData%\Mozilla\Firefox\Profiles\4qi4qup6.default\
please assist is ther anyway i have files to the above location.
My script:
appdata = objShell.ExpandEnvironmentStrings("%appdata%")
tmp = objShell.ExpandEnvironmentStrings("%Temp%")
Const OverwriteExisting = True
objFSO.CopyFile tmp & "\Files\*.xpi",appdata & "\Mozilla\Firefox\Profiles\6zejl8ne.default\extensions\" , OverwriteExisting
where, 6zejl8ne.default folder will change for every user.
is that we can assign that folder to constant so that i can run this script on every machine ?
Cheers,
|
|
|
|
|
OK, so how are you going to determine what the folder name is?
Because once you have the folder name, the rest is very simple string manipulation to build the path.
|
|
|
|
|
I have the following sttring arrays and I would like to use IEnumerator.moveNext in order to move to the next string. Here is my code below it always in position -1. I need to start at 0 until HighLeveBenefitHeader
Dim HighLeveBenefitHeader() As String = {"Health Center Information", "", "", "", "", ""}
Dim myHighLeveBenefitEnum As IEnumerator = HighLeveBenefitHeader.GetEnumerator()
'Add Header row
Dim HeaderHighLevelBenefit As Row = CreateTableHeaderRow(currentRow)
' Add Cells
For Each value As String In HighLeveBenefitHeader
CreateTextTableCell(HeaderHighLevelBenefit, myHighLeveBenefitEnum.MoveNext, value.ToString, currentRow, 182, True)
Next
sheetData.AppendChild(HeaderHighLevelBenefit)
|
|
|
|
|
The <a href="http://msdn.microsoft.com/en-us/library/system.collections.ienumerator.movenext.aspx">MoveNext</a>[<a href="http://msdn.microsoft.com/en-us/library/system.collections.ienumerator.movenext.aspx" target="_blank" title="New Window">^</a>] returns a boolean, not the current value of the enumerator. It does move to the next string, it just doesn't return it. MoveNext before you CreateTextTableCell and use the Current property to retrieve the string.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
if i have database contain list of Autocad Architectural elements
simple(like cornice) and
complex(like window,door,column)
and i received new design for cornice and i want to check if this new cornice is available in my database
there is any way to make the compare using programming languages ??
|
|
|
|
|
The only way to check, would be to compare every property of each object.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
