It’s really helpful when developing macros in VBA to get some sort of feedback from the code as to what is going on behind the scenes. Doing this has helped me get rid of superfluous reiterations.
1. Show the Immediate window
2. Add descriptive messages to the code
3. See the debug messages as they are encountered
Below is an example of how one might execute a stored procedure on the ISeries from a C# application. I understand that there are certainly other, much shorter ways to achieve this.
using (OdbcConnection DbConnection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MyISeres"].ConnectionString))
using (OdbcCommand DbCommand = new OdbcCommand())
DbCommand.Connection = DbConnection;
DbCommand.CommandText = "call MYLIB.MYPROC(?, ?)";
OdbcParameter p1= new OdbcParameter();
p1.DbType = System.Data.DbType.String;
p1.ParameterName = "MyParm1";
p1.Size = 50;
p1.Value = "some value";
p1.Direction = System.Data.ParameterDirection.Input;
OdbcParameter p2= new OdbcParameter();
p2.DbType = System.Data.DbType.String;
p2.ParameterName = "MyParm2";
p2.Size = 50;
p2.Value = "some other value";
p2.Direction = System.Data.ParameterDirection.Input;
using (OdbcDataReader DbReader = DbCommand.ExecuteReader())
GridView1.DataSource = DbReader;
SELECT * FROM qsys2.sysparms
WHERE SPECIFIC_NAME = 'PROC_NAME'
FETCH FIRST 500 ROWS ONLY
Recently I had to debug a Java web application and being new to the platform there were quite a few things I was unfamiliar with. One of those things was how to tell an AS400 resource whether it’s libraries and procedures were going to be separated by a slash or a dot. The application I was working on used slashes and apparently my connection’s default was dots. At first I changed all the calls but I always knew there had to be another way.
Here is how I specified what kind of naming the application should use on the server.xml file.
url="jdbc:as400://AS400URL.COM; libraries= mylib, yourlib, theirlib; naming=system; prompt=false"
Note the naming on the URL property.
“System” causes the connection to interpret the slash character as a separator.
“Sql” causes the connection to interpret the dot character as a separator.
I ran into this code when trying to copy some rows from one sheet to another in Excel through VBA. The widths would always display at the destination spreadsheet’s default column width. This code helps, all you need to pass it is the source and target ranges as parameters.
Private Sub copyColumnWidths(TargetRange As Range, SourceRange As Range)
Dim c As Long
For c = 1 To .Columns.Count
TargetRange.Columns(c).ColumnWidth = .Columns(c).ColumnWidth
copyColumnWidths Sheets("MySheet").Range("A1:AA500"), Sheets("YourSheet").Range("A1:AA500")
I needed to look through a Word document for an application running the .NET 2.0 framework. Here is how I did it using NetOffice 1.60 which can be downloaded via NuGet in Visual Studio.
Imports Word = NetOffice.WordApi
Private Sub readWordDoc()
' start word and turn off msg boxes
Dim wordApplication As New Word.Application
wordApplication.DisplayAlerts = WdAlertLevel.wdAlertsNone
' open document as read only
Dim myDocument As Word.Document
myDocument = wordApplication.Documents.Open(System.IO.Path.GetFullPath("MyDoc.docx"), False, True)
' get document's content
Dim content As String
content = myDocument.Content.Text
' close doc
' output content
I tried to get the last-modified date for a table through the available columns of Microsoft SQL Server Management Studio but was shocked to find it was not available. Luckily I found this query on the web which will give you just that.
SELECT name, modify_date FROM sys.tables
I’m sure this could be done better. But nonetheless, it works.
Dim wb As String
wb = Application.ActiveWorkbook.Name
wb = Replace(wb, ".xlsm", ".")
wb = Replace(wb, ".xlsx", ".")
wb = Replace(wb, ".xls", ".")
getWBname = wb
Useful function that creates a PDF in VBA.
Creating a PDF from the active worksheet.
Dim myFile As String
'Save PDF being generated to Temp location
myFile = IIf(Environ$("tmp") <> "", Environ$("tmp"), Environ$("temp")) & "myPDF.pdf"
fileToSend = Create_PDF(ActiveWorkbook.ActiveSheet, myFile, True, False)
Function Create_PDF(Myvar As Object, FixedFilePathName As String, _
OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
Dim FileFormatstr As String
Dim Fname As Variant
'Test to see if the Microsoft Create/Send add-in is installed.
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then
If FixedFilePathName = "" Then
'Open the GetSaveAsFilename dialog to enter a file name for the PDF file.
FileFormatstr = "PDF Files (*.pdf), *.pdf"
Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
'If you cancel this dialog, exit the function.
If Fname = False Then Exit Function
Fname = FixedFilePathName
'If OverwriteIfFileExist = False then test to see if the PDF
'already exists in the folder and exit the function if it does.
If OverwriteIfFileExist = False Then
If Dir(Fname) <> "" Then Exit Function
'Now export the PDF file.
On Error Resume Next
On Error GoTo 0
'If the export is successful, return the file name.
If Dir(Fname) <> "" Then Create_PDF = Fname
Very handy function for sending emails through SMTP using VBA.
Sub Mail_SMTP(StrAttachment As String, StrTo As String, StrSubject As String, StrBody As String, Send As Boolean)
On Error GoTo ErrHandler
Dim smtp_username As String
Dim smtp_password As String
smtp_username = ""
smtp_password = ""
Dim iMsg As Object
Dim iConf As Object
Dim Flds As Variant
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mycompany.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = smtp_username
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = smtp_password
Set .Configuration = iConf
.To = StrTo
.CC = ""
.BCC = ""
.From = getUsername() & "@mycompany.com"
.Subject = StrSubject
.TextBody = StrBody
If Send = True Then
If Err.Number = 0 Then
MsgBox "Email sent to: " & StrTo, vbInformation, "Email"
MsgBox "Unable to send email. Please make sure you are connected to the internet."