VB versus VB.NET timings!!

Hi,

As part of an evaluation of a small utility that I wrote that converts some
data held in a large number of Excel spreadsheets into SQL Server, I decided
to convert the utility to VB .NET and run both versions both in the IDE and
as standalone compiled exe and see what the difference in time to complete
was.

The utility has to read 3842 single page Excel Sheets that contain a block
of data 12 rows deep and 55 columns across, this is then transferred into an
SQL Server table with the same number of columns.

I thought VB .NET might be slower, but I was surprised by how much.

I would like to release the findings to this community for comment, but I
remembered something in the EULA for .NET that says you can't release
benchmarks. Now I am not in the business of falling out with anyone and I
don't want to get into trouble with MS (particuarly as I make my living on
the back of Microsoft's products). What I would like to release is just a
table showing the start and end times for each run of my program and the
calculated elapsed times bothe for VB6 and VB .NET.

If anyone from MS is looking in I would be pleased to know if you would be
unhappy about me releasing my findings??

Siv

* "Siv" <gs@remove.sivill.com> scripsit:[color=blue]
> I would like to release the findings to this community for comment, but I
> remembered something in the EULA for .NET that says you can't release
> benchmarks.[/color]

Re-read the EULA. I remember that benchmarks were forbidden for the
beta version, but this is maybe not the case for the release versions.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

Herfried,

I had a look on Google and found the following link to the EULA for the .NET
framework 1.1 Redistributable, which seems to contain the line that I
remember seeing pop up somewhere:

a.. You may not disclose the results of any benchmark test of the .NET
Framework component of the OS Components to any third party without
Microsoft's prior written approval.

I tried hunting through the .NET IDE help system looking for "Licence"
"EULA" etc but couldn't find anything.

I suppose the question is, does releasing the timings I have done with my
applications constitute a "benchmark", I don't think they do, as I am sure
someone will point out that my program should be re-written from scratch
using VB.NET rather than by converting a VB6 app.

My reason for posting it would be to see if anyone had any ideas on how the
application could be improved to get better speed under .NET (as you know
from my previous posts I am converting from VB6 to VB.NET and am looking to
_learn_ rather than take a swipe at Microsoft and I expect to get some
useful information from this group about squeezing best performance from
..NET).

Siv

"Herfried K. Wagner [MVP]" <hirf-spam-me-here@gmx.at> wrote in message
news:O8jv2LPmEHA.3712@TK2MSFTNGP15.phx.gbl...[color=blue]
>* "Siv" <gs@remove.sivill.com> scripsit:[color=green]
>> I would like to release the findings to this community for comment, but I
>> remembered something in the EULA for .NET that says you can't release
>> benchmarks.[/color]
>
> Re-read the EULA. I remember that benchmarks were forbidden for the
> beta version, but this is maybe not the case for the release versions.
>
> --
> M S Herfried K. Wagner
> M V P <URL:http://dotnet.mvps.org/>
> V B <URL:http://dotnet.mvps.org/dotnet/faqs/>[/color]

On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
wrote:
[color=blue]
>Hi,
>
>As part of an evaluation of a small utility that I wrote that converts some
>data held in a large number of Excel spreadsheets into SQL Server, I decided
>to convert the utility to VB .NET and run both versions both in the IDE and
>as standalone compiled exe and see what the difference in time to complete
>was.
>
>The utility has to read 3842 single page Excel Sheets that contain a block
>of data 12 rows deep and 55 columns across, this is then transferred into an
>SQL Server table with the same number of columns.
>
>I thought VB .NET might be slower, but I was surprised by how much.[/color]

Make sure you have Option Explicit On and Option Strict set. Watch
out for string concatenation (use System.Text.StringBuilder instead).
Watch out for legacy VB methods, classes, etc., and use .NET versions
instead.
[color=blue]
>I would like to release the findings to this community for comment, but I
>remembered something in the EULA for .NET that says you can't release
>benchmarks. Now I am not in the business of falling out with anyone and I
>don't want to get into trouble with MS (particuarly as I make my living on
>the back of Microsoft's products). What I would like to release is just a
>table showing the start and end times for each run of my program and the
>calculated elapsed times bothe for VB6 and VB .NET.
>
>If anyone from MS is looking in I would be pleased to know if you would be
>unhappy about me releasing my findings??
>
>Siv
>[/color]

I ANAL, but you should be fine; you're trying to figure out what's
wrong with your code. It's to prevent articles like "C# vs. Java",
etc.

Austin

The EULA states:

" 3.4 Benchmark Testing. The Software may contain the Microsoft
..NET Framework. You may not disclose the results of any benchmark test of
the .NET Framework component of the Software to any third party without
Microsoft's prior written approval."

However, the EULA also states:

" 1.1 General License Grant. Microsoft grants to you as an
individual, a personal, nonexclusive license to use the Software, and to
make and use copies of the Software for the purposes of designing,
developing, testing, and demonstrating your software product(s), provided
that you are the only individual using the Software. "

The EULA specifically provides for "demonstrating your software product(s)",
so that's quite a conflict.

Note that just because a license/contract states 'this or that" does not
mean that "this or that" is legal, enforceable, or would be upheld by any
rational court of competent jurisdiction.
--
http://www.standards.com/; See Howard Kaikow's web site.

Thanks for the advice, I do have Option Explicit on, but not Option Strict.
The conversion routine picked up a few "I can't figure ot the default property" errors, but I can't see a way round those as I am using OLE Automation into Excel to open the sheets. The code is as follows:

================================================== =================================================
Dim FName, SheetName As String

Dim DataArray(12, 60) As Object

Dim mth, cl As Short

Dim xlc, xlr, n As Short

Dim rs As ADODB.Recordset

Dim Cn As ADODB.Connection

Dim strSQL, strCnn, MSg As String

Dim XLApp As Object

Dim LastM As Short

On Error GoTo ConvertRoutine_Err

ConvertRoutine = False 'Start off assuming fail and correct this if we do succeed.

'Work out the lastM var from LastMonth

LastM = Val(LastMonth)

FileOpen(1, "C:\T7ConversionLog.txt", OpenMode.Append)

PrintLine(1, "TACSY 7 Conversion Log - " & Now)

PrintLine(1, "===========================================")

PrintLine(1, " ")

'Look for the files in the source directory by

FName = Dir(tp & "*.xls") 'tp is the var holding the path to the spreadsheets being processed.

If FName = "" Then

'No xls files in this directory

MSg = "WARNING: No Excel files found in:" & NL & NL

MSg = MSg & tp & NL & NL

MSg = MSg & "Click OK to exit the Conversion Routine, "

MSg = MSg & "so that you can reselect the source folder."

MsgBox(MSg, MsgBoxStyle.Information, H)

PrintLine(1, "No Files found in selected folder ( " & tp & " ) to convert!")

PrintLine(1, "Aborting Conversion Run at " & Now)

FileClose(1)

Exit Function

Else

'Found some XL files.

'So Get Excel fired up

XLApp = CreateObject("Excel.Application")

XLApp.screenupdating = False

'Get the Tacsy7Data tble open ready to add records

Cn = New ADODB.Connection

Cn.ConnectionString = "Driver={SQL Server};Server=SERVER;UID=;PWD=;DATABASE=TACSY"

Cn.Open()

rs = New ADODB.Recordset

rs.Open("SELECT * FROM Tacsy7Data", Cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)

Do

XLApp.workbooks.Open(tp & FName)

lblReadout.Text = "Processing " & FName & "."

PrintLine(1, "Started converting " & tp & FName & " at " & Now)

System.Windows.Forms.Application.DoEvents() 'Let system in for a tick + allow readout refresh.

xlr = 64 'Row start, 1 less than desired so that the mth can be added to it to get the correct row.

DataArray(0, 0) = XLApp.cells(50, 4).Value 'Get the points score

If XLApp.cells(84, 2).Value = "EmpNo" Then

DataArray(0, 1) = VB.Left(FName, Len(FName) - 4)

Else

DataArray(0, 1) = XLApp.cells(84, 2).Value 'Get EmpNo

End If

For mth = 1 To 12

For cl = 1 To 55

'Read the existing data into the array

DataArray(mth, cl) = XLApp.cells(xlr + mth, cl).Value

Next cl

Next mth

'Array now has all the data from the old sheet - close the sheet

XLApp.workbooks(FName).Close(savechanges:=False) 'close but do not save

For mth = 1 To 12

'Read the data back from the array - Up to col 36 they are the same, then

'the new column appears at the 37th column and then the data is the same to the end.

Select Case mth

Case 1 To 5

rs.AddNew()

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("EmpNo").Value = Val(DataArray(0, 1)) 'Employee Number

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(0, 0). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("PointsScoreAtThisMonth").Value = DataArray(0, 0) 'Points score.

If mth > LastM Then

rs.Fields("Year").Value = Year(Now) - 1

Else

rs.Fields("Year").Value = VB6.Format(Now, "YYYY")

End If

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 1). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("TextMonthNumber").Value = DataArray(mth, 1)

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 2). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("RollMonthNumber").Value = DataArray(mth, 2)

... Lots more fields as per the above ...

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 55). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("BusMixPCInvNumShade").Value = DataArray(mth, 55)

rs.Update()

Case 6 To 12 'beyond m6 data is screwed from column 36, data is in 1 to right of where it should be ie 37.

rs.AddNew()

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("EmpNo").Value = Val(DataArray(0, 1)) 'Employee Number

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(0, 0). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("PointsScoreAtThisMonth").Value = DataArray(0, 0) 'Points score.

If mth > LastM Then

rs.Fields("Year").Value = Year(Now) - 1

Else

rs.Fields("Year").Value = VB6.Format(Now, "YYYY")

End If

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 1). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("TextMonthNumber").Value = DataArray(mth, 1)

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 2). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("RollMonthNumber").Value = DataArray(mth, 2)

... Lots more fields as per the above ...

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, 55). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("BusMixPCInvNum").Value = DataArray(mth, 55)

rs.Fields("BusMixPCInvNumShade").Value = 0 'Just have to hardcode a value of no shade as there is no data on sheets.

rs.Update()

End Select

Next mth

AfterError:

'Clear the array ready for next loop (if any).

For mth = 1 To 12

For cl = 1 To 55

'Clear the array

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, cl). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

DataArray(mth, cl) = 0

Next cl

Next mth

'do a dir for the next file name or exit if FName=""

'UPGRADE_WARNING: Dir has a new behavior. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1041"'

FName = Dir()

Loop Until FName = "" 'Move on to next sheet.

'Close recordset

rs.Close()

'Write closing log entry

PrintLine(1, " and finished file writes at " & Now) 'Finishes a line started near top of loop

PrintLine(1, "================================================= ====================")

FileClose(1) 'close log file!

'UPGRADE_WARNING: Couldn't resolve default property of object XLApp.screenupdating. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

XLApp.screenupdating = True 'Turn on screen updating

'UPGRADE_WARNING: Couldn't resolve default property of object XLApp.quit. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

XLApp.quit() 'Quit excel

End If

ConvertRoutine = True

ConvertRoutine_End:

Exit Function

ConvertRoutine_Err:

Select Case Err.Number

Case 13 'Type Mismatch

Resume Next

Case 94 'Invalid Use of Null

Resume Next

Case 3021 'No record found

Resume ConvertRoutine_End

Case 3022 'Duplicate record

Resume ConvertRoutine_End

Case -2147217887 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

rs.CancelUpdate() 'Will occurr if dataarray(x,y) is empty due to sheet being empty?? Found one so have put in this patch to bypass it.

Resume AfterError

Resume 'debugging

Case Else

Me.Cursor = System.Windows.Forms.Cursors.Default

Call ProgErrorHandler("ConvertRoutine in frmConvertT7ToData", False)

Resume ConvertRoutine_End

Resume 'Debugging

End Select

================================================== ===

I have retained the "On error Goto" because using Try, Catch, Finally I can't find out how to trap a specific error like the -2147217887 Multiple Step OLE error the help on this is "pants".

Siv

"Austin Ehlers" <liberal*number_eight*@ku.edu> wrote in message news:f079k0599a8638b8t9of97g2k8q3j5lvjm@4ax.com...[color=blue]
> On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
> wrote:
> [color=green]
>>Hi,
>>
>>As part of an evaluation of a small utility that I wrote that converts some
>>data held in a large number of Excel spreadsheets into SQL Server, I decided
>>to convert the utility to VB .NET and run both versions both in the IDE and
>>as standalone compiled exe and see what the difference in time to complete
>>was.
>>
>>The utility has to read 3842 single page Excel Sheets that contain a block
>>of data 12 rows deep and 55 columns across, this is then transferred into an
>>SQL Server table with the same number of columns.
>>
>>I thought VB .NET might be slower, but I was surprised by how much.[/color]
>
> Make sure you have Option Explicit On and Option Strict set. Watch
> out for string concatenation (use System.Text.StringBuilder instead).
> Watch out for legacy VB methods, classes, etc., and use .NET versions
> instead.
> [color=green]
>>I would like to release the findings to this community for comment, but I
>>remembered something in the EULA for .NET that says you can't release
>>benchmarks. Now I am not in the business of falling out with anyone and I
>>don't want to get into trouble with MS (particuarly as I make my living on
>>the back of Microsoft's products). What I would like to release is just a
>>table showing the start and end times for each run of my program and the
>>calculated elapsed times bothe for VB6 and VB .NET.
>>
>>If anyone from MS is looking in I would be pleased to know if you would be
>>unhappy about me releasing my findings??
>>
>>Siv
>>[/color]
>
> I ANAL, but you should be fine; you're trying to figure out what's
> wrong with your code. It's to prevent articles like "C# vs. Java",
> etc.
>
> Austin
>[/color]

Howard,

Thanks for your insight. I just want to understand why my code compiled
under VB.NET runs way slower than under VB6 and if there are any ways to
improve how my apps run under VB.NET. I am increasingly finding that once I
have peeled off the outer layers of confusion and find ways to do stuff with
VB.NET I really like working with it, it's just frustrating that as a guy
who has been dveloping applications in VB since Version 1.0 I feel like a
complete beginner again going from VB6 to VB8 and on top of that the new
version runs at about 70% the speed of the old version???

I am a small "one man band" developing apps for customers and couldn't
afford to spend any time in litigation with the likes of Microsoft!

Siv

"Howard Kaikow" <kaikow@standards.com> wrote in message
news:ePeW1lQmEHA.3156@TK2MSFTNGP12.phx.gbl...[color=blue]
> The EULA states:
>
> " 3.4 Benchmark Testing. The Software may contain the Microsoft
> .NET Framework. You may not disclose the results of any benchmark test of
> the .NET Framework component of the Software to any third party without
> Microsoft's prior written approval."
>
> However, the EULA also states:
>
> " 1.1 General License Grant. Microsoft grants to you as an
> individual, a personal, nonexclusive license to use the Software, and to
> make and use copies of the Software for the purposes of designing,
> developing, testing, and demonstrating your software product(s), provided
> that you are the only individual using the Software. "
>
> The EULA specifically provides for "demonstrating your software
> product(s)",
> so that's quite a conflict.
>
> Note that just because a license/contract states 'this or that" does not
> mean that "this or that" is legal, enforceable, or would be upheld by any
> rational court of competent jurisdiction.
> --
> http://www.standards.com/; See Howard Kaikow's web site.
>
>[/color]

Hello Siv:
You wrote in conference microsoft.public.dotnet.languages.vb on Sun, 12 Sep
2004 18:45:48 +0100:

S> I would like to release the findings to this community for comment, but
S> I remembered something in the EULA for .NET that says you can't release
S> benchmarks.

....of the .net framework, which is not the case. You are releasing the
benchmark of your applications. A benchmark of the whole framework would be
quite a project.

Vadim
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com

I am sure you are right:

Details as follows:
VB6 Versus VB.NET
Actual Elapsed
Time
Time Taken
To Process
Difference Between
VB6 and VB.NET
% VB6

VB6 In VB6 IDE - Start:
10/09/2004 21:48:27

VB6 In VB6 IDE - End:
10/09/2004 23:39:09
01:50:42
00:49:54
68.92902

VB6 Compiled EXE - Start:
12/09/2004 15:32:32

VB6 Compiled EXE - End:
12/09/2004 17:22:53
01:50:21
00:42:48
72.05354

VB.NET IDE - Start:
11/09/2004 15:47:22

VB.NET IDE - End:
11/09/2004 18:27:58
02:40:36

VB.NET Compiled Release Binary - Start:
12/09/2004 02:18:11

VB.NET Compiled Release Binary - End:
12/09/2004 04:51:20
02:33:09

Running the app in the IDE 69% of VB6, as a compiled EXE 72%.

This seems to me to be a large difference. Why?

Please see previous posts to see the code. Clearly code just run through the VB converter runs at around 70% of the speed of VB6, clearly the converter is only the starting point, you need to rewrite the code from scratch to get best performance from .NET.

Siv

"Vadim Rapp" <vr@myrealbox.nospam.com> wrote in message news:%235hTGISmEHA.1724@TK2MSFTNGP10.phx.gbl...[color=blue]
> Hello Siv:
> You wrote in conference microsoft.public.dotnet.languages.vb on Sun, 12 Sep
> 2004 18:45:48 +0100:
>
> S> I would like to release the findings to this community for comment, but
> S> I remembered something in the EULA for .NET that says you can't release
> S> benchmarks.
>
> ...of the .net framework, which is not the case. You are releasing the
> benchmark of your applications. A benchmark of the whole framework would be
> quite a project.
>
>
> Vadim
> ----------------------------------------
> Vadim Rapp Consulting
> SQL, Access, VB Solutions
> 847-685-9073
> www.vadimrapp.com
>[/color]

The code will run a lot faster if you use With ... End With to reduce the
number of object references.

--
http://www.standards.com/; See Howard Kaikow's web site.
"Siv" <gs@remove.sivill.com> wrote in message
news:%23Ey6QmRmEHA.896@TK2MSFTNGP12.phx.gbl...
Thanks for the advice, I do have Option Explicit on, but not Option Strict.
The conversion routine picked up a few "I can't figure ot the default
property" errors, but I can't see a way round those as I am using OLE
Automation into Excel to open the sheets. The code is as follows:

================================================== ==========================
=======================
Dim FName, SheetName As String

Dim DataArray(12, 60) As Object

Dim mth, cl As Short

Dim xlc, xlr, n As Short

Dim rs As ADODB.Recordset

Dim Cn As ADODB.Connection

Dim strSQL, strCnn, MSg As String

Dim XLApp As Object

Dim LastM As Short

On Error GoTo ConvertRoutine_Err

ConvertRoutine = False 'Start off assuming fail and correct this if
we do succeed.

'Work out the lastM var from LastMonth

LastM = Val(LastMonth)

FileOpen(1, "C:\T7ConversionLog.txt", OpenMode.Append)

PrintLine(1, "TACSY 7 Conversion Log - " & Now)

PrintLine(1, "===========================================")

PrintLine(1, " ")

'Look for the files in the source directory by

FName = Dir(tp & "*.xls") 'tp is the var holding the path to the
spreadsheets being processed.

If FName = "" Then

'No xls files in this directory

MSg = "WARNING: No Excel files found in:" & NL & NL

MSg = MSg & tp & NL & NL

MSg = MSg & "Click OK to exit the Conversion Routine, "

MSg = MSg & "so that you can reselect the source folder."

MsgBox(MSg, MsgBoxStyle.Information, H)

PrintLine(1, "No Files found in selected folder ( " & tp & " )
to convert!")

PrintLine(1, "Aborting Conversion Run at " & Now)

FileClose(1)

Exit Function

Else

'Found some XL files.

'So Get Excel fired up

XLApp = CreateObject("Excel.Application")

XLApp.screenupdating = False

'Get the Tacsy7Data tble open ready to add records

Cn = New ADODB.Connection

Cn.ConnectionString = "Driver={SQL
Server};Server=SERVER;UID=;PWD=;DATABASE=TACSY"

Cn.Open()

rs = New ADODB.Recordset

rs.Open("SELECT * FROM Tacsy7Data", Cn,
ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic,
ADODB.CommandTypeEnum.adCmdText)

Do

XLApp.workbooks.Open(tp & FName)

lblReadout.Text = "Processing " & FName & "."

PrintLine(1, "Started converting " & tp & FName & " at " &
Now)

System.Windows.Forms.Application.DoEvents() 'Let system in
for a tick + allow readout refresh.

xlr = 64 'Row start, 1 less than desired so that the mth can
be added to it to get the correct row.

DataArray(0, 0) = XLApp.cells(50, 4).Value 'Get the points
score

If XLApp.cells(84, 2).Value = "EmpNo" Then

DataArray(0, 1) = VB.Left(FName, Len(FName) - 4)

Else

DataArray(0, 1) = XLApp.cells(84, 2).Value 'Get EmpNo

End If

For mth = 1 To 12

For cl = 1 To 55

'Read the existing data into the array

DataArray(mth, cl) = XLApp.cells(xlr + mth,
cl).Value

Next cl

Next mth

'Array now has all the data from the old sheet - close the
sheet

XLApp.workbooks(FName).Close(savechanges:=False) 'close but
do not save

For mth = 1 To 12

'Read the data back from the array - Up to col 36 they
are the same, then

'the new column appears at the 37th column and then the
data is the same to the end.

Select Case mth

Case 1 To 5

rs.AddNew()

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
'Employee Number

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.

If mth > LastM Then

rs.Fields("Year").Value = Year(Now) - 1

Else

rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")

End If

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)

... Lots more fields as per the above ...

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("BusMixPCInvNumShade").Value =
DataArray(mth, 55)

rs.Update()

Case 6 To 12 'beyond m6 data is screwed from column
36, data is in 1 to right of where it should be ie 37.

rs.AddNew()

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
'Employee Number

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.

If mth > LastM Then

rs.Fields("Year").Value = Year(Now) - 1

Else

rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")

End If

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)

... Lots more fields as per the above ...

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("BusMixPCInvNum").Value =
DataArray(mth, 55)

rs.Fields("BusMixPCInvNumShade").Value = 0 'Just
have to hardcode a value of no shade as there is no data on sheets.

rs.Update()

End Select

Next mth

AfterError:

'Clear the array ready for next loop (if any).

For mth = 1 To 12

For cl = 1 To 55

'Clear the array

'UPGRADE_WARNING: Couldn't resolve default property
of object DataArray(mth, cl). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

DataArray(mth, cl) = 0

Next cl

Next mth

'do a dir for the next file name or exit if FName=""

'UPGRADE_WARNING: Dir has a new behavior. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1041"'

FName = Dir()

Loop Until FName = "" 'Move on to next sheet.

'Close recordset

rs.Close()

'Write closing log entry

PrintLine(1, " and finished file writes at " & Now) 'Finishes a
line started near top of loop

PrintLine(1,
"================================================= ====================")

FileClose(1) 'close log file!

'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.screenupdating. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

XLApp.screenupdating = True 'Turn on screen updating

'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.quit. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

XLApp.quit() 'Quit excel

End If

ConvertRoutine = True

ConvertRoutine_End:

Exit Function

ConvertRoutine_Err:

Select Case Err.Number

Case 13 'Type Mismatch

Resume Next

Case 94 'Invalid Use of Null

Resume Next

Case 3021 'No record found

Resume ConvertRoutine_End

Case 3022 'Duplicate record

Resume ConvertRoutine_End

Case -2147217887 'Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.

rs.CancelUpdate() 'Will occurr if dataarray(x,y) is empty
due to sheet being empty?? Found one so have put in this patch to bypass it.

Resume AfterError

Resume 'debugging

Case Else

Me.Cursor = System.Windows.Forms.Cursors.Default

Call ProgErrorHandler("ConvertRoutine in
frmConvertT7ToData", False)

Resume ConvertRoutine_End

Resume 'Debugging

End Select

================================================== ===

I have retained the "On error Goto" because using Try, Catch, Finally I
can't find out how to trap a specific error like the -2147217887 Multiple
Step OLE error the help on this is "pants".

Siv

"Austin Ehlers" <liberal*number_eight*@ku.edu> wrote in message
news:f079k0599a8638b8t9of97g2k8q3j5lvjm@4ax.com...[color=blue]
> On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
> wrote:
>[color=green]
>>Hi,
>>
>>As part of an evaluation of a small utility that I wrote that converts[/color][/color]
some[color=blue][color=green]
>>data held in a large number of Excel spreadsheets into SQL Server, I[/color][/color]
decided[color=blue][color=green]
>>to convert the utility to VB .NET and run both versions both in the IDE[/color][/color]
and[color=blue][color=green]
>>as standalone compiled exe and see what the difference in time to complete
>>was.
>>
>>The utility has to read 3842 single page Excel Sheets that contain a block
>>of data 12 rows deep and 55 columns across, this is then transferred into[/color][/color]
an[color=blue][color=green]
>>SQL Server table with the same number of columns.
>>
>>I thought VB .NET might be slower, but I was surprised by how much.[/color]
>
> Make sure you have Option Explicit On and Option Strict set. Watch
> out for string concatenation (use System.Text.StringBuilder instead).
> Watch out for legacy VB methods, classes, etc., and use .NET versions
> instead.
>[color=green]
>>I would like to release the findings to this community for comment, but I
>>remembered something in the EULA for .NET that says you can't release
>>benchmarks. Now I am not in the business of falling out with anyone and I
>>don't want to get into trouble with MS (particuarly as I make my living on
>>the back of Microsoft's products). What I would like to release is just a
>>table showing the start and end times for each run of my program and the
>>calculated elapsed times bothe for VB6 and VB .NET.
>>
>>If anyone from MS is looking in I would be pleased to know if you would be
>>unhappy about me releasing my findings??
>>
>>Siv
>>[/color]
>
> I ANAL, but you should be fine; you're trying to figure out what's
> wrong with your code. It's to prevent articles like "C# vs. Java",
> etc.
>
> Austin
>[/color]

Are you sure, or do you mean the code will be more concise??
Siv

"Howard Kaikow" <kaikow@standards.com> wrote in message
news:ebUfxYSmEHA.3156@TK2MSFTNGP12.phx.gbl...[color=blue]
> The code will run a lot faster if you use With ... End With to reduce the
> number of object references.
>
> --
> http://www.standards.com/; See Howard Kaikow's web site.
> "Siv" <gs@remove.sivill.com> wrote in message
> news:%23Ey6QmRmEHA.896@TK2MSFTNGP12.phx.gbl...
> Thanks for the advice, I do have Option Explicit on, but not Option
> Strict.
> The conversion routine picked up a few "I can't figure ot the default
> property" errors, but I can't see a way round those as I am using OLE
> Automation into Excel to open the sheets. The code is as follows:
>
> ================================================== ==========================
> =======================
> Dim FName, SheetName As String
>
> Dim DataArray(12, 60) As Object
>
> Dim mth, cl As Short
>
> Dim xlc, xlr, n As Short
>
> Dim rs As ADODB.Recordset
>
> Dim Cn As ADODB.Connection
>
> Dim strSQL, strCnn, MSg As String
>
> Dim XLApp As Object
>
> Dim LastM As Short
>
>
>
> On Error GoTo ConvertRoutine_Err
>
>
>
> ConvertRoutine = False 'Start off assuming fail and correct this if
> we do succeed.
>
>
>
> 'Work out the lastM var from LastMonth
>
> LastM = Val(LastMonth)
>
>
>
> FileOpen(1, "C:\T7ConversionLog.txt", OpenMode.Append)
>
>
>
> PrintLine(1, "TACSY 7 Conversion Log - " & Now)
>
> PrintLine(1, "===========================================")
>
> PrintLine(1, " ")
>
>
>
>
>
> 'Look for the files in the source directory by
>
> FName = Dir(tp & "*.xls") 'tp is the var holding the path to the
> spreadsheets being processed.
>
> If FName = "" Then
>
> 'No xls files in this directory
>
> MSg = "WARNING: No Excel files found in:" & NL & NL
>
> MSg = MSg & tp & NL & NL
>
> MSg = MSg & "Click OK to exit the Conversion Routine, "
>
> MSg = MSg & "so that you can reselect the source folder."
>
> MsgBox(MSg, MsgBoxStyle.Information, H)
>
> PrintLine(1, "No Files found in selected folder ( " & tp & " )
> to convert!")
>
> PrintLine(1, "Aborting Conversion Run at " & Now)
>
> FileClose(1)
>
> Exit Function
>
> Else
>
> 'Found some XL files.
>
> 'So Get Excel fired up
>
> XLApp = CreateObject("Excel.Application")
>
> XLApp.screenupdating = False
>
>
>
> 'Get the Tacsy7Data tble open ready to add records
>
> Cn = New ADODB.Connection
>
> Cn.ConnectionString = "Driver={SQL
> Server};Server=SERVER;UID=;PWD=;DATABASE=TACSY"
>
> Cn.Open()
>
>
>
> rs = New ADODB.Recordset
>
> rs.Open("SELECT * FROM Tacsy7Data", Cn,
> ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic,
> ADODB.CommandTypeEnum.adCmdText)
>
>
>
> Do
>
> XLApp.workbooks.Open(tp & FName)
>
>
>
> lblReadout.Text = "Processing " & FName & "."
>
> PrintLine(1, "Started converting " & tp & FName & " at " &
> Now)
>
> System.Windows.Forms.Application.DoEvents() 'Let system in
> for a tick + allow readout refresh.
>
> xlr = 64 'Row start, 1 less than desired so that the mth
> can
> be added to it to get the correct row.
>
>
>
> DataArray(0, 0) = XLApp.cells(50, 4).Value 'Get the points
> score
>
>
>
> If XLApp.cells(84, 2).Value = "EmpNo" Then
>
> DataArray(0, 1) = VB.Left(FName, Len(FName) - 4)
>
> Else
>
> DataArray(0, 1) = XLApp.cells(84, 2).Value 'Get EmpNo
>
> End If
>
>
>
> For mth = 1 To 12
>
> For cl = 1 To 55
>
> 'Read the existing data into the array
>
> DataArray(mth, cl) = XLApp.cells(xlr + mth,
> cl).Value
>
> Next cl
>
> Next mth
>
>
>
> 'Array now has all the data from the old sheet - close the
> sheet
>
> XLApp.workbooks(FName).Close(savechanges:=False) 'close but
> do not save
>
>
>
>
>
>
>
> For mth = 1 To 12
>
> 'Read the data back from the array - Up to col 36 they
> are the same, then
>
> 'the new column appears at the 37th column and then the
> data is the same to the end.
>
> Select Case mth
>
> Case 1 To 5
>
> rs.AddNew()
>
> 'UPGRADE_WARNING: Couldn't resolve default
> property of object DataArray(). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
> 'Employee Number
>
> 'UPGRADE_WARNING: Couldn't resolve default
> property of object DataArray(0, 0). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> rs.Fields("PointsScoreAtThisMonth").Value =
> DataArray(0, 0) 'Points score.
>
> If mth > LastM Then
>
> rs.Fields("Year").Value = Year(Now) - 1
>
> Else
>
> rs.Fields("Year").Value = VB6.Format(Now,
> "YYYY")
>
> End If
>
> 'UPGRADE_WARNING: Couldn't resolve default
> property of object DataArray(mth, 1). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> rs.Fields("TextMonthNumber").Value =
> DataArray(mth, 1)
>
> 'UPGRADE_WARNING: Couldn't resolve default
> property of object DataArray(mth, 2). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> rs.Fields("RollMonthNumber").Value =
> DataArray(mth, 2)
>
>
>
> ... Lots more fields as per the above ...
>
>
>
> 'UPGRADE_WARNING: Couldn't resolve default
> property of object DataArray(mth, 55). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> rs.Fields("BusMixPCInvNumShade").Value =
> DataArray(mth, 55)
>
> rs.Update()
>
>
>
> Case 6 To 12 'beyond m6 data is screwed from column
> 36, data is in 1 to right of where it should be ie 37.
>
> rs.AddNew()
>
> 'UPGRADE_WARNING: Couldn't resolve default
> property of object DataArray(). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
> 'Employee Number
>
> 'UPGRADE_WARNING: Couldn't resolve default
> property of object DataArray(0, 0). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> rs.Fields("PointsScoreAtThisMonth").Value =
> DataArray(0, 0) 'Points score.
>
> If mth > LastM Then
>
> rs.Fields("Year").Value = Year(Now) - 1
>
> Else
>
> rs.Fields("Year").Value = VB6.Format(Now,
> "YYYY")
>
> End If
>
> 'UPGRADE_WARNING: Couldn't resolve default
> property of object DataArray(mth, 1). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> rs.Fields("TextMonthNumber").Value =
> DataArray(mth, 1)
>
> 'UPGRADE_WARNING: Couldn't resolve default
> property of object DataArray(mth, 2). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> rs.Fields("RollMonthNumber").Value =
> DataArray(mth, 2)
>
>
>
> ... Lots more fields as per the above ...
>
>
>
> 'UPGRADE_WARNING: Couldn't resolve default
> property of object DataArray(mth, 55). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> rs.Fields("BusMixPCInvNum").Value =
> DataArray(mth, 55)
>
> rs.Fields("BusMixPCInvNumShade").Value = 0
> 'Just
> have to hardcode a value of no shade as there is no data on sheets.
>
> rs.Update()
>
> End Select
>
> Next mth
>
>
>
> AfterError:
>
> 'Clear the array ready for next loop (if any).
>
> For mth = 1 To 12
>
> For cl = 1 To 55
>
> 'Clear the array
>
> 'UPGRADE_WARNING: Couldn't resolve default property
> of object DataArray(mth, cl). Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> DataArray(mth, cl) = 0
>
> Next cl
>
> Next mth
>
>
>
>
>
>
>
> 'do a dir for the next file name or exit if FName=""
>
> 'UPGRADE_WARNING: Dir has a new behavior. Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1041"'
>
> FName = Dir()
>
>
>
> Loop Until FName = "" 'Move on to next sheet.
>
>
>
> 'Close recordset
>
> rs.Close()
>
>
>
> 'Write closing log entry
>
> PrintLine(1, " and finished file writes at " & Now) 'Finishes a
> line started near top of loop
>
> PrintLine(1,
> "================================================= ====================")
>
>
>
> FileClose(1) 'close log file!
>
>
>
> 'UPGRADE_WARNING: Couldn't resolve default property of object
> XLApp.screenupdating. Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> XLApp.screenupdating = True 'Turn on screen updating
>
> 'UPGRADE_WARNING: Couldn't resolve default property of object
> XLApp.quit. Click for more:
> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>
> XLApp.quit() 'Quit excel
>
>
>
> End If
>
>
>
>
>
>
>
>
>
> ConvertRoutine = True
>
>
>
> ConvertRoutine_End:
>
> Exit Function
>
>
>
>
>
> ConvertRoutine_Err:
>
> Select Case Err.Number
>
> Case 13 'Type Mismatch
>
> Resume Next
>
> Case 94 'Invalid Use of Null
>
> Resume Next
>
> Case 3021 'No record found
>
> Resume ConvertRoutine_End
>
> Case 3022 'Duplicate record
>
> Resume ConvertRoutine_End
>
> Case -2147217887 'Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done.
>
> rs.CancelUpdate() 'Will occurr if dataarray(x,y) is empty
> due to sheet being empty?? Found one so have put in this patch to bypass
> it.
>
> Resume AfterError
>
> Resume 'debugging
>
> Case Else
>
> Me.Cursor = System.Windows.Forms.Cursors.Default
>
> Call ProgErrorHandler("ConvertRoutine in
> frmConvertT7ToData", False)
>
> Resume ConvertRoutine_End
>
> Resume 'Debugging
>
> End Select
>
> ================================================== ===
>
> I have retained the "On error Goto" because using Try, Catch, Finally I
> can't find out how to trap a specific error like the -2147217887 Multiple
> Step OLE error the help on this is "pants".
>
> Siv
>
> "Austin Ehlers" <liberal*number_eight*@ku.edu> wrote in message
> news:f079k0599a8638b8t9of97g2k8q3j5lvjm@4ax.com...[color=green]
>> On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
>> wrote:
>>[color=darkred]
>>>Hi,
>>>
>>>As part of an evaluation of a small utility that I wrote that converts[/color][/color]
> some[color=green][color=darkred]
>>>data held in a large number of Excel spreadsheets into SQL Server, I[/color][/color]
> decided[color=green][color=darkred]
>>>to convert the utility to VB .NET and run both versions both in the IDE[/color][/color]
> and[color=green][color=darkred]
>>>as standalone compiled exe and see what the difference in time to
>>>complete
>>>was.
>>>
>>>The utility has to read 3842 single page Excel Sheets that contain a
>>>block
>>>of data 12 rows deep and 55 columns across, this is then transferred into[/color][/color]
> an[color=green][color=darkred]
>>>SQL Server table with the same number of columns.
>>>
>>>I thought VB .NET might be slower, but I was surprised by how much.[/color]
>>
>> Make sure you have Option Explicit On and Option Strict set. Watch
>> out for string concatenation (use System.Text.StringBuilder instead).
>> Watch out for legacy VB methods, classes, etc., and use .NET versions
>> instead.
>>[color=darkred]
>>>I would like to release the findings to this community for comment, but I
>>>remembered something in the EULA for .NET that says you can't release
>>>benchmarks. Now I am not in the business of falling out with anyone and
>>>I
>>>don't want to get into trouble with MS (particuarly as I make my living
>>>on
>>>the back of Microsoft's products). What I would like to release is just
>>>a
>>>table showing the start and end times for each run of my program and the
>>>calculated elapsed times bothe for VB6 and VB .NET.
>>>
>>>If anyone from MS is looking in I would be pleased to know if you would
>>>be
>>>unhappy about me releasing my findings??
>>>
>>>Siv
>>>[/color]
>>
>> I ANAL, but you should be fine; you're trying to figure out what's
>> wrong with your code. It's to prevent articles like "C# vs. Java",
>> etc.
>>
>> Austin
>>[/color]
>
>[/color]

Hi Siv,

Try using Integer instead of Short. That might give you a little boost. Ken.

Hi Siv,

One more thing to try. You could change the DataArray to be of type Integer or Long. It seems like everything in there is a number. If I mis-read then you could still use String instead of Object and it may go faster for you. At least you'll have early binding and you can use the CInt(), CLng(), etc. for conversions which may be faster. Can't hurt to test. Good luck! Ken.

one of the reasons may be because you are using classic ADO instead of the
newer optimized for .NET ADO.NET, maybe try to convert it and see how that
effects the performance.

"Siv" <gs@remove.sivill.com> wrote in message
news:u1mERuRmEHA.3760@TK2MSFTNGP12.phx.gbl...[color=blue]
> Howard,
>
> Thanks for your insight. I just want to understand why my code compiled
> under VB.NET runs way slower than under VB6 and if there are any ways to
> improve how my apps run under VB.NET. I am increasingly finding that once
> I have peeled off the outer layers of confusion and find ways to do stuff
> with VB.NET I really like working with it, it's just frustrating that as a
> guy who has been dveloping applications in VB since Version 1.0 I feel
> like a complete beginner again going from VB6 to VB8 and on top of that
> the new version runs at about 70% the speed of the old version???
>
> I am a small "one man band" developing apps for customers and couldn't
> afford to spend any time in litigation with the likes of Microsoft!
>
> Siv
>
>
> "Howard Kaikow" <kaikow@standards.com> wrote in message
> news:ePeW1lQmEHA.3156@TK2MSFTNGP12.phx.gbl...[color=green]
>> The EULA states:
>>
>> " 3.4 Benchmark Testing. The Software may contain the Microsoft
>> .NET Framework. You may not disclose the results of any benchmark test
>> of
>> the .NET Framework component of the Software to any third party without
>> Microsoft's prior written approval."
>>
>> However, the EULA also states:
>>
>> " 1.1 General License Grant. Microsoft grants to you as an
>> individual, a personal, nonexclusive license to use the Software, and to
>> make and use copies of the Software for the purposes of designing,
>> developing, testing, and demonstrating your software product(s), provided
>> that you are the only individual using the Software. "
>>
>> The EULA specifically provides for "demonstrating your software
>> product(s)",
>> so that's quite a conflict.
>>
>> Note that just because a license/contract states 'this or that" does not
>> mean that "this or that" is legal, enforceable, or would be upheld by any
>> rational court of competent jurisdiction.
>> --
>> http://www.standards.com/; See Howard Kaikow's web site.
>>
>>[/color]
>
>[/color]

if you'd use ADO.NET instead of ADO you could use TRY CATCH END TRY because
it has a exception handler for the OleDB,ODBC, and SQL providers already in
the framework to handle errors

"Siv" <gs@remove.sivill.com> wrote in message
news:%23Ey6QmRmEHA.896@TK2MSFTNGP12.phx.gbl...
Thanks for the advice, I do have Option Explicit on, but not Option Strict.
The conversion routine picked up a few "I can't figure ot the default
property" errors, but I can't see a way round those as I am using OLE
Automation into Excel to open the sheets. The code is as follows:

================================================== =================================================
Dim FName, SheetName As String

Dim DataArray(12, 60) As Object

Dim mth, cl As Short

Dim xlc, xlr, n As Short

Dim rs As ADODB.Recordset

Dim Cn As ADODB.Connection

Dim strSQL, strCnn, MSg As String

Dim XLApp As Object

Dim LastM As Short

On Error GoTo ConvertRoutine_Err

ConvertRoutine = False 'Start off assuming fail and correct this if
we do succeed.

'Work out the lastM var from LastMonth

LastM = Val(LastMonth)

FileOpen(1, "C:\T7ConversionLog.txt", OpenMode.Append)

PrintLine(1, "TACSY 7 Conversion Log - " & Now)

PrintLine(1, "===========================================")

PrintLine(1, " ")

'Look for the files in the source directory by

FName = Dir(tp & "*.xls") 'tp is the var holding the path to the
spreadsheets being processed.

If FName = "" Then

'No xls files in this directory

MSg = "WARNING: No Excel files found in:" & NL & NL

MSg = MSg & tp & NL & NL

MSg = MSg & "Click OK to exit the Conversion Routine, "

MSg = MSg & "so that you can reselect the source folder."

MsgBox(MSg, MsgBoxStyle.Information, H)

PrintLine(1, "No Files found in selected folder ( " & tp & " )
to convert!")

PrintLine(1, "Aborting Conversion Run at " & Now)

FileClose(1)

Exit Function

Else

'Found some XL files.

'So Get Excel fired up

XLApp = CreateObject("Excel.Application")

XLApp.screenupdating = False

'Get the Tacsy7Data tble open ready to add records

Cn = New ADODB.Connection

Cn.ConnectionString = "Driver={SQL
Server};Server=SERVER;UID=;PWD=;DATABASE=TACSY"

Cn.Open()

rs = New ADODB.Recordset

rs.Open("SELECT * FROM Tacsy7Data", Cn,
ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic,
ADODB.CommandTypeEnum.adCmdText)

Do

XLApp.workbooks.Open(tp & FName)

lblReadout.Text = "Processing " & FName & "."

PrintLine(1, "Started converting " & tp & FName & " at " &
Now)

System.Windows.Forms.Application.DoEvents() 'Let system in
for a tick + allow readout refresh.

xlr = 64 'Row start, 1 less than desired so that the mth can
be added to it to get the correct row.

DataArray(0, 0) = XLApp.cells(50, 4).Value 'Get the points
score

If XLApp.cells(84, 2).Value = "EmpNo" Then

DataArray(0, 1) = VB.Left(FName, Len(FName) - 4)

Else

DataArray(0, 1) = XLApp.cells(84, 2).Value 'Get EmpNo

End If

For mth = 1 To 12

For cl = 1 To 55

'Read the existing data into the array

DataArray(mth, cl) = XLApp.cells(xlr + mth,
cl).Value

Next cl

Next mth

'Array now has all the data from the old sheet - close the
sheet

XLApp.workbooks(FName).Close(savechanges:=False) 'close but
do not save

For mth = 1 To 12

'Read the data back from the array - Up to col 36 they
are the same, then

'the new column appears at the 37th column and then the
data is the same to the end.

Select Case mth

Case 1 To 5

rs.AddNew()

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
'Employee Number

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.

If mth > LastM Then

rs.Fields("Year").Value = Year(Now) - 1

Else

rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")

End If

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)

... Lots more fields as per the above ...

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("BusMixPCInvNumShade").Value =
DataArray(mth, 55)

rs.Update()

Case 6 To 12 'beyond m6 data is screwed from column
36, data is in 1 to right of where it should be ie 37.

rs.AddNew()

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("EmpNo").Value = Val(DataArray(0, 1))
'Employee Number

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(0, 0). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("PointsScoreAtThisMonth").Value =
DataArray(0, 0) 'Points score.

If mth > LastM Then

rs.Fields("Year").Value = Year(Now) - 1

Else

rs.Fields("Year").Value = VB6.Format(Now,
"YYYY")

End If

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 1). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("TextMonthNumber").Value =
DataArray(mth, 1)

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 2). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("RollMonthNumber").Value =
DataArray(mth, 2)

... Lots more fields as per the above ...

'UPGRADE_WARNING: Couldn't resolve default
property of object DataArray(mth, 55). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

rs.Fields("BusMixPCInvNum").Value =
DataArray(mth, 55)

rs.Fields("BusMixPCInvNumShade").Value = 0 'Just
have to hardcode a value of no shade as there is no data on sheets.

rs.Update()

End Select

Next mth

AfterError:

'Clear the array ready for next loop (if any).

For mth = 1 To 12

For cl = 1 To 55

'Clear the array

'UPGRADE_WARNING: Couldn't resolve default property
of object DataArray(mth, cl). Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

DataArray(mth, cl) = 0

Next cl

Next mth

'do a dir for the next file name or exit if FName=""

'UPGRADE_WARNING: Dir has a new behavior. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1041"'

FName = Dir()

Loop Until FName = "" 'Move on to next sheet.

'Close recordset

rs.Close()

'Write closing log entry

PrintLine(1, " and finished file writes at " & Now) 'Finishes a
line started near top of loop

PrintLine(1,
"================================================= ====================")

FileClose(1) 'close log file!

'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.screenupdating. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

XLApp.screenupdating = True 'Turn on screen updating

'UPGRADE_WARNING: Couldn't resolve default property of object
XLApp.quit. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

XLApp.quit() 'Quit excel

End If

ConvertRoutine = True

ConvertRoutine_End:

Exit Function

ConvertRoutine_Err:

Select Case Err.Number

Case 13 'Type Mismatch

Resume Next

Case 94 'Invalid Use of Null

Resume Next

Case 3021 'No record found

Resume ConvertRoutine_End

Case 3022 'Duplicate record

Resume ConvertRoutine_End

Case -2147217887 'Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.

rs.CancelUpdate() 'Will occurr if dataarray(x,y) is empty
due to sheet being empty?? Found one so have put in this patch to bypass it.

Resume AfterError

Resume 'debugging

Case Else

Me.Cursor = System.Windows.Forms.Cursors.Default

Call ProgErrorHandler("ConvertRoutine in
frmConvertT7ToData", False)

Resume ConvertRoutine_End

Resume 'Debugging

End Select

================================================== ===

I have retained the "On error Goto" because using Try, Catch, Finally I
can't find out how to trap a specific error like the -2147217887 Multiple
Step OLE error the help on this is "pants".

Siv

"Austin Ehlers" <liberal*number_eight*@ku.edu> wrote in message
news:f079k0599a8638b8t9of97g2k8q3j5lvjm@4ax.com...[color=blue]
> On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
> wrote:
>[color=green]
>>Hi,
>>
>>As part of an evaluation of a small utility that I wrote that converts
>>some
>>data held in a large number of Excel spreadsheets into SQL Server, I
>>decided
>>to convert the utility to VB .NET and run both versions both in the IDE
>>and
>>as standalone compiled exe and see what the difference in time to complete
>>was.
>>
>>The utility has to read 3842 single page Excel Sheets that contain a block
>>of data 12 rows deep and 55 columns across, this is then transferred into
>>an
>>SQL Server table with the same number of columns.
>>
>>I thought VB .NET might be slower, but I was surprised by how much.[/color]
>
> Make sure you have Option Explicit On and Option Strict set. Watch
> out for string concatenation (use System.Text.StringBuilder instead).
> Watch out for legacy VB methods, classes, etc., and use .NET versions
> instead.
>[color=green]
>>I would like to release the findings to this community for comment, but I
>>remembered something in the EULA for .NET that says you can't release
>>benchmarks. Now I am not in the business of falling out with anyone and I
>>don't want to get into trouble with MS (particuarly as I make my living on
>>the back of Microsoft's products). What I would like to release is just a
>>table showing the start and end times for each run of my program and the
>>calculated elapsed times bothe for VB6 and VB .NET.
>>
>>If anyone from MS is looking in I would be pleased to know if you would be
>>unhappy about me releasing my findings??
>>
>>Siv
>>[/color]
>
> I ANAL, but you should be fine; you're trying to figure out what's
> wrong with your code. It's to prevent articles like "C# vs. Java",
> etc.
>
> Austin
>[/color]

In addition to other suggestions, here's what I would suggest: Instead of looping and setting each array element to 0 here, you should just erase the entire array and redim it at the begining of each loop (see code below). Doing this is just wasteful - 55*12 = 660 iterations - just for each worksheet. However, that won't account for the speed difference between VB6 and VB.NET. This change should equally (more or less) speed up both versions.

hope that helps..
Imran.
AfterError:
'Clear the array ready for next loop (if any).

For mth = 1 To 12

For cl = 1 To 55

'Clear the array

'UPGRADE_WARNING: Couldn't resolve default property of object DataArray(mth, cl). Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'

DataArray(mth, cl) = 0

Next cl

Next mth

'Instead of looping above, use this:

Erase DataArray

Siv,

I saw you code, that is not VBNet, that is converted VB6 using the dotNet
namespace, the Microsoft VisualBasic namespace and the VBNet compatible
namespace.

When you want a true comparasing, you should write a program in the VBNet
way, which only contains the dotNet and the Microsoft VisualBasic namespace.

That means at least using ADONET instead of ADODB which is used in the
interop way
You can use ADONET to get the data and probably as well to set the Excel
sheets in a dataset.

Also you can you can use early binding, what will be archieved when you set
the Option Strict to On because with that you cannot do late binding. Late
binding means that the program search on runtime moment for the most right
object to use, while with early binding that is already told.

That does not mean that your program will not run, however not with much
advantages over a real VBNet program (maybe even with disadvantages)

I hope this helps?

Cor

"Siv" >[color=blue]
> As part of an evaluation of a small utility that I wrote that converts[/color]
some[color=blue]
> data held in a large number of Excel spreadsheets into SQL Server, I[/color]
decided[color=blue]
> to convert the utility to VB .NET and run both versions both in the IDE[/color]
and[color=blue]
> as standalone compiled exe and see what the difference in time to complete
> was.
>
> The utility has to read 3842 single page Excel Sheets that contain a block
> of data 12 rows deep and 55 columns across, this is then transferred into[/color]
an[color=blue]
> SQL Server table with the same number of columns.
>
> I thought VB .NET might be slower, but I was surprised by how much.
>
> I would like to release the findings to this community for comment, but I
> remembered something in the EULA for .NET that says you can't release
> benchmarks. Now I am not in the business of falling out with anyone and I
> don't want to get into trouble with MS (particuarly as I make my living on
> the back of Microsoft's products). What I would like to release is just a
> table showing the start and end times for each run of my program and the
> calculated elapsed times bothe for VB6 and VB .NET.
>
> If anyone from MS is looking in I would be pleased to know if you would be
> unhappy about me releasing my findings??
>
> Siv
>
>[/color]

* "Vadim Rapp" <vr@myrealbox.nospam.com> scripsit:[color=blue]
> S> I would like to release the findings to this community for comment, but
> S> I remembered something in the EULA for .NET that says you can't release
> S> benchmarks.
>
> ...of the .net framework, which is not the case. You are releasing the
> benchmark of your applications. A benchmark of the whole framework
> would be quite a project.[/color]

Mhm... But the code you benchmark includes implementations provided by
MSFT (or at least the CLR)...

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

LOL!!!!!

Shrink wrapping gone mad! What a bunch of dominating bastards! I've dissed
the speed of things in here many times, but you have to remember something,
it's not the Framework or OS components that your benchmarking, it's your
software! Who is to say that your code is 100%, and regardless of that;
there are always ways to improve things.

Remember, *your* software benchmarks, not the framework or os.

Nick.

"Siv" <gs@remove.sivill.com> wrote in message
news:OUyZVUPmEHA.416@TK2MSFTNGP10.phx.gbl...[color=blue]
> Herfried,
>
> I had a look on Google and found the following link to the EULA for the
> .NET framework 1.1 Redistributable, which seems to contain the line that I
> remember seeing pop up somewhere:
>
> a.. You may not disclose the results of any benchmark test of the .NET
> Framework component of the OS Components to any third party without
> Microsoft's prior written approval.
>
> I tried hunting through the .NET IDE help system looking for "Licence"
> "EULA" etc but couldn't find anything.
>
> I suppose the question is, does releasing the timings I have done with my
> applications constitute a "benchmark", I don't think they do, as I am sure
> someone will point out that my program should be re-written from scratch
> using VB.NET rather than by converting a VB6 app.
>
> My reason for posting it would be to see if anyone had any ideas on how
> the application could be improved to get better speed under .NET (as you
> know from my previous posts I am converting from VB6 to VB.NET and am
> looking to _learn_ rather than take a swipe at Microsoft and I expect to
> get some useful information from this group about squeezing best
> performance from .NET).
>
> Siv
>
>
> "Herfried K. Wagner [MVP]" <hirf-spam-me-here@gmx.at> wrote in message
> news:O8jv2LPmEHA.3712@TK2MSFTNGP15.phx.gbl...[color=green]
>>* "Siv" <gs@remove.sivill.com> scripsit:[color=darkred]
>>> I would like to release the findings to this community for comment, but
>>> I
>>> remembered something in the EULA for .NET that says you can't release
>>> benchmarks.[/color]
>>
>> Re-read the EULA. I remember that benchmarks were forbidden for the
>> beta version, but this is maybe not the case for the release versions.
>>
>> --
>> M S Herfried K. Wagner
>> M V P <URL:http://dotnet.mvps.org/>
>> V B <URL:http://dotnet.mvps.org/dotnet/faqs/>[/color]
>
>[/color]

Your right, it makes very little difference to speed, just because you write
the name of an object on 2 different lines does not mean that there are 2
objects in memory, its still the same one being referenced.

Nick.

"Siv" <gs@remove.sivill.com> wrote in message
news:ewS%23LgSmEHA.3452@TK2MSFTNGP15.phx.gbl...[color=blue]
> Are you sure, or do you mean the code will be more concise??
> Siv
>
> "Howard Kaikow" <kaikow@standards.com> wrote in message
> news:ebUfxYSmEHA.3156@TK2MSFTNGP12.phx.gbl...[color=green]
>> The code will run a lot faster if you use With ... End With to reduce
>> the
>> number of object references.
>>
>> --
>> http://www.standards.com/; See Howard Kaikow's web site.
>> "Siv" <gs@remove.sivill.com> wrote in message
>> news:%23Ey6QmRmEHA.896@TK2MSFTNGP12.phx.gbl...
>> Thanks for the advice, I do have Option Explicit on, but not Option
>> Strict.
>> The conversion routine picked up a few "I can't figure ot the default
>> property" errors, but I can't see a way round those as I am using OLE
>> Automation into Excel to open the sheets. The code is as follows:
>>
>> ================================================== ==========================
>> =======================
>> Dim FName, SheetName As String
>>
>> Dim DataArray(12, 60) As Object
>>
>> Dim mth, cl As Short
>>
>> Dim xlc, xlr, n As Short
>>
>> Dim rs As ADODB.Recordset
>>
>> Dim Cn As ADODB.Connection
>>
>> Dim strSQL, strCnn, MSg As String
>>
>> Dim XLApp As Object
>>
>> Dim LastM As Short
>>
>>
>>
>> On Error GoTo ConvertRoutine_Err
>>
>>
>>
>> ConvertRoutine = False 'Start off assuming fail and correct this
>> if
>> we do succeed.
>>
>>
>>
>> 'Work out the lastM var from LastMonth
>>
>> LastM = Val(LastMonth)
>>
>>
>>
>> FileOpen(1, "C:\T7ConversionLog.txt", OpenMode.Append)
>>
>>
>>
>> PrintLine(1, "TACSY 7 Conversion Log - " & Now)
>>
>> PrintLine(1, "===========================================")
>>
>> PrintLine(1, " ")
>>
>>
>>
>>
>>
>> 'Look for the files in the source directory by
>>
>> FName = Dir(tp & "*.xls") 'tp is the var holding the path to the
>> spreadsheets being processed.
>>
>> If FName = "" Then
>>
>> 'No xls files in this directory
>>
>> MSg = "WARNING: No Excel files found in:" & NL & NL
>>
>> MSg = MSg & tp & NL & NL
>>
>> MSg = MSg & "Click OK to exit the Conversion Routine, "
>>
>> MSg = MSg & "so that you can reselect the source folder."
>>
>> MsgBox(MSg, MsgBoxStyle.Information, H)
>>
>> PrintLine(1, "No Files found in selected folder ( " & tp & " )
>> to convert!")
>>
>> PrintLine(1, "Aborting Conversion Run at " & Now)
>>
>> FileClose(1)
>>
>> Exit Function
>>
>> Else
>>
>> 'Found some XL files.
>>
>> 'So Get Excel fired up
>>
>> XLApp = CreateObject("Excel.Application")
>>
>> XLApp.screenupdating = False
>>
>>
>>
>> 'Get the Tacsy7Data tble open ready to add records
>>
>> Cn = New ADODB.Connection
>>
>> Cn.ConnectionString = "Driver={SQL
>> Server};Server=SERVER;UID=;PWD=;DATABASE=TACSY"
>>
>> Cn.Open()
>>
>>
>>
>> rs = New ADODB.Recordset
>>
>> rs.Open("SELECT * FROM Tacsy7Data", Cn,
>> ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic,
>> ADODB.CommandTypeEnum.adCmdText)
>>
>>
>>
>> Do
>>
>> XLApp.workbooks.Open(tp & FName)
>>
>>
>>
>> lblReadout.Text = "Processing " & FName & "."
>>
>> PrintLine(1, "Started converting " & tp & FName & " at " &
>> Now)
>>
>> System.Windows.Forms.Application.DoEvents() 'Let system in
>> for a tick + allow readout refresh.
>>
>> xlr = 64 'Row start, 1 less than desired so that the mth
>> can
>> be added to it to get the correct row.
>>
>>
>>
>> DataArray(0, 0) = XLApp.cells(50, 4).Value 'Get the points
>> score
>>
>>
>>
>> If XLApp.cells(84, 2).Value = "EmpNo" Then
>>
>> DataArray(0, 1) = VB.Left(FName, Len(FName) - 4)
>>
>> Else
>>
>> DataArray(0, 1) = XLApp.cells(84, 2).Value 'Get EmpNo
>>
>> End If
>>
>>
>>
>> For mth = 1 To 12
>>
>> For cl = 1 To 55
>>
>> 'Read the existing data into the array
>>
>> DataArray(mth, cl) = XLApp.cells(xlr + mth,
>> cl).Value
>>
>> Next cl
>>
>> Next mth
>>
>>
>>
>> 'Array now has all the data from the old sheet - close
>> the
>> sheet
>>
>> XLApp.workbooks(FName).Close(savechanges:=False) 'close
>> but
>> do not save
>>
>>
>>
>>
>>
>>
>>
>> For mth = 1 To 12
>>
>> 'Read the data back from the array - Up to col 36 they
>> are the same, then
>>
>> 'the new column appears at the 37th column and then
>> the
>> data is the same to the end.
>>
>> Select Case mth
>>
>> Case 1 To 5
>>
>> rs.AddNew()
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property of object DataArray(). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> rs.Fields("EmpNo").Value = Val(DataArray(0,
>> 1))
>> 'Employee Number
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property of object DataArray(0, 0). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> rs.Fields("PointsScoreAtThisMonth").Value =
>> DataArray(0, 0) 'Points score.
>>
>> If mth > LastM Then
>>
>> rs.Fields("Year").Value = Year(Now) - 1
>>
>> Else
>>
>> rs.Fields("Year").Value = VB6.Format(Now,
>> "YYYY")
>>
>> End If
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property of object DataArray(mth, 1). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> rs.Fields("TextMonthNumber").Value =
>> DataArray(mth, 1)
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property of object DataArray(mth, 2). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> rs.Fields("RollMonthNumber").Value =
>> DataArray(mth, 2)
>>
>>
>>
>> ... Lots more fields as per the above ...
>>
>>
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property of object DataArray(mth, 55). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> rs.Fields("BusMixPCInvNumShade").Value =
>> DataArray(mth, 55)
>>
>> rs.Update()
>>
>>
>>
>> Case 6 To 12 'beyond m6 data is screwed from
>> column
>> 36, data is in 1 to right of where it should be ie 37.
>>
>> rs.AddNew()
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property of object DataArray(). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> rs.Fields("EmpNo").Value = Val(DataArray(0,
>> 1))
>> 'Employee Number
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property of object DataArray(0, 0). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> rs.Fields("PointsScoreAtThisMonth").Value =
>> DataArray(0, 0) 'Points score.
>>
>> If mth > LastM Then
>>
>> rs.Fields("Year").Value = Year(Now) - 1
>>
>> Else
>>
>> rs.Fields("Year").Value = VB6.Format(Now,
>> "YYYY")
>>
>> End If
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property of object DataArray(mth, 1). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> rs.Fields("TextMonthNumber").Value =
>> DataArray(mth, 1)
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property of object DataArray(mth, 2). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> rs.Fields("RollMonthNumber").Value =
>> DataArray(mth, 2)
>>
>>
>>
>> ... Lots more fields as per the above ...
>>
>>
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property of object DataArray(mth, 55). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> rs.Fields("BusMixPCInvNum").Value =
>> DataArray(mth, 55)
>>
>> rs.Fields("BusMixPCInvNumShade").Value = 0
>> 'Just
>> have to hardcode a value of no shade as there is no data on sheets.
>>
>> rs.Update()
>>
>> End Select
>>
>> Next mth
>>
>>
>>
>> AfterError:
>>
>> 'Clear the array ready for next loop (if any).
>>
>> For mth = 1 To 12
>>
>> For cl = 1 To 55
>>
>> 'Clear the array
>>
>> 'UPGRADE_WARNING: Couldn't resolve default
>> property
>> of object DataArray(mth, cl). Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> DataArray(mth, cl) = 0
>>
>> Next cl
>>
>> Next mth
>>
>>
>>
>>
>>
>>
>>
>> 'do a dir for the next file name or exit if FName=""
>>
>> 'UPGRADE_WARNING: Dir has a new behavior. Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1041"'
>>
>> FName = Dir()
>>
>>
>>
>> Loop Until FName = "" 'Move on to next sheet.
>>
>>
>>
>> 'Close recordset
>>
>> rs.Close()
>>
>>
>>
>> 'Write closing log entry
>>
>> PrintLine(1, " and finished file writes at " & Now) 'Finishes
>> a
>> line started near top of loop
>>
>> PrintLine(1,
>> "================================================= ====================")
>>
>>
>>
>> FileClose(1) 'close log file!
>>
>>
>>
>> 'UPGRADE_WARNING: Couldn't resolve default property of object
>> XLApp.screenupdating. Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> XLApp.screenupdating = True 'Turn on screen updating
>>
>> 'UPGRADE_WARNING: Couldn't resolve default property of object
>> XLApp.quit. Click for more:
>> 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
>>
>> XLApp.quit() 'Quit excel
>>
>>
>>
>> End If
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> ConvertRoutine = True
>>
>>
>>
>> ConvertRoutine_End:
>>
>> Exit Function
>>
>>
>>
>>
>>
>> ConvertRoutine_Err:
>>
>> Select Case Err.Number
>>
>> Case 13 'Type Mismatch
>>
>> Resume Next
>>
>> Case 94 'Invalid Use of Null
>>
>> Resume Next
>>
>> Case 3021 'No record found
>>
>> Resume ConvertRoutine_End
>>
>> Case 3022 'Duplicate record
>>
>> Resume ConvertRoutine_End
>>
>> Case -2147217887 'Multiple-step OLE DB operation generated
>> errors. Check each OLE DB status value, if available. No work was done.
>>
>> rs.CancelUpdate() 'Will occurr if dataarray(x,y) is empty
>> due to sheet being empty?? Found one so have put in this patch to bypass
>> it.
>>
>> Resume AfterError
>>
>> Resume 'debugging
>>
>> Case Else
>>
>> Me.Cursor = System.Windows.Forms.Cursors.Default
>>
>> Call ProgErrorHandler("ConvertRoutine in
>> frmConvertT7ToData", False)
>>
>> Resume ConvertRoutine_End
>>
>> Resume 'Debugging
>>
>> End Select
>>
>> ================================================== ===
>>
>> I have retained the "On error Goto" because using Try, Catch, Finally I
>> can't find out how to trap a specific error like the -2147217887 Multiple
>> Step OLE error the help on this is "pants".
>>
>> Siv
>>
>> "Austin Ehlers" <liberal*number_eight*@ku.edu> wrote in message
>> news:f079k0599a8638b8t9of97g2k8q3j5lvjm@4ax.com...[color=darkred]
>>> On Sun, 12 Sep 2004 18:45:48 +0100, "Siv" <gs@remove.sivill.com>
>>> wrote:
>>>
>>>>Hi,
>>>>
>>>>As part of an evaluation of a small utility that I wrote that converts[/color]
>> some[color=darkred]
>>>>data held in a large number of Excel spreadsheets into SQL Server, I[/color]
>> decided[color=darkred]
>>>>to convert the utility to VB .NET and run both versions both in the IDE[/color]
>> and[color=darkred]
>>>>as standalone compiled exe and see what the difference in time to
>>>>complete
>>>>was.
>>>>
>>>>The utility has to read 3842 single page Excel Sheets that contain a
>>>>block
>>>>of data 12 rows deep and 55 columns across, this is then transferred
>>>>into[/color]
>> an[color=darkred]
>>>>SQL Server table with the same number of columns.
>>>>
>>>>I thought VB .NET might be slower, but I was surprised by how much.
>>>
>>> Make sure you have Option Explicit On and Option Strict set. Watch
>>> out for string concatenation (use System.Text.StringBuilder instead).
>>> Watch out for legacy VB methods, classes, etc., and use .NET versions
>>> instead.
>>>
>>>>I would like to release the findings to this community for comment, but
>>>>I
>>>>remembered something in the EULA for .NET that says you can't release
>>>>benchmarks. Now I am not in the business of falling out with anyone and
>>>>I
>>>>don't want to get into trouble with MS (particuarly as I make my living
>>>>on
>>>>the back of Microsoft's products). What I would like to release is just
>>>>a
>>>>table showing the start and end times for each run of my program and the
>>>>calculated elapsed times bothe for VB6 and VB .NET.
>>>>
>>>>If anyone from MS is looking in I would be pleased to know if you would
>>>>be
>>>>unhappy about me releasing my findings??
>>>>
>>>>Siv
>>>>
>>>
>>> I ANAL, but you should be fine; you're trying to figure out what's
>>> wrong with your code. It's to prevent articles like "C# vs. Java",
>>> etc.
>>>
>>> Austin
>>>[/color]
>>
>>[/color]
>
>[/color]

> I ANAL, but you should be fine; you're trying to figure out what's[color=blue]
> wrong with your code. It's to prevent articles like "C# vs. Java",
> etc.[/color]

LOL, please inform me what you mean by "ANAL" as I think I have the wrong
idea :-\

Nick.

Hello Siv:
You wrote in conference microsoft.public.dotnet.languages.vb on Mon, 13 Sep
2004 01:04:50 +0100:

S> Details as follows:
....

it's very hard to judge, having the code you provided. The code does too
many various things. If you want to narrow down the performance difference,
you have to split your code by many parts, each repetitively performing some
atomic function, then measure each one.

That's why Microsoft is trying to limit publishing the benchmarks. Actually,
you should ask your question not in the newsgroup, but rather send it to
Microsfot media contacts. Most likely, they would try to evaluate what
audience would read your results, and if large, they would probably assign
someone who would eventually help you either to improve the performance, or
at least to get more accurate and representing results.

Vadim

Vadim,

Thanks for your comments. Sorry I didn't respond quickly have been "up to
my eyeballs in it!" and haven't looked in on this thread for a while.

You mention "Microsoft Media Contacts" what or who is that?? and how do I
contact them?

Siv

"Vadim Rapp" <vr@myrealbox.nospam.com> wrote in message
news:esB5P72mEHA.3472@TK2MSFTNGP09.phx.gbl...[color=blue]
> Hello Siv:
> You wrote in conference microsoft.public.dotnet.languages.vb on Mon, 13
> Sep 2004 01:04:50 +0100:
>
> S> Details as follows:
> ...
>
> it's very hard to judge, having the code you provided. The code does too
> many various things. If you want to narrow down the performance
> difference, you have to split your code by many parts, each repetitively
> performing some atomic function, then measure each one.
>
> That's why Microsoft is trying to limit publishing the benchmarks.
> Actually, you should ask your question not in the newsgroup, but rather
> send it to Microsfot media contacts. Most likely, they would try to
> evaluate what audience would read your results, and if large, they would
> probably assign someone who would eventually help you either to improve
> the performance, or at least to get more accurate and representing
> results.
>
>
> Vadim[/color]

Hello Siv:
You wrote in conference microsoft.public.dotnet.languages.vb on Fri, 17 Sep
2004 11:14:22 +0100:

S> You mention "Microsoft Media Contacts" what or who is that?? and how do
S> I contact them?

I have no such experience, but if you click "for journalists" at
www.microsoft.com, then PR contacts, then you'll find "rapid response team".

Vadim

Cheers Vladim,
I'll try that.
Siv

"Vadim Rapp" <vr@myrealbox.nospam.com> wrote in message
news:uj8TM$KnEHA.3480@TK2MSFTNGP09.phx.gbl...[color=blue]
> Hello Siv:
> You wrote in conference microsoft.public.dotnet.languages.vb on Fri, 17
> Sep 2004 11:14:22 +0100:
>
> S> You mention "Microsoft Media Contacts" what or who is that?? and how do
> S> I contact them?
>
> I have no such experience, but if you click "for journalists" at
> www.microsoft.com, then PR contacts, then you'll find "rapid response
> team".
>
> Vadim
>[/color]

CreateObject creates an instance of Excel. The easiest workaround is
available even in VB6 which i think you did not try in VB6 itself. If you
are using ADODB, Create two different connections. One with your SQL server
and the other with the excel file. You can use the Jet OLEDB provider to
conect to EXCEL files. The only change will be that the SQL string will be
like

"select * from [Sheet1]"

of course with square brackets for sheet name which is considered as table.
In this way your whole excel data is in your recordset. Doing your
processing between recordsets which has Cachesize property set with an
optimised value something like 200 can make extraordinary performance
difference.

Now in Dot NET you can still open Excel files as Dataset or Datareader as
required. You have to find methods or providers and how to do this yourself.
The moral of the story is not to use CreateObject or GetObject. To get the
connection string of how to connect to excel files or text files using ADODB
or DotNET in multiple languages, check www.able-consulting.com and find
connection strings page.

I have done this with Excel files with 125 columns and over 80,000 rows with
multiple sheets and processed against AS400 tables [PF] using VB6's ADODB
connections. [ADODB connections are independent in same project for both
Excel and AS400]

Good luck
Raja Venkatesh
rv_ooty@hotmail.com

"Siv" wrote:
[color=blue]
> Hi,
>
> As part of an evaluation of a small utility that I wrote that converts some
> data held in a large number of Excel spreadsheets into SQL Server, I decided
> to convert the utility to VB .NET and run both versions both in the IDE and
> as standalone compiled exe and see what the difference in time to complete
> was.
>
> The utility has to read 3842 single page Excel Sheets that contain a block
> of data 12 rows deep and 55 columns across, this is then transferred into an
> SQL Server table with the same number of columns.
>
> I thought VB .NET might be slower, but I was surprised by how much.
>
> I would like to release the findings to this community for comment, but I
> remembered something in the EULA for .NET that says you can't release
> benchmarks. Now I am not in the business of falling out with anyone and I
> don't want to get into trouble with MS (particuarly as I make my living on
> the back of Microsoft's products). What I would like to release is just a
> table showing the start and end times for each run of my program and the
> calculated elapsed times bothe for VB6 and VB .NET.
>
> If anyone from MS is looking in I would be pleased to know if you would be
> unhappy about me releasing my findings??
>
> Siv
>
>
>[/color]

VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

re: VB versus VB.NET timings!!

/bytes/development

/bytes/it

/bytes/about