-
Jan 10th, 2017, 11:42 AM
#1
Thread Starter
Fanatic Member
Excel API Question - Populate spreadsheet with 2D array contents
Hello:
I an trying to simply place the contents of a 2D array into an excel spreadsheet, with the ability to handle null values.
Code:
Private Sub ConvertToExcel(rows As Integer, cols As Integer)
Dim currApp As Excel.Application = New Excel.Application
Dim curBooks As Excel.Workbooks = currApp.Workbooks
Dim curBook As Excel.Workbook = curBooks.Add
Dim curSheets As Excel.Sheets = curBook.Worksheets
Dim curSheet As Excel.Worksheet = curSheets(1)
curSheet.Activate()
Dim m As Integer = rows
Dim n As Integer = cols
Dim rngString As String = "A1:" + "D" + m.ToString
Dim rng As Excel.Range = curSheet.Range(rngString)
curSheet.Range(rng).Value = aryPartMaster
End Sub
This logic works, for displaying data. But I've heard populating one cell at a time in Excel is not the way.
Code:
For x As Integer = 0 To m
For y As Integer = 0 To n
If aryPartMaster(x, y) = Nothing Then
Debug.WriteLine("Nothing")
Else
Debug.WriteLine(aryPartMaster(x, y).ToString)
End If
Next
Next
Thanks in advance!
-
Jan 10th, 2017, 11:58 AM
#2
Re: Excel API Question - Populate spreadsheet with 2D array contents
sooo.. what's not working... setting the .Value of the range should be correct...
-tg
edit - Woah... I see the problem:
Dim rng As Excel.Range = curSheet.Range(rngString)
curSheet.Range(rng).Value = aryPartMaster
Once you have the Range object, you set the .Value of THAT object
it should be:
Dim rng As Excel.Range = curSheet.Range(rngString)
rng.Value = aryPartMaster
-tg
-
Jan 10th, 2017, 12:04 PM
#3
Thread Starter
Fanatic Member
Re: Excel API Question - Populate spreadsheet with 2D array contents
I changed the code. The code runs without error, but the spreadsheet is not populating with data. Also, what do I do about nulls, or will the logic cover this?
I must be missing something.
Thanks!
-
Jan 10th, 2017, 12:40 PM
#4
Re: Excel API Question - Populate spreadsheet with 2D array contents
hard to say... next thing I'd look at is if there is anythying in the array in the first place.
-tg
-
Jan 10th, 2017, 01:43 PM
#5
Thread Starter
Fanatic Member
Re: Excel API Question - Populate spreadsheet with 2D array contents
Originally Posted by techgnome
hard to say... next thing I'd look at is if there is anythying in the array in the first place.
-tg
Aside from some null data, the array is 4 columns by 204 rows, filled with data!
Code:
Private Sub ConvertToExcel(rows As Integer, cols As Integer)
Dim currApp As Excel.Application = New Excel.Application
Dim curBooks As Excel.Workbooks = currApp.Workbooks
Dim curBook As Excel.Workbook = curBooks.Add
Dim curSheets As Excel.Sheets = curBook.Worksheets
Dim curSheet As Excel.Worksheet = curSheets(1)
curSheet.Activate()
' m is the rows in the array, n is the columns
Dim m As Integer = rows
Dim n As Integer = cols
' This part of the code is a ritual that outputs the data in a single column.
For x As Integer = 0 To m
For y As Integer = 0 To n
If aryPartMaster(x, y) = Nothing Then
Debug.WriteLine("Nothing")
Else
Debug.WriteLine(aryPartMaster(x, y).ToString)
End If
Next
Next
' Export to Excel
' Define string value for range (ie: A1:D4") comumns first, rows second in Excel, vs rows first, columns second in an array
Dim rngString As String = "A1:" + Chr(65 + n).ToString + m.ToString
Debug.WriteLine("rngstring: " + rngString)
' Apply range and output
Dim rng As Excel.Range = curSheet.Range(rngString)
rng.Value = aryPartMaster
End Sub
Last edited by ssabc; Jan 10th, 2017 at 01:47 PM.
-
Jan 10th, 2017, 02:09 PM
#6
Re: Excel API Question - Populate spreadsheet with 2D array contents
What does this line output? Is it the proper range and size?
Debug.WriteLine("rngstring: " + rngString)
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Jan 10th, 2017, 02:13 PM
#7
Thread Starter
Fanatic Member
-
Jan 10th, 2017, 03:28 PM
#8
Re: Excel API Question - Populate spreadsheet with 2D array contents
You can disable smilies when composing your reply btw
Can you try it without the nulls in the array?
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Jan 10th, 2017, 04:00 PM
#9
Re: Excel API Question - Populate spreadsheet with 2D array contents
Also, do you have any other instances of Excel running? Maybe hidden from test running your app and visible in the task manager? ill all excel processes and start fresh.
What error handling do you have in the parent procedure?
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Jan 11th, 2017, 11:49 AM
#10
Thread Starter
Fanatic Member
Re: Excel API Question - Populate spreadsheet with 2D array contents
Originally Posted by RobDog888
Also, do you have any other instances of Excel running? Maybe hidden from test running your app and visible in the task manager? ill all excel processes and start fresh.
What error handling do you have in the parent procedure?
I have added values so that nothing is in the array is null. Null values now have a string value of "Nothing".
Is there a good example of how I can better control the Excel document? There is only one document open, but yes, I don't think it is being recognized.
Thanks!
-
Jan 11th, 2017, 12:45 PM
#11
Re: Excel API Question - Populate spreadsheet with 2D array contents
...I don't think it is being recognized.
What is the value of curSheet when you get to this line:
Code:
Dim rng As Excel.Range = curSheet.Range(rngString)
-
Jan 11th, 2017, 01:56 PM
#12
Thread Starter
Fanatic Member
Re: Excel API Question - Populate spreadsheet with 2D array contents
Originally Posted by vbfbryce
What is the value of curSheet when you get to this line:
Code:
Dim rng As Excel.Range = curSheet.Range(rngString)
Code:
MessageBox.Show("curSheet: " + curSheet.Name)
reads Sheet1. The problem is, this is not the name, because I renamed the sheet and saved the current, only open file. Any thoughts??
-
Jan 11th, 2017, 02:06 PM
#13
Re: Excel API Question - Populate spreadsheet with 2D array contents
Where do you rename that sheet? I'm not seeing that anywhere.
-
Jan 11th, 2017, 02:09 PM
#14
Thread Starter
Fanatic Member
Re: Excel API Question - Populate spreadsheet with 2D array contents
I did it manually in my open spreadsheet, just to see if the program would pick up the correctness of the name.
Maybe this is wrong?
Code:
Dim currApp As Excel.Application = New Excel.Application
Though this is not allowed:
Code:
Dim currApp As Excel.Application = Excel.Application
-
Jan 11th, 2017, 02:40 PM
#15
Re: Excel API Question - Populate spreadsheet with 2D array contents
I'm confused. I don't see where currApp is ever visible, so how did you change the name of a worksheet within that instance of Excel?
-
Jan 11th, 2017, 02:45 PM
#16
Thread Starter
Fanatic Member
Re: Excel API Question - Populate spreadsheet with 2D array contents
Originally Posted by vbfbryce
I'm confused. I don't see where currApp is ever visible, so how did you change the name of a worksheet within that instance of Excel?
I am really confused too, though I did find some working code here: http://www.vbforums.com/showthread.p...eet-From-Array
I simply changed it in my spreadsheet, not in code. I have an open blank spreadsheet I am hoping to populate. I highlighted Sheet1, and changed it to Sheet74. When it still displays as Sheet1, I have know idea what it is looking at, unless there is an invisible spreadsheet in memory.
-
Jan 11th, 2017, 02:51 PM
#17
Re: Excel API Question - Populate spreadsheet with 2D array contents
Originally Posted by ssabc
I did it manually in my open spreadsheet, just to see if the program would pick up the correctness of the name.
Maybe this is wrong?
Code:
Dim currApp As Excel.Application = New Excel.Application
Though this is not allowed:
Code:
Dim currApp As Excel.Application = Excel.Application
If you renamed, manually, "Sheet1", to something else then there is no real problem as you are accessing the sheet by its index position, not its name.
However you are referencing index position 2 not 1. So the array daa should be on the second sheet.
Dim curSheet As Excel.Worksheet = curSheets(1) ' index is zero based
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Jan 11th, 2017, 02:54 PM
#18
Re: Excel API Question - Populate spreadsheet with 2D array contents
Dim curSheet As Excel.Worksheet = curSheets(1) ' index is zero based
Unless this is different in different versions, I believe it is 1-based, not zero.
-
Jan 11th, 2017, 03:00 PM
#19
Re: Excel API Question - Populate spreadsheet with 2D array contents
Originally Posted by vbfbryce
Unless this is different in different versions, I believe it is 1-based, not zero.
Ah crap, yea in .net but it still seems like there is a break in teh association of sheet object with the actual instance assumed.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Jan 11th, 2017, 03:01 PM
#20
Re: Excel API Question - Populate spreadsheet with 2D array contents
Try this much, just to see if it works:
Code:
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim oXLWS As Excel.Worksheet
Dim oXLWS2 As Excel.Worksheet
Dim strPath As String
Dim j As Integer
Dim k As Integer
'strPath = "c:\yourPath\testDotNet.xlsx"
oXLApp = New Excel.Application
oXLApp.Visible = True
oXLBook = oXLApp.Workbooks.Add
oXLWS = oXLBook.Worksheets(1)
oXLWS.Name = "abc"
For j = 1 To 5
For k = 1 To 5
oXLWS.Cells(j, k) = "Number: " & j & "," & k
Next
Next
MsgBox(oXLWS.Name)
'cleanup...
Stop
oXLBook.Close(False)
oXLApp = Nothing
end Sub
-
Jan 11th, 2017, 03:05 PM
#21
Re: Excel API Question - Populate spreadsheet with 2D array contents
So wait a minute... where are you populating your array? aryPartMaster
According to your code its never set so it must be a module level variable being set somewhere else?
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Jan 11th, 2017, 03:15 PM
#22
Re: Excel API Question - Populate spreadsheet with 2D array contents
Exactly. That's why I suggested he write to the worksheet with those loops to isolate the problem.
-
Jan 11th, 2017, 03:21 PM
#23
Re: Excel API Question - Populate spreadsheet with 2D array contents
Originally Posted by vbfbryce
Exactly. That's why I suggested he write to the worksheet with those loops to isolate the problem.
But no need to change any code if its empty however he did state the loop debug.writeline is all populated. I would like to see using the same code but change from a range variable to a simple one cell written text value. If it works then its the array data. Also change to specifically referencing the sheet name
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Jan 11th, 2017, 03:35 PM
#24
Re: Excel API Question - Populate spreadsheet with 2D array contents
I think his array is larger than his range. The array goes from zero to M, zero to N, while the range goes from row ONE to row M, same with the columns, right?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|