Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic and Excel
I am using a Visual Basic application (which receives and presents serial
(text) data on a full-frame form in real time) and would like to add a line of code in the MSComm1 module so that the same serial data can be sent to a cell in an excel spreradsheet for simultaneous analysis. Is this possible? And what command structure is needed to write from one application (VB in a minimised window) to Excel (maximised)? Am able to post a portion of the MSComm1 code handling received data if the idea is sound. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic and Excel
Your idea should work. Here is the basic idea (no pun
intended!): Add a reference to the Microsoft Excel object model to your VB project. You can then create an object variable of the type Excel.Application to hold a Microsoft Excel session (which you could populate either by finding an existing open Excel session, if that is what you want to do, or you could start a new session through code). You can then use the object model to create the workbook/spreadsheet structure you need to do your analysis. Have your code put the value you have read into a cell in the workbook. Something like this: Dim XLApp as New Excel.Application, XLBook as Excel.Workbook ' To open an existing workbook from a file: Set XLBook = XLApp.Workbooks.Open FileName ' Or, to create a new workbook: Set XLBook = XLApp.Workbooks.Add ' Make the session visible so the user can view and interact with it: XLApp.Visible = True ' Code here would set up ranges, formulas, formats, etc... needed on the worksheet for processing your input ' Assuming you then want to take your serial data you have read (I will give it the variable name SerialData) and put it in cell B2 on Sheet1, you could write: XLBook.Sheets("Sheet1").Range("B2").Value = SerialData 'etc... ' To clean everythig up at the end: XLBook.Close XLApp.Quit Set XLBook = Nothing Set XLApp = Nothing Just an outline, but should give an idea of how this works - check the MSDN library for Excel programming info to get more details of how to use the Excel object model in VB. K Dales -----Original Message----- I am using a Visual Basic application (which receives and presents serial (text) data on a full-frame form in real time) and would like to add a line of code in the MSComm1 module so that the same serial data can be sent to a cell in an excel spreradsheet for simultaneous analysis. Is this possible? And what command structure is needed to write from one application (VB in a minimised window) to Excel (maximised)? Am able to post a portion of the MSComm1 code handling received data if the idea is sound. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic and Excel
Thanks for this encouragement. Although I am comfortable programming Excel
VBA, I am unfamiliar with Visual Basic and am hoping to achieve my objective with minimal alterations to an existing Visual Basic project. I am pasting below, part of the MSComm1_OnComm() Procedure (which handles a variety of serial commands), and I would initially hope to add a line of the form XLBook.Sheets("Sheet1").Range("B2").Value = dummy, perhaps before the initial Select Case dummy statement (line 3 of Private Sub MSComm1_OnComm()). I want to open an existing spreadsheet "C:\Book1.xls" to receive and analyse the data, but remain unsure where the statements you suggest should be placed:. Dim XLApp as New Excel.Application, XLBook as Excel.Workbook Set XLBook = XLApp.Workbooks.Open "C:\Book1.xls" XLApp.Visible = True "K Dales" wrote in message ... Your idea should work. Here is the basic idea (no pun intended!): Add a reference to the Microsoft Excel object model to your VB project. You can then create an object variable of the type Excel.Application to hold a Microsoft Excel session (which you could populate either by finding an existing open Excel session, if that is what you want to do, or you could start a new session through code). You can then use the object model to create the workbook/spreadsheet structure you need to do your analysis. Have your code put the value you have read into a cell in the workbook. Something like this: Dim XLApp as New Excel.Application, XLBook as Excel.Workbook ' To open an existing workbook from a file: Set XLBook = XLApp.Workbooks.Open FileName ' Or, to create a new workbook: Set XLBook = XLApp.Workbooks.Add ' Make the session visible so the user can view and interact with it: XLApp.Visible = True ' Code here would set up ranges, formulas, formats, etc... needed on the worksheet for processing your input ' Assuming you then want to take your serial data you have read (I will give it the variable name SerialData) and put it in cell B2 on Sheet1, you could write: XLBook.Sheets("Sheet1").Range("B2").Value = SerialData 'etc... ' To clean everythig up at the end: XLBook.Close XLApp.Quit Set XLBook = Nothing Set XLApp = Nothing Just an outline, but should give an idea of how this works - check the MSDN library for Excel programming info to get more details of how to use the Excel object model in VB. K Dales . Private Sub MSComm1_OnComm() Do dummy = MSComm1.Input Select Case dummy Case "" ' do nothing for null characters nullchar = True Case Chr$(STX) BufPoint = 1 InBuffer$(BufPoint) = dummy Case Chr$(ETX) BufPoint = BufPoint + 1 InBuffer$(BufPoint) = dummy Select Case InBuffer$(2) Case "O" ' Question Number getch$ = "" getpoint = 3 Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" QstNumb = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" If FinalRound = True Then If Len(Overall$) < 5 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, Len(Overall$) - 1) + ")" Else QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 1) + "," + Right$(Overall$, 3) + ")" End If Else QuestNumb.Text = "Question : " + QstNumb End If QuestBox.Text = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < Chr$(ETX) Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = Chr$(ETX) AnswerBox.Text = getch$ BufPoint = 0 Case "B" ' get bank getch$ = "" For I = 3 To BufPoint - 1 getch$ = getch$ + InBuffer$(I) Next bankedinf.Text = Str(Val(getch$)) ChainTxt(0).Caption = Str(Val(getch$)) BufPoint = 0 Case "H" ' set clock ClockInf.Text = InBuffer$(3) + InBuffer$(4) + InBuffer$(5) + InBuffer$(6) Case "I" ' set round If InBuffer$(3) = 0 Then FinalRound = False QuestBox.Left = 1560 QuestBox.Width = 10455 QuestNumb.Left = 1560 QuestNumb.Width = 10455 AnswerBox.Left = 1560 AnswerBox.Width = 10455 Else FinalRound = True QuestBox.Left = 0 QuestBox.Width = 12015 QuestNumb.Left = 0 QuestNumb.Width = 12015 AnswerBox.Left = 0 AnswerBox.Width = 12015 End If Call SortForm Case "J" ' contestant names getch$ = "" getpoint = 3 Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" Name1.Text = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" Name2.Text = getch$ Case "K" ' show correct option getpoint = 3 For qst = 0 To 5 For bod = 1 To 2 FinalRes(qst, bod) = Val(InBuffer$(getpoint)) getpoint = getpoint + 1 Next Next Call SortQst Case "L" FinalQst = Val(InBuffer$(3) - 1) Call SortQst Case "N" Overall$ = Str$(Val(InBuffer$(3) + InBuffer$(4) + InBuffer$(5) + InBuffer$(6) + InBuffer$(7))) If FinalRound = True Then If Len(Overall$) < 5 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, Len(Overall$) - 1) + ")" Else If Len(Overall$) <= 6 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 1) + "," + Right$(Overall$, 3) + ")" Else QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 2) + "," + Right$(Overall$, 3) + ")" End If End If End If Case "Z" ' set question font getpoint = 3 ftsize$ = "" Do If InBuffer$(getpoint) < ":" Then ftsize$ = ftsize$ + InBuffer$(getpoint) End If getpoint = getpoint + 1 Loop Until InBuffer$(getpoint) = ":" QuestBox.FontSize = Format(ftsize) ftsize$ = "" Do If InBuffer$(getpoint) < ":" Then ftsize$ = ftsize$ + InBuffer$(getpoint) End If getpoint = getpoint + 1 Loop Until InBuffer$(getpoint) = ":" AnswerBox.FontSize = Format(ftsize$) BufPoint = 0 Case Else BufPoint = 0 End Select BufPoint = 0 Case Else BufPoint = BufPoint + 1 InBuffer$(BufPoint) = dummy End Select Loop Until dummy = "" End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic and Excel
Rather than give you a straightforward answer ("MY"
answer), I will try to help you decide how to handle it yourself (but will also give my recommendations). Basically, here's what happens: At the point you create an Excel.Application object, it is the same thing as starting up a new Excel session, i.e. it is as if you had gone to your Start menu and chosen to start Excel. The only difference is it happens behind the scenes and, also, is invisible for now. You would see an EXCEL process in the Windows Task Manager but will not see anything on screen until you set the Visible property to True. You can start Excel when you first start running your module by using the line: Dim XLApp as New Excel.Application This not only defines XLApp as an Excel.Application object but creates a new instance of it; i.e. starts it. Alternatively, you can hold off on starting Excel until you actually need to use it by defining the object variable and then, later in code, actually activating it: Dim XLApp as Excel.Application .... .... Set XLApp = New Excel.Application Why would it make a difference? Well, first, at the point where you create you actually call on Excel to start it will take some time: it has to read your hard drive and load and initialize Excel. Also, if you do this inside a loop, with all that Excel starting up and shutting down, you can end up slowing down your computer - and if you happen to forget to close the session you run the risk of having multiple invisible Excel sessions running in the background!!! But then, Excel will use some overhead - memory and processor cycles - so you may want to hold off on opening it until you really have to. In short, it is up to you where to actually put the New.Excel.Application declaration that will start Excel - just activate it sometime before you need to use it and make sure you are not inadvertently doing it inside a loop! Once you have that Excel application you can either create a new workbook (by the Set XLBook = XLApp.Workbooks.Add statement) or you can open an existing file (with Set XLBook = XLApp.Workbooks.Open "C:\Book1.xls". This is no different than what you would do if you were manually controlling Excel, you are just doing it through code. Since it sounds like you will be using the same workbook structure over and over, the Open option seems best to me (otherwise you need to create your workbook with all its cell values and formats and formulas through code every time you run your module). Also, rather than repeatedly opening and closing Excel for every new 'dummy' value, it probably makes more sense to create your Excel session att he beginning of the module, then just set it visible or invisible as needed while the rest of your code runs. Once you have your Excel session and workbook open you can do pretty much anything you can do with Excel in VBA, with all the same Objects and Methods, by referring to XLApp or XLBook. So this is where you can set the value of the cell in the workbook to be equal to your 'dummy' and do any further processing you want to do. At this stage you probably also want to make the session visible to the user, so set XLApp.Visible to True and there will be the Excel session you created. Finally, when everything is done you need to clean things up. This means: 1) Close your workbook and save it if necessary 2) Exit the Excel session and clear Excel out of your computer's memory 3) Any object variables you declared (e.g. XLApp, XLBook) have memory and resources assigned to them; clean this up by setting them equal to "Nothing." If you don't do this housekeeping it may not be noticeable, but it is at the least poor practice and wasteful of computer resources - at worst you can crash your system. So, finally, my suggestions: - use the Dim XLApp as New Excel.Application at the beginning of your code - also at the beginning of your code, after all variables are defined, open the PRE-BUILT workbook "C:\Book1.xls" (you will need to set this up and have it saved before running your code). This would be the line Set XLBook = XLApp.WOrkbooks.Open "C:\Book1.xls" - Set XLApp visible at the point you want the user to become aware of it - Now all you need to do is to grab your 'dummy' variable at the appropriate point (as you suggest, just before the Select Case might be good) and put it in the desired cell. - As soon as possible, i.e. as soon as your need for Excel is gone, close everything and set the variables to Nothing as described above. I hope that does it... K Dales -----Original Message----- Thanks for this encouragement. Although I am comfortable programming Excel VBA, I am unfamiliar with Visual Basic and am hoping to achieve my objective with minimal alterations to an existing Visual Basic project. I am pasting below, part of the MSComm1_OnComm() Procedure (which handles a variety of serial commands), and I would initially hope to add a line of the form XLBook.Sheets("Sheet1").Range("B2").Value = dummy, perhaps before the initial Select Case dummy statement (line 3 of Private Sub MSComm1_OnComm()). I want to open an existing spreadsheet "C:\Book1.xls" to receive and analyse the data, but remain unsure where the statements you suggest should be placed:. Dim XLApp as New Excel.Application, XLBook as Excel.Workbook Set XLBook = XLApp.Workbooks.Open "C:\Book1.xls" XLApp.Visible = True "K Dales" wrote in message ... Your idea should work. Here is the basic idea (no pun intended!): Add a reference to the Microsoft Excel object model to your VB project. You can then create an object variable of the type Excel.Application to hold a Microsoft Excel session (which you could populate either by finding an existing open Excel session, if that is what you want to do, or you could start a new session through code). You can then use the object model to create the workbook/spreadsheet structure you need to do your analysis. Have your code put the value you have read into a cell in the workbook. Something like this: Dim XLApp as New Excel.Application, XLBook as Excel.Workbook ' To open an existing workbook from a file: Set XLBook = XLApp.Workbooks.Open FileName ' Or, to create a new workbook: Set XLBook = XLApp.Workbooks.Add ' Make the session visible so the user can view and interact with it: XLApp.Visible = True ' Code here would set up ranges, formulas, formats, etc... needed on the worksheet for processing your input ' Assuming you then want to take your serial data you have read (I will give it the variable name SerialData) and put it in cell B2 on Sheet1, you could write: XLBook.Sheets("Sheet1").Range("B2").Value = SerialData 'etc... ' To clean everythig up at the end: XLBook.Close XLApp.Quit Set XLBook = Nothing Set XLApp = Nothing Just an outline, but should give an idea of how this works - check the MSDN library for Excel programming info to get more details of how to use the Excel object model in VB. K Dales . Private Sub MSComm1_OnComm() Do dummy = MSComm1.Input Select Case dummy Case "" ' do nothing for null characters nullchar = True Case Chr$(STX) BufPoint = 1 InBuffer$(BufPoint) = dummy Case Chr$(ETX) BufPoint = BufPoint + 1 InBuffer$(BufPoint) = dummy Select Case InBuffer$(2) Case "O" ' Question Number getch$ = "" getpoint = 3 Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" QstNumb = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" If FinalRound = True Then If Len(Overall$) < 5 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, Len (Overall$) - 1) + ")" Else QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 1) + "," + Right$(Overall$, 3) + ")" End If Else QuestNumb.Text = "Question : " + QstNumb End If QuestBox.Text = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < Chr$(ETX) Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = Chr$(ETX) AnswerBox.Text = getch$ BufPoint = 0 Case "B" ' get bank getch$ = "" For I = 3 To BufPoint - 1 getch$ = getch$ + InBuffer$(I) Next bankedinf.Text = Str(Val(getch$)) ChainTxt(0).Caption = Str(Val (getch$)) BufPoint = 0 Case "H" ' set clock ClockInf.Text = InBuffer$(3) + InBuffer$(4) + InBuffer$(5) + InBuffer$(6) Case "I" ' set round If InBuffer$(3) = 0 Then FinalRound = False QuestBox.Left = 1560 QuestBox.Width = 10455 QuestNumb.Left = 1560 QuestNumb.Width = 10455 AnswerBox.Left = 1560 AnswerBox.Width = 10455 Else FinalRound = True QuestBox.Left = 0 QuestBox.Width = 12015 QuestNumb.Left = 0 QuestNumb.Width = 12015 AnswerBox.Left = 0 AnswerBox.Width = 12015 End If Call SortForm Case "J" ' contestant names getch$ = "" getpoint = 3 Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" Name1.Text = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" Name2.Text = getch$ Case "K" ' show correct option getpoint = 3 For qst = 0 To 5 For bod = 1 To 2 FinalRes(qst, bod) = Val(InBuffer$(getpoint)) getpoint = getpoint + 1 Next Next Call SortQst Case "L" FinalQst = Val(InBuffer$(3) - 1) Call SortQst Case "N" Overall$ = Str$(Val(InBuffer$(3) + InBuffer$(4) + InBuffer$(5) + InBuffer$(6) + InBuffer$(7))) If FinalRound = True Then If Len(Overall$) < 5 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, Len (Overall$) - 1) + ")" Else If Len(Overall$) <= 6 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 1) + "," + Right$(Overall$, 3) + ")" Else QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 2) + "," + Right$(Overall$, 3) + ")" End If End If End If Case "Z" ' set question font getpoint = 3 ftsize$ = "" Do If InBuffer$(getpoint) < ":" Then ftsize$ = ftsize$ + InBuffer$(getpoint) End If getpoint = getpoint + 1 Loop Until InBuffer$(getpoint) = ":" QuestBox.FontSize = Format(ftsize) ftsize$ = "" Do If InBuffer$(getpoint) < ":" Then ftsize$ = ftsize$ + InBuffer$(getpoint) End If getpoint = getpoint + 1 Loop Until InBuffer$(getpoint) = ":" AnswerBox.FontSize = Format (ftsize$) BufPoint = 0 Case Else BufPoint = 0 End Select BufPoint = 0 Case Else BufPoint = BufPoint + 1 InBuffer$(BufPoint) = dummy End Select Loop Until dummy = "" End Sub . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic and Excel
Dim XLApp as New Excel.Application This not only defines XLApp as an Excel.Application object but creates a new instance of it; i.e. starts it. Technically, this is not true. Excel isn't started when the Dim statement is processed. The Excel application isn't actually started until the first reference in code (not a Dim statement) to the object is encountered. Using the New keyword creates what is called an "auto-instancing" variable. When the VBA code is compiled, VBA actually creates code that, if it were written in VBA, would look like If XLApp Is Nothing Then Set XLApp = New Excel.Application End If It creates this code just before *every* reference to XLApp. The generally prefered method is to avoid auto-instancing variables and use Set/New to create the instance of the object a the appropriate point in your code. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "K Dales" wrote in message ... Rather than give you a straightforward answer ("MY" answer), I will try to help you decide how to handle it yourself (but will also give my recommendations). Basically, here's what happens: At the point you create an Excel.Application object, it is the same thing as starting up a new Excel session, i.e. it is as if you had gone to your Start menu and chosen to start Excel. The only difference is it happens behind the scenes and, also, is invisible for now. You would see an EXCEL process in the Windows Task Manager but will not see anything on screen until you set the Visible property to True. You can start Excel when you first start running your module by using the line: Dim XLApp as New Excel.Application This not only defines XLApp as an Excel.Application object but creates a new instance of it; i.e. starts it. Alternatively, you can hold off on starting Excel until you actually need to use it by defining the object variable and then, later in code, actually activating it: Dim XLApp as Excel.Application .... .... Set XLApp = New Excel.Application Why would it make a difference? Well, first, at the point where you create you actually call on Excel to start it will take some time: it has to read your hard drive and load and initialize Excel. Also, if you do this inside a loop, with all that Excel starting up and shutting down, you can end up slowing down your computer - and if you happen to forget to close the session you run the risk of having multiple invisible Excel sessions running in the background!!! But then, Excel will use some overhead - memory and processor cycles - so you may want to hold off on opening it until you really have to. In short, it is up to you where to actually put the New.Excel.Application declaration that will start Excel - just activate it sometime before you need to use it and make sure you are not inadvertently doing it inside a loop! Once you have that Excel application you can either create a new workbook (by the Set XLBook = XLApp.Workbooks.Add statement) or you can open an existing file (with Set XLBook = XLApp.Workbooks.Open "C:\Book1.xls". This is no different than what you would do if you were manually controlling Excel, you are just doing it through code. Since it sounds like you will be using the same workbook structure over and over, the Open option seems best to me (otherwise you need to create your workbook with all its cell values and formats and formulas through code every time you run your module). Also, rather than repeatedly opening and closing Excel for every new 'dummy' value, it probably makes more sense to create your Excel session att he beginning of the module, then just set it visible or invisible as needed while the rest of your code runs. Once you have your Excel session and workbook open you can do pretty much anything you can do with Excel in VBA, with all the same Objects and Methods, by referring to XLApp or XLBook. So this is where you can set the value of the cell in the workbook to be equal to your 'dummy' and do any further processing you want to do. At this stage you probably also want to make the session visible to the user, so set XLApp.Visible to True and there will be the Excel session you created. Finally, when everything is done you need to clean things up. This means: 1) Close your workbook and save it if necessary 2) Exit the Excel session and clear Excel out of your computer's memory 3) Any object variables you declared (e.g. XLApp, XLBook) have memory and resources assigned to them; clean this up by setting them equal to "Nothing." If you don't do this housekeeping it may not be noticeable, but it is at the least poor practice and wasteful of computer resources - at worst you can crash your system. So, finally, my suggestions: - use the Dim XLApp as New Excel.Application at the beginning of your code - also at the beginning of your code, after all variables are defined, open the PRE-BUILT workbook "C:\Book1.xls" (you will need to set this up and have it saved before running your code). This would be the line Set XLBook = XLApp.WOrkbooks.Open "C:\Book1.xls" - Set XLApp visible at the point you want the user to become aware of it - Now all you need to do is to grab your 'dummy' variable at the appropriate point (as you suggest, just before the Select Case might be good) and put it in the desired cell. - As soon as possible, i.e. as soon as your need for Excel is gone, close everything and set the variables to Nothing as described above. I hope that does it... K Dales -----Original Message----- Thanks for this encouragement. Although I am comfortable programming Excel VBA, I am unfamiliar with Visual Basic and am hoping to achieve my objective with minimal alterations to an existing Visual Basic project. I am pasting below, part of the MSComm1_OnComm() Procedure (which handles a variety of serial commands), and I would initially hope to add a line of the form XLBook.Sheets("Sheet1").Range("B2").Value = dummy, perhaps before the initial Select Case dummy statement (line 3 of Private Sub MSComm1_OnComm()). I want to open an existing spreadsheet "C:\Book1.xls" to receive and analyse the data, but remain unsure where the statements you suggest should be placed:. Dim XLApp as New Excel.Application, XLBook as Excel.Workbook Set XLBook = XLApp.Workbooks.Open "C:\Book1.xls" XLApp.Visible = True "K Dales" wrote in message ... Your idea should work. Here is the basic idea (no pun intended!): Add a reference to the Microsoft Excel object model to your VB project. You can then create an object variable of the type Excel.Application to hold a Microsoft Excel session (which you could populate either by finding an existing open Excel session, if that is what you want to do, or you could start a new session through code). You can then use the object model to create the workbook/spreadsheet structure you need to do your analysis. Have your code put the value you have read into a cell in the workbook. Something like this: Dim XLApp as New Excel.Application, XLBook as Excel.Workbook ' To open an existing workbook from a file: Set XLBook = XLApp.Workbooks.Open FileName ' Or, to create a new workbook: Set XLBook = XLApp.Workbooks.Add ' Make the session visible so the user can view and interact with it: XLApp.Visible = True ' Code here would set up ranges, formulas, formats, etc... needed on the worksheet for processing your input ' Assuming you then want to take your serial data you have read (I will give it the variable name SerialData) and put it in cell B2 on Sheet1, you could write: XLBook.Sheets("Sheet1").Range("B2").Value = SerialData 'etc... ' To clean everythig up at the end: XLBook.Close XLApp.Quit Set XLBook = Nothing Set XLApp = Nothing Just an outline, but should give an idea of how this works - check the MSDN library for Excel programming info to get more details of how to use the Excel object model in VB. K Dales . Private Sub MSComm1_OnComm() Do dummy = MSComm1.Input Select Case dummy Case "" ' do nothing for null characters nullchar = True Case Chr$(STX) BufPoint = 1 InBuffer$(BufPoint) = dummy Case Chr$(ETX) BufPoint = BufPoint + 1 InBuffer$(BufPoint) = dummy Select Case InBuffer$(2) Case "O" ' Question Number getch$ = "" getpoint = 3 Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" QstNumb = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" If FinalRound = True Then If Len(Overall$) < 5 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, Len (Overall$) - 1) + ")" Else QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 1) + "," + Right$(Overall$, 3) + ")" End If Else QuestNumb.Text = "Question : " + QstNumb End If QuestBox.Text = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < Chr$(ETX) Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = Chr$(ETX) AnswerBox.Text = getch$ BufPoint = 0 Case "B" ' get bank getch$ = "" For I = 3 To BufPoint - 1 getch$ = getch$ + InBuffer$(I) Next bankedinf.Text = Str(Val(getch$)) ChainTxt(0).Caption = Str(Val (getch$)) BufPoint = 0 Case "H" ' set clock ClockInf.Text = InBuffer$(3) + InBuffer$(4) + InBuffer$(5) + InBuffer$(6) Case "I" ' set round If InBuffer$(3) = 0 Then FinalRound = False QuestBox.Left = 1560 QuestBox.Width = 10455 QuestNumb.Left = 1560 QuestNumb.Width = 10455 AnswerBox.Left = 1560 AnswerBox.Width = 10455 Else FinalRound = True QuestBox.Left = 0 QuestBox.Width = 12015 QuestNumb.Left = 0 QuestNumb.Width = 12015 AnswerBox.Left = 0 AnswerBox.Width = 12015 End If Call SortForm Case "J" ' contestant names getch$ = "" getpoint = 3 Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" Name1.Text = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" Name2.Text = getch$ Case "K" ' show correct option getpoint = 3 For qst = 0 To 5 For bod = 1 To 2 FinalRes(qst, bod) = Val(InBuffer$(getpoint)) getpoint = getpoint + 1 Next Next Call SortQst Case "L" FinalQst = Val(InBuffer$(3) - 1) Call SortQst Case "N" Overall$ = Str$(Val(InBuffer$(3) + InBuffer$(4) + InBuffer$(5) + InBuffer$(6) + InBuffer$(7))) If FinalRound = True Then If Len(Overall$) < 5 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, Len (Overall$) - 1) + ")" Else If Len(Overall$) <= 6 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 1) + "," + Right$(Overall$, 3) + ")" Else QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 2) + "," + Right$(Overall$, 3) + ")" End If End If End If Case "Z" ' set question font getpoint = 3 ftsize$ = "" Do If InBuffer$(getpoint) < ":" Then ftsize$ = ftsize$ + InBuffer$(getpoint) End If getpoint = getpoint + 1 Loop Until InBuffer$(getpoint) = ":" QuestBox.FontSize = Format(ftsize) ftsize$ = "" Do If InBuffer$(getpoint) < ":" Then ftsize$ = ftsize$ + InBuffer$(getpoint) End If getpoint = getpoint + 1 Loop Until InBuffer$(getpoint) = ":" AnswerBox.FontSize = Format (ftsize$) BufPoint = 0 Case Else BufPoint = 0 End Select BufPoint = 0 Case Else BufPoint = BufPoint + 1 InBuffer$(BufPoint) = dummy End Select Loop Until dummy = "" End Sub . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic and Excel
Thanks Chip - new info for me.
-----Original Message----- Dim XLApp as New Excel.Application This not only defines XLApp as an Excel.Application object but creates a new instance of it; i.e. starts it. Technically, this is not true. Excel isn't started when the Dim statement is processed. The Excel application isn't actually started until the first reference in code (not a Dim statement) to the object is encountered. Using the New keyword creates what is called an "auto-instancing" variable. When the VBA code is compiled, VBA actually creates code that, if it were written in VBA, would look like If XLApp Is Nothing Then Set XLApp = New Excel.Application End If It creates this code just before *every* reference to XLApp. The generally prefered method is to avoid auto-instancing variables and use Set/New to create the instance of the object a the appropriate point in your code. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "K Dales" wrote in message ... Rather than give you a straightforward answer ("MY" answer), I will try to help you decide how to handle it yourself (but will also give my recommendations). Basically, here's what happens: At the point you create an Excel.Application object, it is the same thing as starting up a new Excel session, i.e. it is as if you had gone to your Start menu and chosen to start Excel. The only difference is it happens behind the scenes and, also, is invisible for now. You would see an EXCEL process in the Windows Task Manager but will not see anything on screen until you set the Visible property to True. You can start Excel when you first start running your module by using the line: Dim XLApp as New Excel.Application This not only defines XLApp as an Excel.Application object but creates a new instance of it; i.e. starts it. Alternatively, you can hold off on starting Excel until you actually need to use it by defining the object variable and then, later in code, actually activating it: Dim XLApp as Excel.Application .... .... Set XLApp = New Excel.Application Why would it make a difference? Well, first, at the point where you create you actually call on Excel to start it will take some time: it has to read your hard drive and load and initialize Excel. Also, if you do this inside a loop, with all that Excel starting up and shutting down, you can end up slowing down your computer - and if you happen to forget to close the session you run the risk of having multiple invisible Excel sessions running in the background!!! But then, Excel will use some overhead - memory and processor cycles - so you may want to hold off on opening it until you really have to. In short, it is up to you where to actually put the New.Excel.Application declaration that will start Excel - just activate it sometime before you need to use it and make sure you are not inadvertently doing it inside a loop! Once you have that Excel application you can either create a new workbook (by the Set XLBook = XLApp.Workbooks.Add statement) or you can open an existing file (with Set XLBook = XLApp.Workbooks.Open "C:\Book1.xls". This is no different than what you would do if you were manually controlling Excel, you are just doing it through code. Since it sounds like you will be using the same workbook structure over and over, the Open option seems best to me (otherwise you need to create your workbook with all its cell values and formats and formulas through code every time you run your module). Also, rather than repeatedly opening and closing Excel for every new 'dummy' value, it probably makes more sense to create your Excel session att he beginning of the module, then just set it visible or invisible as needed while the rest of your code runs. Once you have your Excel session and workbook open you can do pretty much anything you can do with Excel in VBA, with all the same Objects and Methods, by referring to XLApp or XLBook. So this is where you can set the value of the cell in the workbook to be equal to your 'dummy' and do any further processing you want to do. At this stage you probably also want to make the session visible to the user, so set XLApp.Visible to True and there will be the Excel session you created. Finally, when everything is done you need to clean things up. This means: 1) Close your workbook and save it if necessary 2) Exit the Excel session and clear Excel out of your computer's memory 3) Any object variables you declared (e.g. XLApp, XLBook) have memory and resources assigned to them; clean this up by setting them equal to "Nothing." If you don't do this housekeeping it may not be noticeable, but it is at the least poor practice and wasteful of computer resources - at worst you can crash your system. So, finally, my suggestions: - use the Dim XLApp as New Excel.Application at the beginning of your code - also at the beginning of your code, after all variables are defined, open the PRE-BUILT workbook "C:\Book1.xls" (you will need to set this up and have it saved before running your code). This would be the line Set XLBook = XLApp.WOrkbooks.Open "C:\Book1.xls" - Set XLApp visible at the point you want the user to become aware of it - Now all you need to do is to grab your 'dummy' variable at the appropriate point (as you suggest, just before the Select Case might be good) and put it in the desired cell. - As soon as possible, i.e. as soon as your need for Excel is gone, close everything and set the variables to Nothing as described above. I hope that does it... K Dales -----Original Message----- Thanks for this encouragement. Although I am comfortable programming Excel VBA, I am unfamiliar with Visual Basic and am hoping to achieve my objective with minimal alterations to an existing Visual Basic project. I am pasting below, part of the MSComm1_OnComm() Procedure (which handles a variety of serial commands), and I would initially hope to add a line of the form XLBook.Sheets("Sheet1").Range("B2").Value = dummy, perhaps before the initial Select Case dummy statement (line 3 of Private Sub MSComm1_OnComm()). I want to open an existing spreadsheet "C:\Book1.xls" to receive and analyse the data, but remain unsure where the statements you suggest should be placed:. Dim XLApp as New Excel.Application, XLBook as Excel.Workbook Set XLBook = XLApp.Workbooks.Open "C:\Book1.xls" XLApp.Visible = True "K Dales" wrote in message ... Your idea should work. Here is the basic idea (no pun intended!): Add a reference to the Microsoft Excel object model to your VB project. You can then create an object variable of the type Excel.Application to hold a Microsoft Excel session (which you could populate either by finding an existing open Excel session, if that is what you want to do, or you could start a new session through code). You can then use the object model to create the workbook/spreadsheet structure you need to do your analysis. Have your code put the value you have read into a cell in the workbook. Something like this: Dim XLApp as New Excel.Application, XLBook as Excel.Workbook ' To open an existing workbook from a file: Set XLBook = XLApp.Workbooks.Open FileName ' Or, to create a new workbook: Set XLBook = XLApp.Workbooks.Add ' Make the session visible so the user can view and interact with it: XLApp.Visible = True ' Code here would set up ranges, formulas, formats, etc... needed on the worksheet for processing your input ' Assuming you then want to take your serial data you have read (I will give it the variable name SerialData) and put it in cell B2 on Sheet1, you could write: XLBook.Sheets("Sheet1").Range("B2").Value = SerialData 'etc... ' To clean everythig up at the end: XLBook.Close XLApp.Quit Set XLBook = Nothing Set XLApp = Nothing Just an outline, but should give an idea of how this works - check the MSDN library for Excel programming info to get more details of how to use the Excel object model in VB. K Dales . Private Sub MSComm1_OnComm() Do dummy = MSComm1.Input Select Case dummy Case "" ' do nothing for null characters nullchar = True Case Chr$(STX) BufPoint = 1 InBuffer$(BufPoint) = dummy Case Chr$(ETX) BufPoint = BufPoint + 1 InBuffer$(BufPoint) = dummy Select Case InBuffer$(2) Case "O" ' Question Number getch$ = "" getpoint = 3 Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" QstNumb = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" If FinalRound = True Then If Len(Overall$) < 5 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, Len (Overall$) - 1) + ")" Else QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 1) + "," + Right$(Overall$, 3) + ")" End If Else QuestNumb.Text = "Question : " + QstNumb End If QuestBox.Text = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < Chr$(ETX) Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = Chr$(ETX) AnswerBox.Text = getch$ BufPoint = 0 Case "B" ' get bank getch$ = "" For I = 3 To BufPoint - 1 getch$ = getch$ + InBuffer$(I) Next bankedinf.Text = Str(Val(getch$)) ChainTxt(0).Caption = Str(Val (getch$)) BufPoint = 0 Case "H" ' set clock ClockInf.Text = InBuffer$(3) + InBuffer$(4) + InBuffer$(5) + InBuffer$(6) Case "I" ' set round If InBuffer$(3) = 0 Then FinalRound = False QuestBox.Left = 1560 QuestBox.Width = 10455 QuestNumb.Left = 1560 QuestNumb.Width = 10455 AnswerBox.Left = 1560 AnswerBox.Width = 10455 Else FinalRound = True QuestBox.Left = 0 QuestBox.Width = 12015 QuestNumb.Left = 0 QuestNumb.Width = 12015 AnswerBox.Left = 0 AnswerBox.Width = 12015 End If Call SortForm Case "J" ' contestant names getch$ = "" getpoint = 3 Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" Name1.Text = getch$ getch$ = "" Do j = InBuffer$(getpoint) If j < ":" Then getch$ = getch$ + j End If getpoint = getpoint + 1 Loop Until j = ":" Name2.Text = getch$ Case "K" ' show correct option getpoint = 3 For qst = 0 To 5 For bod = 1 To 2 FinalRes(qst, bod) = Val(InBuffer$(getpoint)) getpoint = getpoint + 1 Next Next Call SortQst Case "L" FinalQst = Val(InBuffer$(3) - 1) Call SortQst Case "N" Overall$ = Str$(Val(InBuffer$(3) + InBuffer$(4) + InBuffer$(5) + InBuffer$(6) + InBuffer$(7))) If FinalRound = True Then If Len(Overall$) < 5 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, Len (Overall$) - 1) + ")" Else If Len(Overall$) <= 6 Then QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 1) + "," + Right$(Overall$, 3) + ")" Else QuestNumb.Text = "Question : " + QstNumb + Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 2) + "," + Right$(Overall$, 3) + ")" End If End If End If Case "Z" ' set question font getpoint = 3 ftsize$ = "" Do If InBuffer$(getpoint) < ":" Then ftsize$ = ftsize$ + InBuffer$(getpoint) End If getpoint = getpoint + 1 Loop Until InBuffer$(getpoint) = ":" QuestBox.FontSize = Format (ftsize) ftsize$ = "" Do If InBuffer$(getpoint) < ":" Then ftsize$ = ftsize$ + InBuffer$(getpoint) End If getpoint = getpoint + 1 Loop Until InBuffer$(getpoint) = ":" AnswerBox.FontSize = Format (ftsize$) BufPoint = 0 Case Else BufPoint = 0 End Select BufPoint = 0 Case Else BufPoint = BufPoint + 1 InBuffer$(BufPoint) = dummy End Select Loop Until dummy = "" End Sub . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel and Visual Basic | Excel Worksheet Functions | |||
Excel and its Visual Basic IDE | Excel Discussion (Misc queries) | |||
Is Visual Basic the same as Visual Studio 2008? | Excel Worksheet Functions | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) | |||
Excel and Visual Basic | Excel Discussion (Misc queries) |