4 Replies - 21902 Views - Last Post: 25 May 2009 - 03:35 AM Rate Topic: -----

#1 jeff87  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 17-March 09

Export data into Excel Template...

Posted 22 May 2009 - 07:31 PM

hi guys...i make my textboxes to export data into an excel template....it works fine but there are few errors too..but i don't know how to fix it...here is my code.
 Dim objExcel As Object
		Dim objWorkBook As Object

		Dim objWorkSheet As Object

		objExcel = CreateObject("EXCEL.APPLICATION")
		objWorkBook = objExcel.Workbooks.Open(Application.StartupPath & "\" & "Book1.xlsx")

		objWorkSheet = objWorkBook.Worksheets("Sheet1")
		objWorkSheet.Range("F15,G15,H15,I15,J15,K15").Value = TextBox1.Text
		objWorkSheet.Range("F1,G1").Value = TextBox1.Text
		objWorkSheet.Range("F7,G7,H7,I7,J7,K7").Value = TextBox3.Text
		objWorkSheet.Range("G5").Value = TextBox42.Text
		objWorkSheet.Range("J1,K1").Value = TextBox43.Text
		objWorkSheet.Range("C19").Value = TextBox5.Text
		objWorkSheet.Range("H19").Value = TextBox6.Text
		objWorkSheet.Range("C20").Value = TextBox7.Text
		objWorkSheet.Range("H20").Value = TextBox8.Text
		objWorkSheet.Range("C21").Value = TextBox9.Text
		objWorkSheet.Range("H21").Value = TextBox10.Text



		objWorkBook.SaveAs(Application.StartupPath & "\" & "Book1.xlsx")

		objWorkBook.Close()

		objExcel = Nothing


for the objworkbook.SaveAs,it will always ask me to replace to file and i would like to use SAVE command...bu it not works..anything happen?sumore everytime i run this process,it will tell me the file is read-only while the setting doesn't shows that the file is read-only...and i have to go to task manager and end the EXCEL process by myself to run the process again...thx.

Is This A Good Question/Topic? 0
  • +

Replies To: Export data into Excel Template...

#2 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: Export data into Excel Template...

Posted 23 May 2009 - 12:34 AM

Check this Will Help you
Was This Post Helpful? 0
  • +
  • -

#3 jeff87  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 17-March 09

Re: Export data into Excel Template...

Posted 24 May 2009 - 06:04 PM

any others idea?coz i dun relly understand tat and i m doing tis on VB.net
Was This Post Helpful? 0
  • +
  • -

#4 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: Export data into Excel Template...

Posted 25 May 2009 - 03:20 AM

View Postjeff87, on 24 May, 2009 - 05:04 PM, said:

any others idea?coz i dun relly understand tat and i m doing tis on VB.net


Hope this will get you started

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.IO
Imports System.Globalization
Imports System.Collections
Imports System.Data
Imports System.Drawing
Imports System.Text
Imports System.Threading
Imports System.Diagnostics
Imports System.Windows.Forms
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
	''' <summary>
	''' 
	''' </summary>
	''' <param name="sender"></param>
	''' <param name="e"></param>
	''' <remarks></remarks>
	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		''added some dummy values:
		TextBox1.Text = TextBox1.Name.ToString()
		TextBox2.Text = TextBox2.Name.ToString()
		TextBox3.Text = TextBox3.Name.ToString()
		TextBox4.Text = TextBox4.Name.ToString()
		TextBox5.Text = TextBox5.Name.ToString()
		TextBox6.Text = TextBox6.Name.ToString()
		TextBox7.Text = TextBox7.Name.ToString()
		TextBox8.Text = TextBox8.Name.ToString()
		TextBox9.Text = TextBox9.Name.ToString()
		TextBox10.Text = TextBox10.Name.ToString()
	End Sub
	''' <summary>
	''' 
	''' </summary>
	''' <param name="sender"></param>
	''' <param name="e"></param>
	''' <remarks></remarks>
	Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
		Try
			Dim strFileName As String = "C:\ExcelTest.xls"

			Dim xlApp As Excel.Application = New Excel.ApplicationClass()
			Dim xlWorkBook As Excel.Workbook = Nothing
			Dim xlWorkSheet As Excel.Worksheet = Nothing
			Dim xlRange As Excel.Range = Nothing
			Dim misValue As Object = System.Reflection.Missing.Value

			'' * to open existing file *

			'xlWorkBook = xlApp.Workbooks.Open(strFileName, misValue, misValue, misValue _
			'								  , misValue, misValue, misValue, misValue _
			'								 , misValue, misValue, misValue, misValue _
			'								, misValue, misValue, misValue)

			'' * to add the new one *

			xlWorkBook = xlApp.Workbooks.Add() '(Excel.XlFileFormat.xlTemplate)
			xlWorkBook = xlApp.ActiveWorkbook
			xlWorkSheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)
			xlApp.ScreenUpdating = False
			xlApp.DisplayAlerts = False
			Try
				xlApp.ScreenUpdating = False
				'
				'	 * first way to write data to cell *
				'
				'Range "F15:K15"
				xlRange = CType(xlWorkSheet.Range(xlWorkSheet.Cells(15, 6), xlWorkSheet.Cells(15, 11)), Excel.Range)
				xlRange.Value2 = TextBox1.Text
				'Range "F1:G1"
				xlRange = CType(xlWorkSheet.Range(xlWorkSheet.Cells(1, 6), xlWorkSheet.Cells(1, 7)), Excel.Range)
				xlRange.Value2 = TextBox2.Text
				'Range "F7:K7"
				xlRange = CType(xlWorkSheet.Range(xlWorkSheet.Cells(7, 6), xlWorkSheet.Cells(7, 11)), Excel.Range)
				xlRange.Value2 = TextBox3.Text
				'Range "G5" (note: one row range)
				xlRange = CType(xlWorkSheet.Cells(5, 7), Excel.Range)
				xlRange.Value2 = TextBox4.Text
				'
				'	 * another way to write data to cell *
				'
				xlRange = CType(xlWorkSheet.Cells.Range("J1:K1"), Excel.Range)
				xlRange.Value2 = TextBox3.Text
				xlRange = CType(xlWorkSheet.Cells.Range("C19"), Excel.Range)
				xlRange.Value2 = TextBox5.Text
				xlRange = CType(xlWorkSheet.Cells.Range("H19"), Excel.Range)
				'
				'	 * and another one goes here *
				'
				xlRange.Value2 = TextBox6.Text
				xlRange = CType(xlWorkSheet.Range("C20"), Excel.Range)
				xlRange.Value2 = TextBox7.Text
				xlRange = CType(xlWorkSheet.Range("H20"), Excel.Range)
				xlRange.Value2 = TextBox8.Text
				xlRange = CType(xlWorkSheet.Range("C21"), Excel.Range)
				xlRange.Value2 = TextBox9.Text
				xlRange = CType(xlWorkSheet.Range("H21"), Excel.Range)
				xlRange.Value2 = TextBox10.Text

				xlWorkSheet.Columns.EntireColumn.AutoFit()
				xlApp.ScreenUpdating = True
				xlWorkBook.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
				xlWorkBook.Close(True, misValue, misValue)
				xlApp.Quit()
				MessageBox.Show("Data exported to " & strFileName)

			Catch ex As System.Exception

				MessageBox.Show(ex.Message & "\n\n=======  WRITE TO EXCEL ERROR:  ======\n\n" & _
							ex.StackTrace)

			Finally

				releaseObject(xlRange)
				releaseObject(xlWorkSheet)
				releaseObject(xlWorkBook)
				releaseObject(xlApp)

			End Try
		Catch exl As System.Exception

			MessageBox.Show(exl.Message & _
				"\n\n=======   ERROR TO ACESS EXCEL:  ======\n\n" & _
				exl.StackTrace)

		End Try
	End Sub
	'
	'					 * clean up *
	'
	Private Sub releaseObject(ByVal obj As Object)
		Try

			System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
			obj = Nothing

		Catch ex As System.Exception
			obj = Nothing
			MessageBox.Show("Clean Up Memory Error\n" + ex.ToString())
		Finally
			If (Not obj Is Nothing) Then
				Dim pos As Integer = GC.GetGeneration(obj)
				GC.Collect(pos)

			Else
				GC.Collect()
				GC.WaitForPendingFinalizers()
			End If
		End Try

	End Sub

...................

End Class


~'J'~

This post has been edited by fixo: 25 May 2009 - 03:22 AM

Was This Post Helpful? 1
  • +
  • -

#5 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: Export data into Excel Template...

Posted 25 May 2009 - 03:35 AM

Forgot to say about you need to add COM reference
to Microsoft.Excel XX.X Object Library and also
this working code tested on VS2005, MS Office 2003,
Windows XP

~'J'~
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1