Write array from vb6 dll to a specified worksheet in Excel
 

Hi,

I have the following code:

Excel VBA Sub:

Sub CallVBDLL

VBDLL (rows,cols)

End Sub

In VB6 DLL I have the following:


Public Sub VBDLL (rows,cols)

Re Dim BugArray(rows,cols) as string
Go through rows and cols, classify and add to my BigArray(rows,cols)

*** I would like a method of writing the BigArray to a new worksheet
in the workbook without looping through array **

End Sub

Any help much appreciated.

Thank you in advance

Meldrum

Write array from vb6 dll to a specified worksheet in Excel
 

You will have to work out your Workbook and Worksheet references etc.,
but basically to write an array to a range without looping you would do:

Dim lRows As Long
Dim lCols As Long

lRows = (UBound(BigArray) - LBound(BigArray)) + 1
lCols = (UBound(BigArray, 2) - LBound(BigArray, 2)) + 1

Range(Cells(1), Cells(lRows, lCols)) = BigArray


RBS


wrote in message
...
Hi,

I have the following code:

Excel VBA Sub:

Sub CallVBDLL

VBDLL (rows,cols)

End Sub

In VB6 DLL I have the following:


Public Sub VBDLL (rows,cols)

Re Dim BugArray(rows,cols) as string
Go through rows and cols, classify and add to my BigArray(rows,cols)

*** I would like a method of writing the BigArray to a new worksheet
in the workbook without looping through array **

End Sub

Any help much appreciated.

Thank you in advance

Meldrum

Write array from vb6 dll to a specified worksheet in Excel
 

On 18 Nov, 11:15, "RB Smissaert"
wrote:
You will have to work out your Workbook and Worksheet references etc.,
but basically to write an array to a range without looping you would do:

Dim lRows As Long
Dim lCols As Long

lRows = (UBound(BigArray) - LBound(BigArray)) + 1
lCols = (UBound(BigArray, 2) - LBound(BigArray, 2)) + 1

Range(Cells(1), Cells(lRows, lCols)) = BigArray

RBS

wrote in message

...



Hi,

I have the following code:

Excel VBA Sub:

Sub CallVBDLL

VBDLL (rows,cols)

End Sub

In VB6 DLL I have the following:

Public Sub VBDLL (rows,cols)

Re Dim BugArray(rows,cols) as string
Go through rows and cols, classify and add to my BigArray(rows,cols)

*** I would like a method of writing the BigArray to a new worksheet
in the workbook without looping through array **

End Sub

Any help much appreciated.

Thank you in advance

Meldrum- Hide quoted text -

- Show quoted text -

Hi RBS

Thank you for your reply - I tried your code but got no joy, I think I
might not be referencing it correct. In my DLL I add a new sheet to
the active workbook called ReportSheet. I then loop through a sheet
called TestSheet and classify each cell.

I am able to go through each item in the array and write it
individually to the ReportSheet and that works fine.

The problem is when I want to write the entire array in one go to a
range it doesn't work. I've set Option Base 1 as well.

In my code I've got:

Option Base 1

Set ReportSheet = mxlApp.ActiveWorkbook.sheets.add --- this should
give me correct referencing as I can write array by looping.

ReportSheet.Range(Cells(1,1),Cells(xRow,yCol)).val ue = BigArray

Does this look okay?

Any help much appreciated.

Meldrum

Write array from vb6 dll to a specified worksheet in Excel
 

ReportSheet.Range(Cells(1,1),Cells(xRow,yCol)).val ue = BigArray

Try this instead:

With ReportSheet
.Range(.Cells(1,1),.Cells(xRow,yCol)).value = BigArray
End With

Note the dots before Range and Cells.


RBS


wrote in message
...
On 18 Nov, 11:15, "RB Smissaert"
wrote:
You will have to work out your Workbook and Worksheet references etc.,
but basically to write an array to a range without looping you would do:

Dim lRows As Long
Dim lCols As Long

lRows = (UBound(BigArray) - LBound(BigArray)) + 1
lCols = (UBound(BigArray, 2) - LBound(BigArray, 2)) + 1

Range(Cells(1), Cells(lRows, lCols)) = BigArray

RBS

wrote in message

...



Hi,

I have the following code:

Excel VBA Sub:

Sub CallVBDLL

VBDLL (rows,cols)

End Sub

In VB6 DLL I have the following:

Public Sub VBDLL (rows,cols)

Re Dim BugArray(rows,cols) as string
Go through rows and cols, classify and add to my BigArray(rows,cols)

*** I would like a method of writing the BigArray to a new worksheet
in the workbook without looping through array **

End Sub

Any help much appreciated.

Thank you in advance

Meldrum- Hide quoted text -

- Show quoted text -

Hi RBS

Thank you for your reply - I tried your code but got no joy, I think I
might not be referencing it correct. In my DLL I add a new sheet to
the active workbook called ReportSheet. I then loop through a sheet
called TestSheet and classify each cell.

I am able to go through each item in the array and write it
individually to the ReportSheet and that works fine.

The problem is when I want to write the entire array in one go to a
range it doesn't work. I've set Option Base 1 as well.

In my code I've got:

Option Base 1

Set ReportSheet = mxlApp.ActiveWorkbook.sheets.add --- this should
give me correct referencing as I can write array by looping.

ReportSheet.Range(Cells(1,1),Cells(xRow,yCol)).val ue = BigArray

Does this look okay?

Any help much appreciated.

Meldrum

Write array from vb6 dll to a specified worksheet in Excel
 

On 18 Nov, 11:59, "RB Smissaert"
wrote:
ReportSheet.Range(Cells(1,1),Cells(xRow,yCol)).val ue = BigArray

Try this instead:

With ReportSheet
.Range(.Cells(1,1),.Cells(xRow,yCol)).value = BigArray
End With

Note the dots before Range and Cells.

RBS

wrote in message

...



On 18 Nov, 11:15, "RB Smissaert"
wrote:
You will have to work out your Workbook and Worksheet references etc.,
but basically to write an array to a range without looping you would do:

Dim lRows As Long
Dim lCols As Long

lRows = (UBound(BigArray) - LBound(BigArray)) + 1
lCols = (UBound(BigArray, 2) - LBound(BigArray, 2)) + 1

Range(Cells(1), Cells(lRows, lCols)) = BigArray

RBS

wrote in message

...

Hi,

I have the following code:

Excel VBA Sub:

Sub CallVBDLL

VBDLL (rows,cols)

End Sub

In VB6 DLL I have the following:

Public Sub VBDLL (rows,cols)

Re Dim BugArray(rows,cols) as string
Go through rows and cols, classify and add to my BigArray(rows,cols)

*** I would like a method of writing the BigArray to a new worksheet
in the workbook without looping through array **

End Sub

Any help much appreciated.

Thank you in advance

Meldrum- Hide quoted text -

- Show quoted text -

Hi RBS

Thank you for your reply - I tried your code but got no joy, I think I
might not be referencing it correct. In my DLL I add a new sheet to
the active workbook called ReportSheet. I then loop through a sheet
called TestSheet and classify each cell.

I am able to go through each item in the array and write it
individually to the ReportSheet and that works fine.

The problem is when I want to write the entire array in one go to a
range it doesn't work. I've set Option Base 1 as well.

In my code I've got:

Option Base 1

Set ReportSheet = mxlApp.ActiveWorkbook.sheets.add --- this should
give me correct referencing as I can write array by looping.

ReportSheet.Range(Cells(1,1),Cells(xRow,yCol)).val ue = BigArray

Does this look okay?

Any help much appreciated.

Meldrum- Hide quoted text -

- Show quoted text -

RBS,

Genius..works like a dream.

Thank you.

Meldrum

Write array from vb6 dll to a specified worksheet in Excel
 

Just for variety, you can do something like the following in your VB6 code,
where Arr is the array to be dumped to the worksheet with an upper left
corner of the cell referenced by R.


R.Resize(UBound(Arr, 1) - LBound(Arr) + 1, UBound(Arr, 2) - LBound(Arr, 2) +
1) = Arr


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"RB Smissaert" wrote in message
...
ReportSheet.Range(Cells(1,1),Cells(xRow,yCol)).val ue = BigArray

Try this instead:

With ReportSheet
.Range(.Cells(1,1),.Cells(xRow,yCol)).value = BigArray
End With

Note the dots before Range and Cells.


RBS


wrote in message
...
On 18 Nov, 11:15, "RB Smissaert"
wrote:
You will have to work out your Workbook and Worksheet references etc.,
but basically to write an array to a range without looping you would do:

Dim lRows As Long
Dim lCols As Long

lRows = (UBound(BigArray) - LBound(BigArray)) + 1
lCols = (UBound(BigArray, 2) - LBound(BigArray, 2)) + 1

Range(Cells(1), Cells(lRows, lCols)) = BigArray

RBS

wrote in message

...



Hi,

I have the following code:

Excel VBA Sub:

Sub CallVBDLL

VBDLL (rows,cols)

End Sub

In VB6 DLL I have the following:

Public Sub VBDLL (rows,cols)

Re Dim BugArray(rows,cols) as string
Go through rows and cols, classify and add to my BigArray(rows,cols)

*** I would like a method of writing the BigArray to a new worksheet
in the workbook without looping through array **

End Sub

Any help much appreciated.

Thank you in advance

Meldrum- Hide quoted text -

- Show quoted text -

Hi RBS

Thank you for your reply - I tried your code but got no joy, I think I
might not be referencing it correct. In my DLL I add a new sheet to
the active workbook called ReportSheet. I then loop through a sheet
called TestSheet and classify each cell.

I am able to go through each item in the array and write it
individually to the ReportSheet and that works fine.

The problem is when I want to write the entire array in one go to a
range it doesn't work. I've set Option Base 1 as well.

In my code I've got:

Option Base 1

Set ReportSheet = mxlApp.ActiveWorkbook.sheets.add --- this should
give me correct referencing as I can write array by looping.

ReportSheet.Range(Cells(1,1),Cells(xRow,yCol)).val ue = BigArray

Does this look okay?

Any help much appreciated.

Meldrum