VB6 and Excel
in [Visual Basic]
|
Prev: Address parsing algorithm needed please
Next: DataGrid
From: Bob Butler on 23 Apr 2010 10:32 "Stan Weiss" <srweiss(a)erols.com> wrote in message news:4BD1AE06.4A5FC0E1(a)erols.com... > 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) Dim oXL as excel.application Dim oWB as excel.workbook Dim oWS as excel.worksheet set oxl=new excel.application set owb=oxl.workbooks.add set ows=owb.worksheets(1) > --> replace - Building tab_rec > Print #fNoO, Tab_Rec > > --> with - RowIndx = RowIndx +1 > For ColIndx = 1 to 5 > Excel.Cells(RowIndx, ColIndx).Value = Flds(ColIndx) ows.cells(rowindx,colindx).value=flds(colindx) > Next > > > > --> replace - Close #fNoO > > --> with - Excel.Save (fNameO) > Excel.Quit set ows=nothing owb.save fNameO owb.close set owb=nothing if oxl.workbooks.count=0 then oxl.quit 'else ' oxl.visible=true end if set oxl=nothing > 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
From: Stan Weiss on 23 Apr 2010 11:30 Thanks Bob Stan Bob Butler wrote: > > "Stan Weiss" <srweiss(a)erols.com> wrote in message > news:4BD1AE06.4A5FC0E1(a)erols.com... > > 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) > > Dim oXL as excel.application > Dim oWB as excel.workbook > Dim oWS as excel.worksheet > set oxl=new excel.application > set owb=oxl.workbooks.add > set ows=owb.worksheets(1) > > > --> replace - Building tab_rec > > Print #fNoO, Tab_Rec > > > > --> with - RowIndx = RowIndx +1 > > For ColIndx = 1 to 5 > > Excel.Cells(RowIndx, ColIndx).Value = Flds(ColIndx) > > ows.cells(rowindx,colindx).value=flds(colindx) > > > Next > > > > > > > > --> replace - Close #fNoO > > > > --> with - Excel.Save (fNameO) > > Excel.Quit > > set ows=nothing > owb.save fNameO > owb.close > set owb=nothing > if oxl.workbooks.count=0 then > oxl.quit > 'else > ' oxl.visible=true > end if > set oxl=nothing > > > 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
From: Stan Weiss on 24 Apr 2010 14:34 The spec.'s keep changing. Now instead of saving the file and exiting the program I am letting the user go to work on the spreadsheet after I have populated some of the cells. This code works. Do you see any possible problems? oWS.Cells(21, 12) = "Cell 21 - 12 - 3" oXL.Visible = True 'Make Excel Visible oXL.UserControl = True 'Give the User Control Set oWS = Nothing ' oWB.SaveAs ("C:\Stan_Test.xls") ' oWB.Close Set oWB = Nothing ' oXL.Quit Set oXL = Nothing Stan Bob Butler wrote: > > "Stan Weiss" <srweiss(a)erols.com> wrote in message > news:4BD1AE06.4A5FC0E1(a)erols.com... > > 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) > > Dim oXL as excel.application > Dim oWB as excel.workbook > Dim oWS as excel.worksheet > set oxl=new excel.application > set owb=oxl.workbooks.add > set ows=owb.worksheets(1) > > > --> replace - Building tab_rec > > Print #fNoO, Tab_Rec > > > > --> with - RowIndx = RowIndx +1 > > For ColIndx = 1 to 5 > > Excel.Cells(RowIndx, ColIndx).Value = Flds(ColIndx) > > ows.cells(rowindx,colindx).value=flds(colindx) > > > Next > > > > > > > > --> replace - Close #fNoO > > > > --> with - Excel.Save (fNameO) > > Excel.Quit > > set ows=nothing > owb.save fNameO > owb.close > set owb=nothing > if oxl.workbooks.count=0 then > oxl.quit > 'else > ' oxl.visible=true > end if > set oxl=nothing > > > 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
From: MikeD on 25 Apr 2010 08:38 "Stan Weiss" <srweiss(a)erols.com> wrote in message news:4BD339C0.41110549(a)erols.com... > The spec.'s keep changing. Now instead of saving the file and exiting > the program I am letting the user go to work on the spreadsheet after I > have populated some of the cells. This code works. Do you see any > possible problems? > > oWS.Cells(21, 12) = "Cell 21 - 12 - 3" > > oXL.Visible = True 'Make Excel Visible > oXL.UserControl = True 'Give the User Control > > Set oWS = Nothing > ' oWB.SaveAs ("C:\Stan_Test.xls") > ' oWB.Close > Set oWB = Nothing > > ' oXL.Quit > Set oXL = Nothing > > Stan You might not want to set your references to Nothing unless/until you really no longer need them in your program. Of course, if they're local variables, they'll automatically be set to Nothing when the procedure goes out of scope. However, what you really need to do is test on several different end-user PCs. Sure, we might be able to point out this or that, but that shouldn't replace actual "real-world" testing. -- Mike
From: Peter T on 26 Apr 2010 06:37
"Stan Weiss" <srweiss(a)erols.com> wrote in message news:4BD339C0.41110549(a)erols.com... > The spec.'s keep changing. Now instead of saving the file and exiting > the program I am letting the user go to work on the spreadsheet after I > have populated some of the cells. This code works. Do you see any > possible problems? > > oWS.Cells(21, 12) = "Cell 21 - 12 - 3" > > oXL.Visible = True 'Make Excel Visible > oXL.UserControl = True 'Give the User Control > > Set oWS = Nothing > ' oWB.SaveAs ("C:\Stan_Test.xls") > ' oWB.Close > Set oWB = Nothing > > ' oXL.Quit > Set oXL = Nothing > > Stan Couple more thoughts - If the intention is ultimately to leave the workbook open and visible to the user, consider starting by attempting to reference an existing Excel instance the the user is already working with, eg on error resume next Set oXL = GetObject(, "excel.application") on error goto 0 ' or other handler if oXL is nothing then ' as you were doing before Else check if the app is visible, if not maybe it's been created for some other programatic needs (yours maybe). Typically though it'll be the user's working instance. might be an idea to check if your workbook is already open on error resume next set oWB = oXL.Workbooks("myFile.xlx") on error goto 0 Another thing to consider, if leaving the newly "created" instance open, personal.xls(m) will not have loaded and neither will have any addins. If their absence might confuse the user it'd be easy enough for your code to open them. Regards, Peter T |