Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to write a number in words in excel worksheet? | Excel Worksheet Functions | |||
Write data inthe worksheet / array | Excel Programming | |||
Array write | Excel Programming | |||
How to write a marco in Excel to delete worksheet? | Excel Programming | |||
Write to Array from other closed Excel file | Excel Programming |