Recently I began to keep my taskbar at the top of my screen. I've noticed that often times programs will open up behind the taskbar at the top of the screen. Because of this, they cannot be moved and part of the window content is hidden. I've done searching online and have not found an answer or solution to this problem. It boils down to this: when you write a program, you can set the position of the window when it opens. The position can either be system managed or you can set it manually. So, many programmers choose to have their windows open at 0,0 (meaning 0 pixels from the top, 0 pixels from the left). This works well for MDI Parent windows especially, since they often need to occupy most, if not all, of the screen to accommodate all of its child windows.
Solutions / Workarounds :
1) Set auto-hide on the taskbar to ON. With this enabled, you will be able to easily see all of your screen and move those windows from behind the taskbar area while the taskbar is hidden.
2) Often times right clicking the program on the taskbar and choosing "Move" or "Resize" will not work, though they are worth a try. However, clicking on the right bottom corner to "resize" the window will often move it down from behind the taskbar, as windows will not let you resize an item and have it end up behind the taskbar.
3) Why hasn't a programmer written some code to fix this? Just hooking the processes and checking their X,Y coordinates isnt tough, and re-setting them is also as painless with some API commands. I don't have the time right now; who's up for the challenge?
Thursday, October 8, 2009
Wednesday, May 27, 2009
Continued
As an addition to the last post, I'd like to point out that certain SQL commands (ones that do not return any information) can be executed very simply from VB. For example, to delete a record, one simply needs the following code:
Here we have executed a command that will delete everything from the "name" table. Whether or not the SQL command is correct, i do not know (as i haven't tested it) but that's not the important part. The rest of the code is what matters here. SQL commands themselves vary from database to database so you must do your own checking of the syntax of the command. For example, when writing to an MS Access db one must enclose the table name (and column names) in brackets (ie. [name] refers to the table named "name"). Using the foxpro db in the last example, the brackets were not necessary. The point is, the SQL command syntax varies and this example is more to show you how to execute an SQL command and less about SQL command syntax.
This type of code can be used for Delete, Insert and Update commands, as they do not return any result and therefore they do not need anywhere to store retrieved data.
However, any commands that return data require a recordset or a dataadapter to read from the table. Then, one can do what they please with the information that has been retrieved. Ill post more about this later, though I've already briefly touched on recordsets in earlier posts.
dim cn as adodb.connection
cn.ConnectionString = "Provider=vfpoledb;" & _
"Data Source=C:\ab\dname.dbf;" & _
"Mode=Read|Share Deny None;" & _
"Password=''"
cn.open("DELETE * FROM name")
cn.close
cn = nothing
Here we have executed a command that will delete everything from the "name" table. Whether or not the SQL command is correct, i do not know (as i haven't tested it) but that's not the important part. The rest of the code is what matters here. SQL commands themselves vary from database to database so you must do your own checking of the syntax of the command. For example, when writing to an MS Access db one must enclose the table name (and column names) in brackets (ie. [name] refers to the table named "name"). Using the foxpro db in the last example, the brackets were not necessary. The point is, the SQL command syntax varies and this example is more to show you how to execute an SQL command and less about SQL command syntax.
This type of code can be used for Delete, Insert and Update commands, as they do not return any result and therefore they do not need anywhere to store retrieved data.
However, any commands that return data require a recordset or a dataadapter to read from the table. Then, one can do what they please with the information that has been retrieved. Ill post more about this later, though I've already briefly touched on recordsets in earlier posts.
Tuesday, April 14, 2009
SQL Database Access Using FoxPro Database, Visual Basic Frontend and ADO
I learned about databases today and how to work with them. It took me a long time to run through different code to figure out how to open, view and work with an existing database. For my purposes, I was working with a *.dbf database file, created in FoxPro. I am using visual basic to access this database. The following code shows how you can connect to a database, send an SQL command to read all the fields within the database, and then two different ways to print those fields elsewhere.
Now, this code was created with the displaying of only one row of information in mind. I wrote it with a more complex SQL command which should only return one row of information (searching for a row by number). This code will then write out all the information in that row. The first command has many options, especially when taking the data from your sql command result and formatting it into its own table. It has options for column delimiters, row delimiters, how many rows to read, where to start, what to do with null characters, etc. It does not, however, deal well with extra spaces. The second command, however, handles the extra characters well with a Trim() command. It simply asks how many items are in our results list and then loops through each one, printing out the name and the value for that particular field.
Important things to know: The Connection String is going to be the one thing that EVERYONE has to customize. It contains information about the type of database you are connecting to as well as the filename of that database. For example, my "provider" is foxpro (which, by the way, requires the fox pro ole db to be able to access it), my location is C:\ab\name.dbf, and I've set other parameters (the ability to read/write to the DB, sharing of the file, etc.). The other important and customizable line is the command you will send. Here I've used SELECT * FROM name; - this means I'm selecting all (*) from the table "name" which happens to be in my database file, name.dbf. Your table name and .dbf file wont always be the same, and there may be more than one table in each .dbf file. Finally, I just used ActiveDocument.ActiveWindow.Selection.InsertAfter so i could print out the results to the open MSWord window, as this is all being written in VBA.
Sub Whatever()
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
con.ConnectionString = "Provider=vfpoledb;" & _
"Data Source=C:\ab\dname.dbf;" & _
"Mode=Read|Share Deny None;" & _
"Password=''"
con.Open
With cmd
.ActiveConnection = con
.CommandText = "SELECT * FROM name;"
.CommandType = adCmdText
End With
With rst
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd
End With
ActiveDocument.ActiveWindow.Selection.InsertAfter _
Trim(rst.GetString(2, 1, ",", Chr(13), ""))
For Each x In rst.Fields
ActiveDocument.ActiveWindow.Selection.InsertAfter _
Trim(x.Name) & "=" & Trim(x.Value)
Next
con.Close
Set con = Nothing
Set cmd = Nothing
Set rst = Nothing
End Sub
Now, this code was created with the displaying of only one row of information in mind. I wrote it with a more complex SQL command which should only return one row of information (searching for a row by number). This code will then write out all the information in that row. The first command has many options, especially when taking the data from your sql command result and formatting it into its own table. It has options for column delimiters, row delimiters, how many rows to read, where to start, what to do with null characters, etc. It does not, however, deal well with extra spaces. The second command, however, handles the extra characters well with a Trim() command. It simply asks how many items are in our results list and then loops through each one, printing out the name and the value for that particular field.
Important things to know: The Connection String is going to be the one thing that EVERYONE has to customize. It contains information about the type of database you are connecting to as well as the filename of that database. For example, my "provider" is foxpro (which, by the way, requires the fox pro ole db to be able to access it), my location is C:\ab\name.dbf, and I've set other parameters (the ability to read/write to the DB, sharing of the file, etc.). The other important and customizable line is the command you will send. Here I've used SELECT * FROM name; - this means I'm selecting all (*) from the table "name" which happens to be in my database file, name.dbf. Your table name and .dbf file wont always be the same, and there may be more than one table in each .dbf file. Finally, I just used ActiveDocument.ActiveWindow.Selection.InsertAfter so i could print out the results to the open MSWord window, as this is all being written in VBA.
Sunday, April 5, 2009
Handling the faulty WebBrowser control
The last post dealt with using the WebBrowser control to communicate with a website. Today I will take that post a step further and explain how to workaround the faulty responses the WebBrowser control returns regarding its status when loading a page. In the last post, we saw that the following code allows us to communicate with a website, navigating our control to a specific URL, and even sending POST data along with our request:
Today we will take this a step further, refining our code for checking whether the browser is done loading and also setting a variable equal to the html code of the page the control has navigated to.
Starting with the easier of the two, all we need to do to read the html from the page is use this line of code at any point after you have successfully completed the WebBrowser.Navigate command.
where varname is the variable you want to set equal to the html code, and your WebBrowser control is accessing the "Document" property of the WebBrowser control which allows you access to a number of the elements of the document itself. More info on this can be found at MSDN. For our purposes, this simple line of code pulls all the html from the page. However, because our WebBrowser control does not return a reliable result on whether it has finished loading the page, sometimes setting a variable equal to the Document.Body.innerHTML will return an error "91" because the page has not finished loading and therefore the property is not yet set. To deal with this, we will use error handlers and labels in our code.
An error handler tells your code snippet what to do when an error occurs. By default, an error in vba will trigger a Message box with the error number and a description of the error and then the program will terminate. Other options include "On Error Resume Next" which will simply ignore errors and move forward with the code, skipping the line that caused the error, "On Error Goto 0" which will end code execution silently when an error occurs, or "On Error Goto labelname" where "labelname" is a name you've chosen. By doing this, you will have to build your own error handler. In this case, we want to use "On Error Goto LabelName" to set up our own error handler. It will look like this:
This bit of code incorporates labels and GoTo statements as well as an error handler. In the beginning we tell the code to goto "errhandler" if an error occurs. at the END of our code we define our errhandler section using a label "ERRHANDLER:".
Essentially, our errorhandler stops the code from reporting any error #91's, and instead loop back to around to the point in the code that is causing that error. In this way, we can create a loop that keeps attempting to set the variable "abc" to the value of innerHTML until it can successfully do so. If it's not an error #91, the errorhandler reports the error and then ends (because there is no more code to execute - which is why the errorhandler is placed at the end of the code).
Note that in our error handler we use the code "Resume RD" to goto our label "RD:". During a break operation like an error handler, we use "Resume" instead of "GoTo" to direct the code back to a label. Also not that after the last command PRIOR to our error handler code, we insert the "Exit Sub" code so that our code does not continue into our error handler unless directed to do so.
Sub Brws()
Dim URL As String, PostData() As Byte
URL = "https://www.google.com"
PostData = "name=ben&lname=cavallaro"
PostData = StrConv(PostData, vbFromUnicode)
Me.WebBrowser1.Silent = True
Me.WebBrowser1.Navigate URL, 0, "", PostData, ""
Do
DoEvents
Loop Until Me.WebBrowser1.ReadyState = 4
End Sub
Today we will take this a step further, refining our code for checking whether the browser is done loading and also setting a variable equal to the html code of the page the control has navigated to.
Starting with the easier of the two, all we need to do to read the html from the page is use this line of code at any point after you have successfully completed the WebBrowser.Navigate command.
varname = Me.WebBrowser1.Document.Body.innerHTMLwhere varname is the variable you want to set equal to the html code, and your WebBrowser control is accessing the "Document" property of the WebBrowser control which allows you access to a number of the elements of the document itself. More info on this can be found at MSDN. For our purposes, this simple line of code pulls all the html from the page. However, because our WebBrowser control does not return a reliable result on whether it has finished loading the page, sometimes setting a variable equal to the Document.Body.innerHTML will return an error "91" because the page has not finished loading and therefore the property is not yet set. To deal with this, we will use error handlers and labels in our code.
An error handler tells your code snippet what to do when an error occurs. By default, an error in vba will trigger a Message box with the error number and a description of the error and then the program will terminate. Other options include "On Error Resume Next" which will simply ignore errors and move forward with the code, skipping the line that caused the error, "On Error Goto 0" which will end code execution silently when an error occurs, or "On Error Goto labelname" where "labelname" is a name you've chosen. By doing this, you will have to build your own error handler. In this case, we want to use "On Error Goto LabelName" to set up our own error handler. It will look like this:
Sub Brws()
On Error Goto errhandler
Dim URL As String, PostData() As Byte
URL = "https://www.google.com"
PostData = "name=ben&lname=cavallaro"
PostData = StrConv(PostData, vbFromUnicode)
Me.WebBrowser1.Silent = True
Me.WebBrowser1.Navigate URL, 0, "", PostData, ""
Do
DoEvents
Loop Until Me.WebBrowser1.ReadyState = 4
RD:
abc = Me.WebBrowser1.Document.body.innerhtml
Exit Sub
ERRHANDLER:
If err.number = "91" Then
Resume RD
Else
Msgbox err.number & err.description
End If
End Sub
This bit of code incorporates labels and GoTo statements as well as an error handler. In the beginning we tell the code to goto "errhandler" if an error occurs. at the END of our code we define our errhandler section using a label "ERRHANDLER:".
Essentially, our errorhandler stops the code from reporting any error #91's, and instead loop back to around to the point in the code that is causing that error. In this way, we can create a loop that keeps attempting to set the variable "abc" to the value of innerHTML until it can successfully do so. If it's not an error #91, the errorhandler reports the error and then ends (because there is no more code to execute - which is why the errorhandler is placed at the end of the code).
Note that in our error handler we use the code "Resume RD" to goto our label "RD:". During a break operation like an error handler, we use "Resume" instead of "GoTo" to direct the code back to a label. Also not that after the last command PRIOR to our error handler code, we insert the "Exit Sub" code so that our code does not continue into our error handler unless directed to do so.
Saturday, April 4, 2009
Webbrowser Control in VBA Excel / Word
Using the Webbrowser control is a helpful tool in many programming situations. One of the most common tasks that can be automated is interaction with websites. In the following example, I will setup the webbrowser control within the document itself, navigate to a website and send post data along to that site. I've changed the site location and the post data fields so the code will not function correctly until you enter that information in.
FYI: it is easy to find out the URL and the post data of the form you want to submit by following these steps:
Open your internet browser and view the site where the data would be entered. View the source code. You will find an HTML tag that says "form action=xxxxxx". The URL we want will be the "xxxxx" portion in that line. Then you will want to note all of the input tag name values within that form. These will look like "input type=xxx name=xxx". When posting your data (or when using a GET command, for that matter) you will need to know the input names so you can send the relevant data along with them.
In the example below i've included inputs named "fname", "lname", and "zip" which you will see in the postdata variable. The script below also reads through a list of 10 rows in an excel spreadsheet. If column "A" has data in it, then it pulls data from Columns A, B, and C corresponding to our variables "fname", "lname", and "zip", in that order. Then it moves on to the next row and loops until there is no more data or until row 10 is reached.
As you will see, the code can iterate through a list of items and run a request to a website, filling in the data the user desires and retrieving the results. the me.webbrowser1.silent = true code stops the browser from spitting any messageboxes back at us so we can be sure there are no problems automating the process. The line "loop until me.webbrowser1.readystate = readystate_complete" is a line that waits until the webbrowser control has responded that it has fully finished loading the page. the words readystate_complete can be replaced by the number 4, as they are interchangeable. However, I have found that this code is often flawed as the webbrowser does not always return a reliable result about whether it has finished loading (it often says it's done loading but has not loaded the innerhtml property yet). I will deal with this issue in a later post.
For now, this should be a sufficient example to get you POSTing data using the webbrowser control. A GET command would be even simpler. You just append the data that is in the post variable to the end of the web address, with a question mark separating the web address and the postdata (ie. http://testsite.com/script.cgi?variable1=value&variable2=2ndvalue&variable3=thirdvalue)
Note, when combining the variables in the string you separate each new variable with the "&" character and place the input name on the left of the equals sign and the value on the right of it.
FYI: it is easy to find out the URL and the post data of the form you want to submit by following these steps:
Open your internet browser and view the site where the data would be entered. View the source code. You will find an HTML tag that says "form action=xxxxxx". The URL we want will be the "xxxxx" portion in that line. Then you will want to note all of the input tag name values within that form. These will look like "input type=xxx name=xxx". When posting your data (or when using a GET command, for that matter) you will need to know the input names so you can send the relevant data along with them.
In the example below i've included inputs named "fname", "lname", and "zip" which you will see in the postdata variable. The script below also reads through a list of 10 rows in an excel spreadsheet. If column "A" has data in it, then it pulls data from Columns A, B, and C corresponding to our variables "fname", "lname", and "zip", in that order. Then it moves on to the next row and loops until there is no more data or until row 10 is reached.
Sub getpage()
Dim URL As String, PostData() As Byte, kp as integer
URL = "https://examplepage.com/script.cgi"
For kp = 1 To 10
'iterate through the first ten rows
If Not Me.Cells(kp, 1) = "" Then
'if cell A1 isn't empty then proceed
PostData = "fname=" & Me.Cells(kp, 1) & "&lname=" _
& Me.Cells(kp, 2) & "&zip=" & Me.Cells(kp, 3)
'set our postdata string to the variables and their
'corresponding values
PostData = StrConv(PostData, vbFromUnicode)
'convert the string
Me.WebBrowser1.Silent = True
'stop the browser from alerting us of anything
Me.WebBrowser1.Navigate URL, 0, "", PostData, ""
'tell the browser to goto the URL
Do
DoEvents
Loop Until Me.WebBrowser1.ReadyState = READYSTATE_COMPLETE
'let this loop run until the webbrowser has finished
'loading the page
End If
Next kp
End Sub
As you will see, the code can iterate through a list of items and run a request to a website, filling in the data the user desires and retrieving the results. the me.webbrowser1.silent = true code stops the browser from spitting any messageboxes back at us so we can be sure there are no problems automating the process. The line "loop until me.webbrowser1.readystate = readystate_complete" is a line that waits until the webbrowser control has responded that it has fully finished loading the page. the words readystate_complete can be replaced by the number 4, as they are interchangeable. However, I have found that this code is often flawed as the webbrowser does not always return a reliable result about whether it has finished loading (it often says it's done loading but has not loaded the innerhtml property yet). I will deal with this issue in a later post.
For now, this should be a sufficient example to get you POSTing data using the webbrowser control. A GET command would be even simpler. You just append the data that is in the post variable to the end of the web address, with a question mark separating the web address and the postdata (ie. http://testsite.com/script.cgi?variable1=value&variable2=2ndvalue&variable3=thirdvalue)
Note, when combining the variables in the string you separate each new variable with the "&" character and place the input name on the left of the equals sign and the value on the right of it.
Programming
Just wanted to include some of the stuff I've learned over the past 2 months. I used to program a lot, but then I stopped for a very long time. Now it has become relevant again in my life, especially in the context of macros built into MS Office applications. It seems to make sense to me that I should post some of the code that I write - and some of the solutions to problems that have arisen while writing these macros. I often have trouble finding solutions to problems that I run into, and I think it is helpful to post these solutions so that others may find them quicker.
Sunday, January 25, 2009
welcome
i created this simply to post things that i think others might want to find. sometimes i have questions that take me a while to find the answer to, so i figured i might post them here in case someone else out there is also wondering the same things.
Subscribe to:
Posts (Atom)