Results 1 to 10 of 10

Thread: VB.NET GetObject and save makes spreadhsheet unreadable in Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    6

    VB.NET GetObject and save makes spreadhsheet unreadable in Excel

    I write to a spreadhsheet and can go back into VB.NET and read it again without any issues. I can also read spreadsheet in open Office without any issues, but in OO I can't save in XLSX or I'd just be ok with this weird step. But I cannot open up spreadsheet in Excel. It just says book on top and there is not an option to save and no data is present.

    Any Ideas?

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel

    Welcome to VBForums

    It sounds like you aren't saving it in an appropriate way (perhaps a different file format, with or without an Excel based extension on the filename), but as we can't see your code we can only guess... if you show us the code then we can help solve it.

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    6

    Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel

    Quote Originally Posted by si_the_geek View Post
    Welcome to VBForums

    It sounds like you aren't saving it in an appropriate way (perhaps a different file format, with or without an Excel based extension on the filename), but as we can't see your code we can only guess... if you show us the code then we can help solve it.

    VB.Net Code:
    1. Imports System
    2. Imports System.Data
    3. 'Imports System.Data.OleDb
    4. Imports System.IO
    5. Imports Microsoft.Office.Interop.Excel
    6. Imports System.Collections
    7. Imports System.ComponentModel
    8. 'Imports System.Drawing
    9. Imports System.Text
    10. Imports Microsoft.Office.Interop
    11.  
    12. 'Imports iTextSharp
    13. 'Imports iTextSharp.text
    14. 'Imports iTextSharp.text.pdf
    15. 'Imports iTextSharp.text.xml
    16.  
    17.  
    18. Module Module1
    19.     Public conn As OLEDBConnection()
    20.     Public sr As String
    21.     Public Filename As String
    22.     Public ImageFileName As String
    23.     Public WorkArea As String
    24.     Public chkexcel As Boolean
    25.     'Public oexcel As New Application
    26.     Public oexcel As New Excel.Application
    27.     'Public oexcel As New Excel.ApplicationClass
    28.     Public obookDG As Workbook = Nothing
    29.     Public osheet As Worksheet = Nothing
    30.     'Public Activesheet As Worksheet = Nothing
    31.     Public WorkSheetName As String
    32.     Public Directory As String
    33.     Public StoreName As String
    34.     Public LineID As String
    35.     Public LineName As String
    36.     Public ListData() As String
    37.     Public LineData() As String
    38.     Public args As String
    39.     Public strargs As String
    40.  
    41.  
    42.     'Private Property ActiveCell As New Object
    43.  
    44.     'Private Property xlNone As New Object
    45.  
    46.     Sub Main(ByVal args As String())
    47.  
    48.         Dim datestart As Date = Date.Now
    49.  
    50.         ListData = Split(Command, "~")
    51.         Directory = Trim(ListData(0))
    52.         StoreName = Trim(ListData(1))
    53.  
    54.  
    55.         Console.WriteLine("Directory=" & Directory)
    56.         Console.WriteLine("StoreName=" & StoreName)
    57.         Console.Write("Press Enter to exit")
    58.         Console.Read()
    59.  
    60.         Dim ActiveCell As New Object
    61.         Dim xlNone As New Object
    62.         Dim irow As Integer
    63.         Dim irow1 As Integer
    64.         Dim iWorkSheet As Integer
    65.         Dim Line As String
    66.         Dim AreaDisp As String
    67.         'Dim oRng As Integer
    68.         'oexcel.Visible = True
    69.         oexcel.Application.DisplayAlerts = True
    70.  
    71.         Console.WriteLine(Directory & "\" & StoreName & "Dept.xlsx")
    72.         AreaDisp = Directory & "\" & StoreName & "Dept.xlsx"
    73.         obookDG = GetObject("C:\groupcbf\Dynamics\Epicor\Warren\WarrenDept.xlsx")
    74.         'obookDG = GetObject(Directory & "\" & StoreName & "Dept.xlsx")
    75.  
    76.  
    77.  
    78.         'WorkSheetName = obookDG.Worksheets("Sheet1").ToString
    79.  
    80.  
    81.         'Console.WriteLine(WorkSheetName)
    82.         irow = 2
    83.         irow1 = 2
    84.         iWorkSheet = 1
    85.  
    86.         WorkSheetName = obookDG.Worksheets(iWorkSheet).Name
    87.         Console.WriteLine(WorkSheetName)
    88.  
    89.         obookDG.Worksheets(iWorkSheet).cells(1, 1) = "ID"
    90.         obookDG.Worksheets(iWorkSheet).cells(1, 2) = "Department Name"
    91.  
    92.  
    93.         Using sr As StreamReader = New StreamReader(Directory & "\" & "DEPARTMENT.TXT")
    94.  
    95.             ' Read and display the lines from the file until the end
    96.             ' of the file is reached.
    97.             Line = sr.ReadLine()
    98.             Do
    99.                 'Console.WriteLine(Line)
    100.                 ' If irow < 65000 Then
    101.                 LineData = Split(Line, "|")
    102.                 LineID = Trim(LineData(0))
    103.                 LineName = Trim(LineData(1))
    104.  
    105.                 AreaDisp = obookDG.Worksheets(iWorkSheet).cells(irow1, 1).value & "~" & obookDG.Worksheets(iWorkSheet).cells(irow1, 2).value & "~row=" & irow1
    106.  
    107.                 Console.Write(AreaDisp)
    108.                 Console.Read()
    109.                 obookDG.Worksheets(iWorkSheet).cells(irow1, 1) = Trim(LineID).ToString
    110.                 obookDG.Worksheets(iWorkSheet).cells(irow1, 2) = Trim(LineName).ToString
    111.                 irow1 = irow1 + 1
    112.                 irow = irow + 1
    113.                 If Int(irow / 1000) = (irow / 1000) Then
    114.                     Console.WriteLine(irow)
    115.                 End If
    116.                 'End If
    117.                 Line = sr.ReadLine()
    118.             Loop Until Line Is Nothing
    119.             sr.Close()
    120.         End Using
    121.  
    122.  
    123.         obookDG.Worksheets(iWorkSheet).columns(1).columnwidth = 6.53
    124.         obookDG.Worksheets(iWorkSheet).columns(2).columnwidth = 32.53
    125.        
    126.         With obookDG.Worksheets(iWorkSheet).Range("A1:B1")
    127.             .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
    128.             .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
    129.         End With
    130.  
    131.         obookDG.Worksheets(iWorkSheet).Range("A1:B4000").Font.Size = 12.0
    132.         obookDG.Worksheets(iWorkSheet).Range("A1:B4000").Font.Bold = True
    133.  
    134.         obookDG.Worksheets(iWorkSheet).PageSetup.CenterFooter = "&C&""Segoe(UI),Bold""&12 Department Analysis - Dynamics 365"
    135.         obookDG.Worksheets(iWorkSheet).PageSetup.LeftFooter = "Page " & "&P" & " of " & "&N"
    136.         obookDG.Worksheets(iWorkSheet).PageSetup.CenterHeader = "&C&""Segoe(UI),Bold""&14 " & Trim(StoreName) & "Department Review"
    137.         obookDG.Worksheets(iWorkSheet).PageSetup.LeftMargin = 20
    138.         obookDG.Worksheets(iWorkSheet).PageSetup.RightMargin = 20
    139.         obookDG.Worksheets(iWorkSheet).PageSetup.PrintGridlines = True
    140.  
    141.         obookDG.Worksheets(iWorkSheet).cells(1, 1).interior.Color = RGB(179, 170, 179)
    142.         obookDG.Worksheets(iWorkSheet).cells(1, 2).interior.color = RGB(179, 170, 179)
    143.         obookDG.Worksheets(iWorkSheet).cells(1, 1).font.Color = RGB(0, 0, 0)
    144.         obookDG.Worksheets(iWorkSheet).cells(1, 2).font.color = RGB(0, 0, 0)
    145.  
    146.         obookDG.Close(SaveChanges:=True)
    147.         'obookDG.SaveAs(Directory & "\" & StoreName & "Dept.xlsx")
    148.         'obookDG.Close(SaveChanges:=True)
    149.         'obookDG.Save()
    150.         'obookDG.Close()
    151.         oexcel.Application.DisplayAlerts = True
    152.         obookDG = Nothing
    153.         oexcel.Quit()
    154.         oexcel = Nothing
    155.         chkexcel = False
    156.  
    157.         'obookDG.SaveAs(Directory & "\" & StoreName & "Dept.xlsx")
    158.  
    159.         'System.IO.File.Delete("TIME.TXT")
    160.         Dim dateEnd As Date = Date.Now
    161.         End_Excel_App(datestart, dateEnd) ' This closes excel proces
    162.        
    163.     End Sub
    164.  
    165.     Private Sub End_Excel_App(datestart As Date, dateEnd As Date)
    166.         Dim xlp() As Process = Process.GetProcessesByName("EXCEL")
    167.         For Each Process As Process In xlp
    168.             If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
    169.                 Process.Kill()
    170.                 Exit For
    171.             End If
    172.         Next
    173.     End Sub
    174.  
    175. End Module
    Last edited by si_the_geek; Nov 7th, 2017 at 02:38 PM. Reason: added code tags

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel

    I can't see anything that would cause that problem, but it is hard for me to read clearly as I'm distracted by the fact you have a mixture of ways of working with Excel (that aren't really compatible), and various bits of dubious code.

    For example, it is extremely rare that declaring a variable "As New Object" is sensible, and it certainly doesn't seem to be here... especially as the variable names are duplicating names used within Excel automation (and are ones you should not declare yourself). Thankfully you don't seem to be using them at all, so removing those lines is the way to go.

    You have an application variable (oexcel) that is being misused. It doesn't make sense to create a new instance of the application and be using that along with GetObject, as GetObject will acquire an instance of Excel anyway via the workbook (and it probably wont be the same application instance). The fact you are only using oexcel to set DisplayAlerts=True (twice) doesn't alleviate the concerns. If using oexcel at all you should set it like this: oexcel = obookDG.Application (and remove the New from the declaration).

    The sub End_Excel_App is a very bad way to do things (it could easily close other instances of Excel that you didn't intend to close), but doing that part of things the right way is a large topic in itself, so we'd best avoid it.

    You have declared a variable to contain a worksheet (osheet), but don't use it. Using it would make the code much easier to read (as each instance of obookDG.Worksheets(iWorkSheet) would change to just osheet) and also run a bit faster.


    I find it rather concerning that you are using GetObject to grab the workbook, but are then closing the workbook. Generally you would only use GetObject if you want to work with a workbook that is already on screen (in which case you probably wouldn't want to close it), otherwise you would use Workbooks.Open (and you probably would want to close it).


    In terms of the issue you created this thread for, there are a variety of possibilities, but based on the code you have I suspect that the issue with Excel not showing it is down to the visibility of the workbook, setting it to be visible may well fix that. If memory serves (it may not!) you can do it like this: obookDG.Visible =True

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    6

    Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel

    Quote Originally Posted by si_the_geek View Post
    I can't see anything that would cause that problem, but it is hard for me to read clearly as I'm distracted by the fact you have a mixture of ways of working with Excel (that aren't really compatible), and various bits of dubious code.

    For example, it is extremely rare that declaring a variable "As New Object" is sensible, and it certainly doesn't seem to be here... especially as the variable names are duplicating names used within Excel automation (and are ones you should not declare yourself). Thankfully you don't seem to be using them at all, so removing those lines is the way to go.

    You have an application variable (oexcel) that is being misused. It doesn't make sense to create a new instance of the application and be using that along with GetObject, as GetObject will acquire an instance of Excel anyway via the workbook (and it probably wont be the same application instance). The fact you are only using oexcel to set DisplayAlerts=True (twice) doesn't alleviate the concerns. If using oexcel at all you should set it like this: oexcel = obookDG.Application (and remove the New from the declaration).

    The sub End_Excel_App is a very bad way to do things (it could easily close other instances of Excel that you didn't intend to close), but doing that part of things the right way is a large topic in itself, so we'd best avoid it.

    You have declared a variable to contain a worksheet (osheet), but don't use it. Using it would make the code much easier to read (as each instance of obookDG.Worksheets(iWorkSheet) would change to just osheet) and also run a bit faster.


    I find it rather concerning that you are using GetObject to grab the workbook, but are then closing the workbook. Generally you would only use GetObject if you want to work with a workbook that is already on screen (in which case you probably wouldn't want to close it), otherwise you would use Workbooks.Open (and you probably would want to close it).


    In terms of the issue you created this thread for, there are a variety of possibilities, but based on the code you have I suspect that the issue with Excel not showing it is down to the visibility of the workbook, setting it to be visible may well fix that. If memory serves (it may not!) you can do it like this: obookDG.Visible =True
    First I never really programmed i VB but taught myself for spreadsheet stuff. Which I do often to create spreadsheets.

    I can do same exact code but do a create object and it is readable afterwards. It's on in get object that there is an issue.

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel

    Hi thepla,

    I posted this sample the other day for somebody, you will have to adjust to xlsx.
    I'm not going to go threw your code, only one thing.. if you use a COM Object release it when your done.

    Code:
    Option Strict On
    
    Imports Excel
    
    Public Class Form1
        Public Enum Excel_Colors
            rgbAliceBlue = 16775408          'Alice Blue
            rgbAntiqueWhite = 14150650       'Antique White
            rgbAqua = 16776960               'Aqua
            rgbAquamarine = 13959039         'Aquamarine
            rgbAzure = 16777200              'Azure
            rgbBeige = 14480885              'Beige
            rgbBisque = 12903679             'Bisque
            rgbBlack = 0                     'Black
            rgbBlanchedAlmond = 13495295     'Blanched Almond
            rgbBlue = 16711680               'Blue
            rgbBlueViolet = 14822282         'Blue Violet
            rgbBrown = 2763429               'Brown
            rgbBurlyWood = 8894686           'Burly Wood
            rgbCadetBlue = 10526303          'Cadet Blue
            rgbChartreuse = 65407            'Chartreuse
            rgbCoral = 5275647               'Coral
            rgbCornflowerBlue = 15570276     'Cornflower Blue
            rgbCornsilk = 14481663           'Cornsilk
            rgbCrimson = 3937500             'Crimson
            rgbDarkBlue = 9109504            'Dark Blue
            rgbDarkCyan = 9145088            'Dark Cyan
            rgbDarkGoldenrod = 755384        'Dark Goldenrod
            rgbDarkGray = 11119017           'Dark Gray
            rgbDarkGreen = 25600             'Dark Green
            rgbDarkGrey = 11119017           'Dark Grey
            rgbDarkKhaki = 7059389           'Dark Khaki
            rgbDarkMagenta = 9109643         'Dark Magenta
            rgbDarkOliveGreen = 3107669      'Dark Olive Green
            rgbDarkOrange = 36095            'Dark Orange
            rgbDarkOrchid = 13382297         'Dark Orchid
            rgbDarkRed = 139                 'Dark Red
            rgbDarkSalmon = 8034025          'Dark Salmon
            rgbDarkSeaGreen = 9419919        'Dark Sea Green
            rgbDarkSlateBlue = 9125192       'Dark Slate Blue
            rgbDarkSlateGray = 5197615       'Dark Slate Gray
            rgbDarkSlateGrey = 5197615       'Dark Slate Grey
            rgbDarkTurquoise = 13749760      'Dark Turquoise
            rgbDarkViolet = 13828244         'Dark Violet
            rgbDeepPink = 9639167            'Deep Pink
            rgbDeepSkyBlue = 16760576        'Deep Sky Blue
            rgbDimGray = 6908265             'Dim Gray
            rgbDimGrey = 6908265             'Dim Grey
            rgbDodgerBlue = 16748574         'Dodger Blue
            rgbFireBrick = 2237106           'Fire Brick
            rgbFloralWhite = 15792895        'Floral White
            rgbForestGreen = 2263842         'Forest Green
            rgbFuchsia = 16711935            'Fuchsia
            rgbGainsboro = 14474460          'Gainsboro
            rgbGhostWhite = 16775416         'Ghost White
            rgbGold = 55295                  'Gold
            rgbGoldenrod = 2139610           'Goldenrod
            rgbGray = 8421504                'Gray
            rgbGreen = 32768                 'Green
            rgbGreenYellow = 3145645         'Green Yellow
            rgbGrey = 8421504                'Grey
            rgbHoneydew = 15794160           'Honeydew
            rgbHotPink = 11823615            'Hot Pink
            rgbIndianRed = 6053069           'Indian Red
            rgbIndigo = 8519755              'Indigo
            rgbIvory = 15794175              'Ivory
            rgbKhaki = 9234160               'Khaki
            rgbLavender = 16443110           'Lavender
            rgbLavenderBlush = 16118015      'Lavender Blush
            rgbLawnGreen = 64636             'Lawn Green
            rgbLemonChiffon = 13499135       'Lemon Chiffon
            rgbLightBlue = 15128749          'Light Blue
            rgbLightCoral = 8421616          'Light Coral
            rgbLightCyan = 9145088           'Light Cyan
            rgbLightGoldenrodYellow = 13826810   'LightGoldenrodYellow
            rgbLightGray = 13882323          'Light Gray
            rgbLightGreen = 9498256          'Light Green
            rgbLightGrey = 13882323          'Light Grey
            rgbLightPink = 12695295          'Light Pink
            rgbLightSalmon = 8036607         'Light Salmon
            rgbLightSeaGreen = 11186720      'Light Sea Green
            rgbLightSkyBlue = 16436871       'Light Sky Blue
            rgbLightSlateGray = 10061943     'Light Slate Gray
            rgbLightSteelBlue = 14599344     'Light Steel Blue
            rgbLightYellow = 14745599        'Light Yellow
            rgbLime = 65280                  'Lime
            rgbLimeGreen = 3329330           'Lime Green
            rgbLinen = 15134970              'Linen
            rgbMaroon = 128                  'Maroon
            rgbMediumAquamarine = 11206502   'Medium Aquamarine
            rgbMediumBlue = 13434880         'Medium Blue
            rgbMediumOrchid = 13850042       'Medium Orchid
            rgbMediumPurple = 14381203       'Medium Purple
            rgbMediumSeaGreen = 7451452      'Medium Sea Green
            rgbMediumSlateBlue = 15624315    'Medium Slate Blue
            rgbMediumSpringGreen = 10156544  'Medium Spring Green
            rgbMediumTurquoise = 13422920    'Medium Turquoise
            rgbMediumVioletRed = 8721863     'Medium Violet Red
            rgbMidnightBlue = 7346457        'Midnight Blue
            rgbMintCream = 16449525          'Mint Cream
            rgbMistyRose = 14804223          'Misty Rose
            rgbMoccasin = 11920639           'Moccasin
            rgbNavajoWhite = 11394815        'Navajo White
            rgbNavy = 8388608                'Navy
            rgbNavyBlue = 8388608            'Navy Blue
            rgbOldLace = 15136253            'Old Lace
            rgbOlive = 32896                 'Olive
            rgbOliveDrab = 2330219           'Olive Drab
            rgbOrange = 42495                'Orange
            rgbOrangeRed = 17919             'Orange Red
            rgbOrchid = 14053594             'Orchid
            rgbPaleGoldenrod = 7071982       'Pale Goldenrod
            rgbPaleGreen = 10025880          'Pale Green
            rgbPaleTurquoise = 15658671      'Pale Turquoise
            rgbPaleVioletRed = 9662683       'Pale Violet Red
            rgbPapayaWhip = 14020607         'Papaya Whip
            rgbPeachPuff = 12180223          'Peach Puff
            rgbPeru = 4163021                'Peru
            rgbPink = 13353215               'Pink
            rgbPlum = 14524637               'Plum
            rgbPowderBlue = 15130800         'Powder Blue
            rgbPurple = 8388736              'Purple
            rgbRed = 255                     'Red
            rgbRosyBrown = 9408444           'Rosy Brown
            rgbRoyalBlue = 14772545          'Royal Blue
            rgbSalmon = 7504122              'Salmon
            rgbSandyBrown = 6333684          'Sandy Brown
            rgbSeaGreen = 5737262            'Sea Green
            rgbSeashell = 15660543           'Seashell
            rgbSienna = 2970272              'Sienna
            rgbSilver = 12632256             'Silver
            rgbSkyBlue = 15453831            'Sky Blue
            rgbSlateBlue = 13458026          'Slate Blue
            rgbSlateGray = 9470064           'Slate Gray
            rgbSnow = 16448255               'Snow
            rgbSpringGreen = 8388352         'Spring Green
            rgbSteelBlue = 11829830          'Steel Blue
            rgbTan = 9221330                 'Tan
            rgbTeal = 8421376                'Teal
            rgbThistle = 14204888            'Thistle
            rgbTomato = 4678655              'Tomato
            rgbTurquoise = 13688896          'Turquoise
            rgbViolet = 15631086             'Violet
            rgbWheat = 11788021              'Wheat
            rgbWhite = 16777215              'White
            rgbWhiteSmoke = 16119285         'White Smoke
            rgbYellow = 65535                'Yellow
            rgbYellowGreen = 3329434         'Yellow Green
        End Enum
    
       
       
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim a As Integer
            Dim Exceldaten(9, 2) As String
            For a = 0 To 9
                Exceldaten(a, 0) = Chr(65 + a)
                Exceldaten(a, 1) = Chr(48 + a)
                Exceldaten(a, 2) = "Column 3 - Row " & (a + 1)
            Next
            Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            oXL = CType(CreateObject("Excel.Application"), Excel.Application)
            oWB = oXL.Workbooks.Add
            oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)
            'oXL.Visible = True
            oSheet.Range("A1").Value = "vbForums.com"
            oSheet.Range("B1").Value = "my Number"
            oSheet.Range("C1").Value = "Sssssss"
            'Excelformat
            oSheet.Range("A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("E1").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("A1").ColumnWidth = 10
            oSheet.Range("B1").ColumnWidth = 10
            oSheet.Range("C1").ColumnWidth = 15.5
            With oSheet.Range("A1", "C1")
                .Font.Bold = True
                .Font.Color = Excel_Colors.rgbOrangeRed
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            End With
    
            'add something to calculate
            oSheet.Range("D2").Value = 10.25
            oSheet.Range("D3").Value = 101.25
            oSheet.Range("D4").Value = 123330.65
            oSheet.Range("D5").Value = 1255.25
          
            oSheet.Range("E6").Value = "20.10.2017 " & TimeOfDay
            oSheet.Range("E7").Value = String.Format("{0:dd.MM.yyyy HH:mm:ss}", Date.Now)
    
    
    
            'Dropdown Start ................
            'create a Dropdown , do you want to hide that column ?
            'Dim columnA As Excel.Range = CType(oSheet.Columns(2), Excel.Range)
            'columnA.Hidden = True
    
            'this will set the Dropdown Button in cell D4
            Dim range As Excel.Range = CType(oSheet.Cells(1, 4), Excel.Range)
    
    
            'what Range ? in this case B2:B70
            range.Validation.Add(Type:=Excel.XlDVType.xlValidateList, _
                AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _
                Operator:=Excel.XlFormatConditionOperator.xlBetween, _
                Formula1:="=$B$2:$B$70")
            'Dropdown End...............
    
    
            'Data to Excel 
            oSheet.Range("A2", "C9").Value = Exceldaten
            'Calulate values, 
            'in German = "=Summe(D2:D5)"
            'in Englisch probably = "=Sum(D2:D5)"
            oSheet.Range("D7").Value = "=Summe(D2:D5)"
            oSheet.Range("D7").NumberFormatLocal = "#.##0,00"
            oSheet.Range("D7").Interior.Color = Excel_Colors.rgbCadetBlue
    
            'Save Excel
            oSheet.SaveAs("C:\Exceldaten99.xls")
            oXL.Quit()
            releaseObject(oXL)
            releaseObject(oWB)
            End_Excel_App(datestart, dateend)
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
        Dim datestart As Date = Date.Now
        Dim dateend As Date = Date.Now
    
        Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
            Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
            For Each Process As Process In xlp
                If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                    Process.Kill()
                    Exit For
                End If
            Next
        End Sub

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    6

    Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel

    Quote Originally Posted by ChrisE View Post
    Hi thepla,

    I posted this sample the other day for somebody, you will have to adjust to xlsx.
    I'm not going to go threw your code, only one thing.. if you use a COM Object release it when your done.

    Code:
    Option Strict On
    
    Imports Excel
    
    Public Class Form1
        Public Enum Excel_Colors
            rgbAliceBlue = 16775408          'Alice Blue
            rgbAntiqueWhite = 14150650       'Antique White
            rgbAqua = 16776960               'Aqua
            rgbAquamarine = 13959039         'Aquamarine
            rgbAzure = 16777200              'Azure
            rgbBeige = 14480885              'Beige
            rgbBisque = 12903679             'Bisque
            rgbBlack = 0                     'Black
            rgbBlanchedAlmond = 13495295     'Blanched Almond
            rgbBlue = 16711680               'Blue
            rgbBlueViolet = 14822282         'Blue Violet
            rgbBrown = 2763429               'Brown
            rgbBurlyWood = 8894686           'Burly Wood
            rgbCadetBlue = 10526303          'Cadet Blue
            rgbChartreuse = 65407            'Chartreuse
            rgbCoral = 5275647               'Coral
            rgbCornflowerBlue = 15570276     'Cornflower Blue
            rgbCornsilk = 14481663           'Cornsilk
            rgbCrimson = 3937500             'Crimson
            rgbDarkBlue = 9109504            'Dark Blue
            rgbDarkCyan = 9145088            'Dark Cyan
            rgbDarkGoldenrod = 755384        'Dark Goldenrod
            rgbDarkGray = 11119017           'Dark Gray
            rgbDarkGreen = 25600             'Dark Green
            rgbDarkGrey = 11119017           'Dark Grey
            rgbDarkKhaki = 7059389           'Dark Khaki
            rgbDarkMagenta = 9109643         'Dark Magenta
            rgbDarkOliveGreen = 3107669      'Dark Olive Green
            rgbDarkOrange = 36095            'Dark Orange
            rgbDarkOrchid = 13382297         'Dark Orchid
            rgbDarkRed = 139                 'Dark Red
            rgbDarkSalmon = 8034025          'Dark Salmon
            rgbDarkSeaGreen = 9419919        'Dark Sea Green
            rgbDarkSlateBlue = 9125192       'Dark Slate Blue
            rgbDarkSlateGray = 5197615       'Dark Slate Gray
            rgbDarkSlateGrey = 5197615       'Dark Slate Grey
            rgbDarkTurquoise = 13749760      'Dark Turquoise
            rgbDarkViolet = 13828244         'Dark Violet
            rgbDeepPink = 9639167            'Deep Pink
            rgbDeepSkyBlue = 16760576        'Deep Sky Blue
            rgbDimGray = 6908265             'Dim Gray
            rgbDimGrey = 6908265             'Dim Grey
            rgbDodgerBlue = 16748574         'Dodger Blue
            rgbFireBrick = 2237106           'Fire Brick
            rgbFloralWhite = 15792895        'Floral White
            rgbForestGreen = 2263842         'Forest Green
            rgbFuchsia = 16711935            'Fuchsia
            rgbGainsboro = 14474460          'Gainsboro
            rgbGhostWhite = 16775416         'Ghost White
            rgbGold = 55295                  'Gold
            rgbGoldenrod = 2139610           'Goldenrod
            rgbGray = 8421504                'Gray
            rgbGreen = 32768                 'Green
            rgbGreenYellow = 3145645         'Green Yellow
            rgbGrey = 8421504                'Grey
            rgbHoneydew = 15794160           'Honeydew
            rgbHotPink = 11823615            'Hot Pink
            rgbIndianRed = 6053069           'Indian Red
            rgbIndigo = 8519755              'Indigo
            rgbIvory = 15794175              'Ivory
            rgbKhaki = 9234160               'Khaki
            rgbLavender = 16443110           'Lavender
            rgbLavenderBlush = 16118015      'Lavender Blush
            rgbLawnGreen = 64636             'Lawn Green
            rgbLemonChiffon = 13499135       'Lemon Chiffon
            rgbLightBlue = 15128749          'Light Blue
            rgbLightCoral = 8421616          'Light Coral
            rgbLightCyan = 9145088           'Light Cyan
            rgbLightGoldenrodYellow = 13826810   'LightGoldenrodYellow
            rgbLightGray = 13882323          'Light Gray
            rgbLightGreen = 9498256          'Light Green
            rgbLightGrey = 13882323          'Light Grey
            rgbLightPink = 12695295          'Light Pink
            rgbLightSalmon = 8036607         'Light Salmon
            rgbLightSeaGreen = 11186720      'Light Sea Green
            rgbLightSkyBlue = 16436871       'Light Sky Blue
            rgbLightSlateGray = 10061943     'Light Slate Gray
            rgbLightSteelBlue = 14599344     'Light Steel Blue
            rgbLightYellow = 14745599        'Light Yellow
            rgbLime = 65280                  'Lime
            rgbLimeGreen = 3329330           'Lime Green
            rgbLinen = 15134970              'Linen
            rgbMaroon = 128                  'Maroon
            rgbMediumAquamarine = 11206502   'Medium Aquamarine
            rgbMediumBlue = 13434880         'Medium Blue
            rgbMediumOrchid = 13850042       'Medium Orchid
            rgbMediumPurple = 14381203       'Medium Purple
            rgbMediumSeaGreen = 7451452      'Medium Sea Green
            rgbMediumSlateBlue = 15624315    'Medium Slate Blue
            rgbMediumSpringGreen = 10156544  'Medium Spring Green
            rgbMediumTurquoise = 13422920    'Medium Turquoise
            rgbMediumVioletRed = 8721863     'Medium Violet Red
            rgbMidnightBlue = 7346457        'Midnight Blue
            rgbMintCream = 16449525          'Mint Cream
            rgbMistyRose = 14804223          'Misty Rose
            rgbMoccasin = 11920639           'Moccasin
            rgbNavajoWhite = 11394815        'Navajo White
            rgbNavy = 8388608                'Navy
            rgbNavyBlue = 8388608            'Navy Blue
            rgbOldLace = 15136253            'Old Lace
            rgbOlive = 32896                 'Olive
            rgbOliveDrab = 2330219           'Olive Drab
            rgbOrange = 42495                'Orange
            rgbOrangeRed = 17919             'Orange Red
            rgbOrchid = 14053594             'Orchid
            rgbPaleGoldenrod = 7071982       'Pale Goldenrod
            rgbPaleGreen = 10025880          'Pale Green
            rgbPaleTurquoise = 15658671      'Pale Turquoise
            rgbPaleVioletRed = 9662683       'Pale Violet Red
            rgbPapayaWhip = 14020607         'Papaya Whip
            rgbPeachPuff = 12180223          'Peach Puff
            rgbPeru = 4163021                'Peru
            rgbPink = 13353215               'Pink
            rgbPlum = 14524637               'Plum
            rgbPowderBlue = 15130800         'Powder Blue
            rgbPurple = 8388736              'Purple
            rgbRed = 255                     'Red
            rgbRosyBrown = 9408444           'Rosy Brown
            rgbRoyalBlue = 14772545          'Royal Blue
            rgbSalmon = 7504122              'Salmon
            rgbSandyBrown = 6333684          'Sandy Brown
            rgbSeaGreen = 5737262            'Sea Green
            rgbSeashell = 15660543           'Seashell
            rgbSienna = 2970272              'Sienna
            rgbSilver = 12632256             'Silver
            rgbSkyBlue = 15453831            'Sky Blue
            rgbSlateBlue = 13458026          'Slate Blue
            rgbSlateGray = 9470064           'Slate Gray
            rgbSnow = 16448255               'Snow
            rgbSpringGreen = 8388352         'Spring Green
            rgbSteelBlue = 11829830          'Steel Blue
            rgbTan = 9221330                 'Tan
            rgbTeal = 8421376                'Teal
            rgbThistle = 14204888            'Thistle
            rgbTomato = 4678655              'Tomato
            rgbTurquoise = 13688896          'Turquoise
            rgbViolet = 15631086             'Violet
            rgbWheat = 11788021              'Wheat
            rgbWhite = 16777215              'White
            rgbWhiteSmoke = 16119285         'White Smoke
            rgbYellow = 65535                'Yellow
            rgbYellowGreen = 3329434         'Yellow Green
        End Enum
    
       
       
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim a As Integer
            Dim Exceldaten(9, 2) As String
            For a = 0 To 9
                Exceldaten(a, 0) = Chr(65 + a)
                Exceldaten(a, 1) = Chr(48 + a)
                Exceldaten(a, 2) = "Column 3 - Row " & (a + 1)
            Next
            Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            oXL = CType(CreateObject("Excel.Application"), Excel.Application)
            oWB = oXL.Workbooks.Add
            oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)
            'oXL.Visible = True
            oSheet.Range("A1").Value = "vbForums.com"
            oSheet.Range("B1").Value = "my Number"
            oSheet.Range("C1").Value = "Sssssss"
            'Excelformat
            oSheet.Range("A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("E1").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("A1").ColumnWidth = 10
            oSheet.Range("B1").ColumnWidth = 10
            oSheet.Range("C1").ColumnWidth = 15.5
            With oSheet.Range("A1", "C1")
                .Font.Bold = True
                .Font.Color = Excel_Colors.rgbOrangeRed
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            End With
    
            'add something to calculate
            oSheet.Range("D2").Value = 10.25
            oSheet.Range("D3").Value = 101.25
            oSheet.Range("D4").Value = 123330.65
            oSheet.Range("D5").Value = 1255.25
          
            oSheet.Range("E6").Value = "20.10.2017 " & TimeOfDay
            oSheet.Range("E7").Value = String.Format("{0:dd.MM.yyyy HH:mm:ss}", Date.Now)
    
    
    
            'Dropdown Start ................
            'create a Dropdown , do you want to hide that column ?
            'Dim columnA As Excel.Range = CType(oSheet.Columns(2), Excel.Range)
            'columnA.Hidden = True
    
            'this will set the Dropdown Button in cell D4
            Dim range As Excel.Range = CType(oSheet.Cells(1, 4), Excel.Range)
    
    
            'what Range ? in this case B2:B70
            range.Validation.Add(Type:=Excel.XlDVType.xlValidateList, _
                AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _
                Operator:=Excel.XlFormatConditionOperator.xlBetween, _
                Formula1:="=$B$2:$B$70")
            'Dropdown End...............
    
    
            'Data to Excel 
            oSheet.Range("A2", "C9").Value = Exceldaten
            'Calulate values, 
            'in German = "=Summe(D2:D5)"
            'in Englisch probably = "=Sum(D2:D5)"
            oSheet.Range("D7").Value = "=Summe(D2:D5)"
            oSheet.Range("D7").NumberFormatLocal = "#.##0,00"
            oSheet.Range("D7").Interior.Color = Excel_Colors.rgbCadetBlue
    
            'Save Excel
            oSheet.SaveAs("C:\Exceldaten99.xls")
            oXL.Quit()
            releaseObject(oXL)
            releaseObject(oWB)
            End_Excel_App(datestart, dateend)
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
        Dim datestart As Date = Date.Now
        Dim dateend As Date = Date.Now
    
        Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
            Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
            For Each Process As Process In xlp
                If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                    Process.Kill()
                    Exit For
                End If
            Next
        End Sub

    regards
    Chris
    Thanks

  8. #8

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    6

    Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel

    Suggestion for doing a create object gave some great stuff for colors, thanks. My issue was never with create object but when I opened a spreadsheet and upon saving it of save as I could not get to sheet itself.

    Well after just stopping and using Open Office to open file I tried again today to open excel file. it turns out ExcelName.xlsx opens just as book. I notice I can get to unhide and ExcelName.xlxx is hidden. I can unhide and sure enough there is my data and I just hit save and I'm back on track.

    Now what can cause it to be hidden? My code doesn't do any commands to hide anything.

    Thanks
    steve

  9. #9

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    6

    Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel

    Quote Originally Posted by thepla View Post
    Suggestion for doing a create object gave some great stuff for colors, thanks. My issue was never with create object but when I opened a spreadsheet and upon saving it of save as I could not get to sheet itself.

    Well after just stopping and using Open Office to open file I tried again today to open excel file. it turns out ExcelName.xlsx opens just as book. I notice I can get to unhide and ExcelName.xlxx is hidden. I can unhide and sure enough there is my data and I just hit save and I'm back on track.

    Now what can cause it to be hidden? My code doesn't do any commands to hide anything.

    Thanks
    steve
    hi again,

    I cannot figure out how to unhide a workbook with VB. Any ideas?

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel

    If my previous guess ( obookDG.Visible =True ) doesn't work, a quick web search suggests this: Windows(WB.Name).Visible = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width