Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   .NET Office Automation (http://www.xtremevbtalk.com/-net-office-automation/)
-   -   Excel 2010 VB.net 2012 Automation (http://www.xtremevbtalk.com/-net-office-automation/327300-excel-2010-vb-net-2012-automation.html)

sourcegin 09-29-2014 04:16 PM

Excel 2010 VB.net 2012 Automation
 
what i'm attempting to do is write values to an excel spreadsheet , i don't want the workbook to open , i just wanna write the values without overwriting the values from yesterday or the day before ,
can someone guide me or maybe even show me how to accomplish this better

this is what i have on a button click for right now

'---------------//Code starts here //-------------------------
'// Data insert to excel
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim myDay As String
Dim MyMonth As String
Dim MyYear As String
Dim offset As String
Dim Mydirectory As String
Dim MyExtension As String
Dim MyFileName As String
Dim FileCheck As String
Dim c1 As String
Dim time As DateTime = DateTime.Now
'xlApp.DisplayAlerts = False
offset = 9

'get month
myDay = Format(Now, " d ")
MyMonth = Format(Now, " mm ")
'get year
MyYear = Format(Now, " yyyy ")
'working directory
Mydirectory = "C:\REPORT\"
'----Start Excel---------------------------------------------
'Excel extension
xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Open("C:\REPORT\report.xlsx")
xlWorkSheet = xlApp.Worksheets("Daily")

xlApp.DisplayAlerts = False
' xlWorkBook = xlWorkSheet(1)

xlApp.Visible = False
c1 = 234
' Need the 12,19,19 to be replace by the Current day of the month myday but remed out statements do not work
xlApp.Cells(19 + offset, 2).Value = DateTime.Now
xlApp.Cells(19 + offset, 3).Value = 123 'mData0 old display value variable
xlApp.Cells(19 + offset, 4).Value = 234 'mData0 old display value variable
' xlApp.Cells(myDay + offset, 5).Value = 345 'mData0 old display value variable
' xlApp.Cells(myDay + offset, 6).Value = 456 'mData0 old display value variable

With xlApp.ActiveSheet
.Columns.AutoFit()
' .ColumnWidth = .ColumnWidth * 2.5
'.HorizontalAlignment = xlCenter
xlWorkBook.Saved = True
xlWorkBook.Close(SaveChanges:=True)
xlApp.Quit()
xlApp.DisplayAlerts = False

xlApp = Nothing
xlWorkBook = Nothing
xlWorkSheet = Nothing

End With

End Sub

Rapiant 10-09-2014 08:47 PM

You forgot to include xlWorkbook.save() in your code. if you don't want to override values you need to have your code look at the values in your cells in then find the first empty cell.

Dim EmptyRow As Long
EmptyRow = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row


All times are GMT -6. The time now is 01:15 AM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.