VB6 and Excel
in [Visual Basic]
|
Prev: Address parsing algorithm needed please
Next: DataGrid
From: Karl E. Peterson on 22 Apr 2010 16:00 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
From: MikeD on 22 Apr 2010 19:06 "Karl E. Peterson" <karl(a)exmvps.org> wrote in message news:#Wx2KZl4KHA.4520(a)TK2MSFTNGP02.phx.gbl... > 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. Why? I see no point in starting Excel separately in each event...especially since he's NOT quitting Excel in each event. -- Mike
From: Karl E. Peterson on 22 Apr 2010 19:23 MikeD wrote: > "Karl E. Peterson" <karl(a)exmvps.org> wrote... >> 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. > > Why? I see no point in starting Excel separately in each event... Utility functions. I don't know what he's planning to do with them. But I can sure see functions that are designed to be used independently of one another to have exactly that design. Especially if they're not used in an intensive manner. You also tie up resources by keeping the object alive. May or may not matter, of course. But it may. And having a module level variable of that sort just invites issues with improper clean-up, too. Higher odds of garbage being left behind. Lots of considerations. I'm just saying, it's not quite so black and white. In some situations, it'd certainly make sense to open and hold onto a single instance, as you suggest. > especially since he's NOT quitting Excel in each event. Well, yeah, assuming that sort of detail were handled, too. That's why I agreed with the rest of what you said. -- ..NET: It's About Trust! http://vfred.mvps.org
From: Stan Weiss on 23 Apr 2010 00:59 MikeD wrote: > > "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 > Thanks, other than a load and unload sub that was the whole program. I just put something together to work the bugs out. Mostly my bad typing. <lol> Now I can start to design the real program logic. Stan
From: Stan Weiss on 23 Apr 2010 10:26
After having more time and looking farther into this I have a problem with the create. It is creating 2 .xls files. The Stan_Test.xls and also a sheet1.xls. What do I need to do to have everything in one .xls file? What I plan on doing is --> replace - Open fNameO For Output As #fNoO --> with - Dim Excel As New Excel.Application Excel.Application.Workbooks.Add (True) --> replace - Building tab_rec Print #fNoO, Tab_Rec --> with - RowIndx = RowIndx +1 For ColIndx = 1 to 5 Excel.Cells(RowIndx, ColIndx).Value = Flds(ColIndx) Next --> replace - Close #fNoO --> with - Excel.Save (fNameO) Excel.Quit Thanks, Stan Stan Weiss wrote: > > 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 |