Getting rid of Macros?

[bklynman01]

Recently, I have been assigned the task of tracking training records used by multiple departments within my company. Before, they would fill out ISO Standard Forms that are built in MS Excel and save them in one folder. This folder held ALL of the documents used by multiple departments, training on multiple topics. The first assignment was to organize this, which I did by building macros in the forms to save these files in a document tree that was built dependent on some data from the form itself. Then they asked me for a program to make it easy to find these files, so I built a program that would populate a series of dropdown boxes that worked their way through the tree. Now they want to add a SQL database to track data from the sheet and add an archiving feature that checks the revision number of the form and moves it to another folder if there are higher revisions.

If they assigned all of this in the beginning, it would have been great. BUT... now I have about 75 documents spread across network locations with macros built into them, being tracked by a very simple program.

Is there (or has there been) a good method for doing all of this work WITHOUT macros. I'd like to get rid of them altogether, and not have to worry about updates not taking effect in all previously saved files. I found something called GemBox.Spreadsheet that would help manipulate data in Excel. Anyone ever heard of it?

I'm not looking for code samples right now, but more a discussion on some good ways to do this. A little brainstorming anyone?...

[lolafuertes]

I've been using the Grapecity Farpoint (http://www.fpoint.com/default.aspx) for many years

Hope this helps

[smellissa]

you can get better idea in this. use can apply micros its best for that

_________________
Barhocker:Hier finden Sie eine sehr große Auswahl an Barhocker. Internationaler Versand oder Abholung in Berlin.

[bklynman01]

I checked out Grapecity. It looks like a very powerful tool that would be useful. The only problem is the business end. The building I work in has just under 1000 people in it. I understand that excel and Spread can be used side-by-side, but the number of people that are not computer literate enough to understand this would cause an aneurysm. :)

I'm just looking to have my program read the information from the sheet, save that information in a database somewhere, save the sheet in a specific place (dependent on the data pulled from that sheet), and go back to waiting on the user. Right now, all it does is browse some folders looking for files that already exist.

I was able to find out, through various forums, that excel data is not easy to access using VB.net. I found a program called GemBox.Spreadsheet that is supposed to make it easy to work with excel. Ever hear of it?

This project is not my primary responsibility, just one that happened to be assigned. I'm not working day-in and day-out on this. I have time to learn new things to make this work.

[lolafuertes]

Never used gembox.

In order to read the information from the excel workbook, is enough to add the Microsoft.Office.Interop.Excel .NET component (12.0.0.0 for Excel 2007) reference to your poject. You do not need third party programs for that.

Assumming you know the name of the file, an you have a DataGridView, heeafter a function that opens the file and loads into the datagrid

Private Function OpenExcel(ByVal ExcelFileName As String, ByRef Dg As DataGridView) As Boolean
		'Declare the return value: true if loaded OK else false
		Dim ReturnValue As Boolean = False
		' Define the excel application
		Dim ExcelApp As Microsoft.Office.Interop.Excel.Application = Nothing
		' Define the Workbook
		Dim Wb As Microsoft.Office.Interop.Excel.Workbook = Nothing
		' Define the workseet
		Dim Ws As Microsoft.Office.Interop.Excel.Worksheet = Nothing
		'
		' Verify if the file name exists
		'
		Dim Fi As New IO.FileInfo(ExcelFileName)
		If Not Fi.Exists Then
			GoTo Exitfunction
		End If
		'
		'	instantiate the excel application
		'
		Try
			ExcelApp = New Microsoft.Office.Interop.Excel.Application
		Catch ex As Exception
			MsgBox("Unable to load Excel 2007", MsgBoxStyle.Critical)
			GoTo Exitfunction
		End Try
		Application.DoEvents()
		'
		' Open the workbook from the supllied file name
		'
		Try
			Wb = ExcelApp.Workbooks.Open(ExcelFileName)
		Catch ex As Exception
			MsgBox("Unable to open '" & ExcelFileName & "': " & ex.Message)
			GoTo Exitfunction
		End Try
		Application.DoEvents()
		'
		' Verify if this is a valid excel file
		'
		If Wb.Sheets.Count = 0 Then
			MsgBox("'" & ExcelFileName & "' is not a valid workbook for Excel 2007", MsgBoxStyle.Critical)
			GoTo Exitfunction
		End If
		Application.DoEvents()
		'
		' Get the first worksheet
		¡
		Ws = CType(Wb.Sheets(0), Microsoft.Office.Interop.Excel.Worksheet)
		'
		' Get the filled columns and rows
		'
		Dg.ColumnCount = Ws.Columns.Count
		Dg.RowCount = Ws.Rows.Count
		'
		'	Cile over the cells
		'
		For R As Integer = 0 To Ws.Rows.Count
			For C As Integer = 0 To Ws.Columns.Count
				'
				'	Populate the data grid view
				'
				Dg.Item(C, R).Value = CType(Ws.Cells(R, C), Microsoft.Office.Interop.Excel.Range).Text
				Application.DoEvents()
			Next
		Next
		'
		'	evryt cell is loaded
		'
		ReturnValue = True
		'
		' Close the workbook
		'
		Try
			ExcelApp.Workbooks.Close()
		Catch ex As Exception
		End Try
		Application.DoEvents()
		'
		' Close the excel
		'
		Try
			ExcelApp.Quit()
		Catch ex As Exception
		End Try
		Application.DoEvents()
Exitfunction:
		'
		' release the worksheet
		'
		If Not Ws Is Nothing Then
			Try
				Dim Rc As Integer = 1
				Do While Rc > 0
					Rc = Runtime.InteropServices.Marshal.ReleaseComObject(Ws)
					Application.DoEvents()
				Loop
				Wb = Nothing
			Catch ex As Exception
			End Try
		End If
		'
		' release the workbook
		'
		If Not Wb Is Nothing Then
			Try
				Dim Rc As Integer = 1
				Do While Rc > 0
					Rc = Runtime.InteropServices.Marshal.ReleaseComObject(Wb)
					Application.DoEvents()
				Loop
				Wb = Nothing
			Catch ex As Exception
			End Try
		End If
		'
		' and release the excel application
		'
		If Not ExcelApp Is Nothing Then
			Try
				Dim Rc As Integer = 1
				Do While Rc > 0
					Rc = Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp)
					Application.DoEvents()
				Loop
				Wb = Nothing
			Catch ex As Exception
			End Try
		End If
		'
		Return ReturnValue
	End Function

This is just an example on howto.

Then, moving the data from the datagrid to the database is up to you.
Or maybe, you can put every single row from excel direct to the DB, instead of the datagrid.

You have many choices for that.

Hope this helps.

[bklynman01]

lola,
This is excellent. I will play around with this and certainly give feedback.
You've heard of GemBox? May I ask why you say not to use it? I haven't tried it on a count of I could not find any reviews for it.

[lolafuertes]

Sorry, never heared of GemBox. I've seen on their web that you can download a limited in use trial/demo.

I just prefer to use the Microsoft standars when no special features of third party are needed, so evaluate if you need to spend money or not.

Hope this helps

[bklynman01]

Just today, I decided to play around with opening Excel inside a form within my program. I have succeeded in using a WebBrowser control. By using that, excel opens without the standard toolbars across the top. This forces the use of the buttons that I put in the window, such as save and print, thus allowing me full control of what they do with the documents. And, by using some of the code you put here in this thread, I am able to pull data from the sheet that they have entered, and use that to build my save directory. I feel the end of Macros coming! Well... maybe not completely, but I hope that this information helps anyone else who is trying to find a better way to control documents.

Let me know what you think of using this method about things. I am one of two people at my company that know anything about programming, so brainstorming is limited.

Thanks!