VS 2015 Excel API Question - Populate spreadsheet with 2D array contents-VBForums
Results 1 to 24 of 24

Thread: Excel API Question - Populate spreadsheet with 2D array contents

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    38

    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!

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    30,107

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    38

    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!

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    30,107

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    38

    Re: Excel API Question - Populate spreadsheet with 2D array contents

    Quote Originally Posted by techgnome View Post
    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.

  6. #6
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,261

    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!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 Intel 300 GB SSDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1, Office 2010, VS 2013

  7. #7

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    38

    Re: Excel API Question - Populate spreadsheet with 2D array contents

    rngstring: A1204

    is presenting as a smiley face

    Name:  Screen Shot 01-10-17 at 01.16 PM.JPG
Views: 25
Size:  21.5 KB
    Attached Images Attached Images  
    Last edited by ssabc; Jan 10th, 2017 at 02:18 PM.

  8. #8
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,261

    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!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 Intel 300 GB SSDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1, Office 2010, VS 2013

  9. #9
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,261

    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!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 Intel 300 GB SSDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1, Office 2010, VS 2013

  10. #10

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    38

    Re: Excel API Question - Populate spreadsheet with 2D array contents

    Quote Originally Posted by RobDog888 View Post
    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!

  11. #11
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,339

    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)

  12. #12

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    38

    Re: Excel API Question - Populate spreadsheet with 2D array contents

    Quote Originally Posted by vbfbryce View Post
    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??

  13. #13
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,339

    Re: Excel API Question - Populate spreadsheet with 2D array contents

    Where do you rename that sheet? I'm not seeing that anywhere.

  14. #14

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    38

    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

  15. #15
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,339

    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?

  16. #16

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    38

    Re: Excel API Question - Populate spreadsheet with 2D array contents

    Quote Originally Posted by vbfbryce View Post
    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.

  17. #17
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,261

    Re: Excel API Question - Populate spreadsheet with 2D array contents

    Quote Originally Posted by ssabc View Post
    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!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 Intel 300 GB SSDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1, Office 2010, VS 2013

  18. #18
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,339

    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.

  19. #19
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,261

    Re: Excel API Question - Populate spreadsheet with 2D array contents

    Quote Originally Posted by vbfbryce View Post
    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!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 Intel 300 GB SSDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1, Office 2010, VS 2013

  20. #20
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,339

    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

  21. #21
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,261

    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!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 Intel 300 GB SSDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1, Office 2010, VS 2013

  22. #22
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,339

    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.

  23. #23
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,261

    Re: Excel API Question - Populate spreadsheet with 2D array contents

    Quote Originally Posted by vbfbryce View Post
    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!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 Intel 300 GB SSDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1, Office 2010, VS 2013

  24. #24
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,339

    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.