VB6 and Excel
in [Visual Basic]
|
Prev: Address parsing algorithm needed please
Next: DataGrid
From: Stan Weiss on 22 Apr 2010 14:23 I am using VB6 - SP5 with Microsoft Office\Office\EXCEL9.OLB#Microsoft Excel 9.0 Object Library. I have been creating delimited files which I than imported in to Excel. I only needed to do this a couple of times a year. I am now looking at a project where this may need to be done a number of times a day. First try at creating and reading Excel files with VB6. Running in the IDE this works OK. Does anyone see any problems or have a better way of doing this. Private Sub Create_Excel_Click() Dim Excel As New Excel.Application Excel.Application.Workbooks.Add (True) Excel.Cells(1, 1).Value = "Bore" Excel.Cells(1, 2).Value = "4.03" Excel.Cells(2, 1).Value = "Stroke" Excel.Cells(2, 2).Value = "3.25" Excel.Save ("C:\Stan_Test.xls") End Sub Private Sub Read_Excel_Click() Dim Excel As New Excel.Application Dim sheet As Excel.Worksheet Dim wb As Excel.Workbook Set wb = Excel.Workbooks.Open("C:\Stan_Test.xls") Set sheet = wb.Sheets.Item(1) Text1.Text = sheet.Cells(1, 1) Text2.Text = sheet.Cells(1, 2) Text3.Text = sheet.Cells(2, 1) Text4.Text = sheet.Cells(1, 2) Excel.ActiveWorkbook.Close False, "c:\Stan_Test.xls" End Sub
From: GS on 22 Apr 2010 15:06 Hi Stan, The code you show here will work. However, having to open/close files numerous times will be an unecessary performance hit. If I understand you correctly, you are storing data in both delimited files AND Excel spreadsheets. Did you know that you can read/write both without having to open either file? VB[A] can do this using ADO. In the case of your delimited file, ADO treats it as a stand-alone data table. Each Excel spreadsheet is treated as a separate data table within the workbook. You can explore this with complete with documentation and project samples that demonstrate this in Excel using VBA. The code is usable in VB6 with some ref editing to the Excel object. Here's where you can download this project: http://www.appspro.com/conference/DatabaseProgramming.zip <FWIW>For some reason your name seems familiar. Regardless if I know you or not, your subject material is very familiar as I worked in the engine rebuilding industry as a machinist and machine fixture & specialty tool designer/maker for over 35 years. I have created several apps for use specifically within that industry. HTH Kind regards, Garry -- Stan Weiss brought next idea : > I am using VB6 - SP5 with Microsoft Office\Office\EXCEL9.OLB#Microsoft > Excel 9.0 Object Library. I have been creating delimited files which I > than imported in to Excel. I only needed to do this a couple of times a > year. I am now looking at a project where this may need to be done a > number of times a day. First try at creating and reading Excel files > with VB6. Running in the IDE this works OK. Does anyone see any problems > or have a better way of doing this. > > > Private Sub Create_Excel_Click() > > Dim Excel As New Excel.Application > > Excel.Application.Workbooks.Add (True) > > Excel.Cells(1, 1).Value = "Bore" > Excel.Cells(1, 2).Value = "4.03" > Excel.Cells(2, 1).Value = "Stroke" > Excel.Cells(2, 2).Value = "3.25" > > Excel.Save ("C:\Stan_Test.xls") > > End Sub > > Private Sub Read_Excel_Click() > > Dim Excel As New Excel.Application > Dim sheet As Excel.Worksheet > Dim wb As Excel.Workbook > > Set wb = Excel.Workbooks.Open("C:\Stan_Test.xls") > Set sheet = wb.Sheets.Item(1) > > Text1.Text = sheet.Cells(1, 1) > Text2.Text = sheet.Cells(1, 2) > Text3.Text = sheet.Cells(2, 1) > Text4.Text = sheet.Cells(1, 2) > > Excel.ActiveWorkbook.Close False, "c:\Stan_Test.xls" > > End Sub
From: Stan Weiss on 22 Apr 2010 15:18 Thanks Garry, This was just a little test program to test the basic operations. Up until now I had no need to checkout going directly to excel. Once I get this working there will not be anymore need for delimited files. <g> Stan GS wrote: > > Hi Stan, > > The code you show here will work. However, having to open/close files > numerous times will be an unecessary performance hit. > > If I understand you correctly, you are storing data in both delimited > files AND Excel spreadsheets. Did you know that you can read/write both > without having to open either file? VB[A] can do this using ADO. In the > case of your delimited file, ADO treats it as a stand-alone data table. > Each Excel spreadsheet is treated as a separate data table within the > workbook. > > You can explore this with complete with documentation and project > samples that demonstrate this in Excel using VBA. The code is usable in > VB6 with some ref editing to the Excel object. Here's where you can > download this project: > > http://www.appspro.com/conference/DatabaseProgramming.zip > > <FWIW>For some reason your name seems familiar. Regardless if I know > you or not, your subject material is very familiar as I worked in the > engine rebuilding industry as a machinist and machine fixture & > specialty tool designer/maker for over 35 years. I have created several > apps for use specifically within that industry. > > HTH > Kind regards, > Garry > -- > > Stan Weiss brought next idea : > > I am using VB6 - SP5 with Microsoft Office\Office\EXCEL9.OLB#Microsoft > > Excel 9.0 Object Library. I have been creating delimited files which I > > than imported in to Excel. I only needed to do this a couple of times a > > year. I am now looking at a project where this may need to be done a > > number of times a day. First try at creating and reading Excel files > > with VB6. Running in the IDE this works OK. Does anyone see any problems > > or have a better way of doing this. > > > > > > Private Sub Create_Excel_Click() > > > > Dim Excel As New Excel.Application > > > > Excel.Application.Workbooks.Add (True) > > > > Excel.Cells(1, 1).Value = "Bore" > > Excel.Cells(1, 2).Value = "4.03" > > Excel.Cells(2, 1).Value = "Stroke" > > Excel.Cells(2, 2).Value = "3.25" > > > > Excel.Save ("C:\Stan_Test.xls") > > > > End Sub > > > > Private Sub Read_Excel_Click() > > > > Dim Excel As New Excel.Application > > Dim sheet As Excel.Worksheet > > Dim wb As Excel.Workbook > > > > Set wb = Excel.Workbooks.Open("C:\Stan_Test.xls") > > Set sheet = wb.Sheets.Item(1) > > > > Text1.Text = sheet.Cells(1, 1) > > Text2.Text = sheet.Cells(1, 2) > > Text3.Text = sheet.Cells(2, 1) > > Text4.Text = sheet.Cells(1, 2) > > > > Excel.ActiveWorkbook.Close False, "c:\Stan_Test.xls" > > > > End Sub
From: MikeD on 22 Apr 2010 15:41 "Stan Weiss" <srweiss(a)erols.com> wrote in message news:4BD0942D.BAE10A47(a)erols.com... > I am using VB6 - SP5 with Microsoft Office\Office\EXCEL9.OLB#Microsoft > Excel 9.0 Object Library. I have been creating delimited files which I > than imported in to Excel. I only needed to do this a couple of times a > year. I am now looking at a project where this may need to be done a > number of times a day. First try at creating and reading Excel files > with VB6. Running in the IDE this works OK. Does anyone see any problems > or have a better way of doing this. > > > Private Sub Create_Excel_Click() > > Dim Excel As New Excel.Application > > Excel.Application.Workbooks.Add (True) > > Excel.Cells(1, 1).Value = "Bore" > Excel.Cells(1, 2).Value = "4.03" > Excel.Cells(2, 1).Value = "Stroke" > Excel.Cells(2, 2).Value = "3.25" > > Excel.Save ("C:\Stan_Test.xls") > > End Sub > > Private Sub Read_Excel_Click() > > Dim Excel As New Excel.Application > Dim sheet As Excel.Worksheet > Dim wb As Excel.Workbook > > Set wb = Excel.Workbooks.Open("C:\Stan_Test.xls") > Set sheet = wb.Sheets.Item(1) > > Text1.Text = sheet.Cells(1, 1) > Text2.Text = sheet.Cells(1, 2) > Text3.Text = sheet.Cells(2, 1) > Text4.Text = sheet.Cells(1, 2) > > Excel.ActiveWorkbook.Close False, "c:\Stan_Test.xls" > > End Sub I see a few things. 1. Why are you creating the Excel application object twice? Make that a module-level object variable and just create it once. 2. It's best to determine if Excel is already running and only CREATE an instance if it's not. Also, if you create an instance you should also quit that instance or it will remain running, perhaps unbeknownst to the user if you're not making the application visible. One way to accomplish this is as such: -----BEGIN CODE Option Explicit Private mbStartedExcel As Boolean Private moXLApp As Excel.Application Private Sub Form_Load() On Error GoTo EH 'set your standard error handling '<code> On Error Resume Next 'TEMPORARILY ignore errors Set moXLApp = GetObject(, "Excel.Application") On Error GoTo EH 'got back to standard error handling If moXLApp Is Nothing Then 'Create new instance of Excel Set moXLApp = New Excel.Application mbStartedExcel = True End If Exit Sub EH: End Sub Private Sub Form_Unload(Cancel As Integer) If mbStartedExcel Then moXLApp.Quit End If End Sub -----END CODE 3. Don't use the New keyword in your variable declarations. Instead, explicitly instantiate new objects. This goes for ALL objects you create, not just Excel. 4. Probably not a good idea to use "Excel" as your object variable since that's also the library name. 5. Many people recommend that you use late-binding instead of early-binding. This is particularly true if you don't know what version of Excel will be in use of the machine running your program. In my own experience, as long as you're developing and using an reference that is an earlier version of Excel than on the target machine, you should be OK with early-binding. 6. You might want to consider using ADO to read and create the .xls files. This would eliminate Excel having to be installed on the machine(s) running your app. I'm pretty sure you can use the Jet OLEDB provider for this, but I'm not positive. I do know you can use ADO though. -- Mike
From: Karl E. Peterson on 22 Apr 2010 15:59
MikeD wrote: > I see a few things. > > 1. Why are you creating the Excel application object twice? Make that a > module-level object variable and just create it once. Reasonable people might disagree on that. Especially in a testcase scenario like this. I liked the rest of your ideas, though. -- ..NET: It's About Trust! http://vfred.mvps.org |