-
Nov 7th, 2017, 12:56 PM
#1
Thread Starter
New Member
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?
-
Nov 7th, 2017, 01:20 PM
#2
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.
-
Nov 7th, 2017, 02:17 PM
#3
Thread Starter
New Member
Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel
Originally Posted by si_the_geek
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:
Imports System Imports System.Data 'Imports System.Data.OleDb Imports System.IO Imports Microsoft.Office.Interop.Excel Imports System.Collections Imports System.ComponentModel 'Imports System.Drawing Imports System.Text Imports Microsoft.Office.Interop 'Imports iTextSharp 'Imports iTextSharp.text 'Imports iTextSharp.text.pdf 'Imports iTextSharp.text.xml Module Module1 Public conn As OLEDBConnection() Public sr As String Public Filename As String Public ImageFileName As String Public WorkArea As String Public chkexcel As Boolean 'Public oexcel As New Application Public oexcel As New Excel.Application 'Public oexcel As New Excel.ApplicationClass Public obookDG As Workbook = Nothing Public osheet As Worksheet = Nothing 'Public Activesheet As Worksheet = Nothing Public WorkSheetName As String Public Directory As String Public StoreName As String Public LineID As String Public LineName As String Public ListData() As String Public LineData() As String Public args As String Public strargs As String 'Private Property ActiveCell As New Object 'Private Property xlNone As New Object Sub Main(ByVal args As String()) Dim datestart As Date = Date.Now ListData = Split(Command, "~") Directory = Trim(ListData(0)) StoreName = Trim(ListData(1)) Console.WriteLine("Directory=" & Directory) Console.WriteLine("StoreName=" & StoreName) Console.Write("Press Enter to exit") Console.Read() Dim ActiveCell As New Object Dim xlNone As New Object Dim irow As Integer Dim irow1 As Integer Dim iWorkSheet As Integer Dim Line As String Dim AreaDisp As String 'Dim oRng As Integer 'oexcel.Visible = True oexcel.Application.DisplayAlerts = True Console.WriteLine(Directory & "\" & StoreName & "Dept.xlsx") AreaDisp = Directory & "\" & StoreName & "Dept.xlsx" obookDG = GetObject("C:\groupcbf\Dynamics\Epicor\Warren\WarrenDept.xlsx") 'obookDG = GetObject(Directory & "\" & StoreName & "Dept.xlsx") 'WorkSheetName = obookDG.Worksheets("Sheet1").ToString 'Console.WriteLine(WorkSheetName) irow = 2 irow1 = 2 iWorkSheet = 1 WorkSheetName = obookDG.Worksheets(iWorkSheet).Name Console.WriteLine(WorkSheetName) obookDG.Worksheets(iWorkSheet).cells(1, 1) = "ID" obookDG.Worksheets(iWorkSheet).cells(1, 2) = "Department Name" Using sr As StreamReader = New StreamReader(Directory & "\" & "DEPARTMENT.TXT") ' Read and display the lines from the file until the end ' of the file is reached. Line = sr.ReadLine() Do 'Console.WriteLine(Line) ' If irow < 65000 Then LineData = Split(Line, "|") LineID = Trim(LineData(0)) LineName = Trim(LineData(1)) AreaDisp = obookDG.Worksheets(iWorkSheet).cells(irow1, 1).value & "~" & obookDG.Worksheets(iWorkSheet).cells(irow1, 2).value & "~row=" & irow1 Console.Write(AreaDisp) Console.Read() obookDG.Worksheets(iWorkSheet).cells(irow1, 1) = Trim(LineID).ToString obookDG.Worksheets(iWorkSheet).cells(irow1, 2) = Trim(LineName).ToString irow1 = irow1 + 1 irow = irow + 1 If Int(irow / 1000) = (irow / 1000) Then Console.WriteLine(irow) End If 'End If Line = sr.ReadLine() Loop Until Line Is Nothing sr.Close() End Using obookDG.Worksheets(iWorkSheet).columns(1).columnwidth = 6.53 obookDG.Worksheets(iWorkSheet).columns(2).columnwidth = 32.53 With obookDG.Worksheets(iWorkSheet).Range("A1:B1") .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter End With obookDG.Worksheets(iWorkSheet).Range("A1:B4000").Font.Size = 12.0 obookDG.Worksheets(iWorkSheet).Range("A1:B4000").Font.Bold = True obookDG.Worksheets(iWorkSheet).PageSetup.CenterFooter = "&C&""Segoe(UI),Bold""&12 Department Analysis - Dynamics 365" obookDG.Worksheets(iWorkSheet).PageSetup.LeftFooter = "Page " & "&P" & " of " & "&N" obookDG.Worksheets(iWorkSheet).PageSetup.CenterHeader = "&C&""Segoe(UI),Bold""&14 " & Trim(StoreName) & "Department Review" obookDG.Worksheets(iWorkSheet).PageSetup.LeftMargin = 20 obookDG.Worksheets(iWorkSheet).PageSetup.RightMargin = 20 obookDG.Worksheets(iWorkSheet).PageSetup.PrintGridlines = True obookDG.Worksheets(iWorkSheet).cells(1, 1).interior.Color = RGB(179, 170, 179) obookDG.Worksheets(iWorkSheet).cells(1, 2).interior.color = RGB(179, 170, 179) obookDG.Worksheets(iWorkSheet).cells(1, 1).font.Color = RGB(0, 0, 0) obookDG.Worksheets(iWorkSheet).cells(1, 2).font.color = RGB(0, 0, 0) obookDG.Close(SaveChanges:=True) 'obookDG.SaveAs(Directory & "\" & StoreName & "Dept.xlsx") 'obookDG.Close(SaveChanges:=True) 'obookDG.Save() 'obookDG.Close() oexcel.Application.DisplayAlerts = True obookDG = Nothing oexcel.Quit() oexcel = Nothing chkexcel = False 'obookDG.SaveAs(Directory & "\" & StoreName & "Dept.xlsx") 'System.IO.File.Delete("TIME.TXT") Dim dateEnd As Date = Date.Now End_Excel_App(datestart, dateEnd) ' This closes excel proces End Sub Private Sub End_Excel_App(datestart As Date, dateEnd As Date) Dim xlp() As Process = Process.GetProcessesByName("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 End Module
Last edited by si_the_geek; Nov 7th, 2017 at 02:38 PM.
Reason: added code tags
-
Nov 7th, 2017, 04:43 PM
#4
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
-
Nov 7th, 2017, 06:02 PM
#5
Thread Starter
New Member
Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel
Originally Posted by si_the_geek
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.
-
Nov 8th, 2017, 03:49 AM
#6
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.
-
Nov 8th, 2017, 10:28 AM
#7
Thread Starter
New Member
Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel
Originally Posted by ChrisE
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
-
Nov 14th, 2017, 12:29 PM
#8
Thread Starter
New Member
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
-
Nov 17th, 2017, 11:16 AM
#9
Thread Starter
New Member
Re: VB.NET GetObject and save makes spreadhsheet unreadable in Excel
Originally Posted by thepla
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?
-
Nov 17th, 2017, 06:06 PM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|