Browsing the VBA category

MS Access VBA Environ() function not defined

A user was getting an error that the “ENVIRON()” function was not defined.  This is a built-in function, so I was confused at first…

In the VBA code, I went to Tools >> References and I saw that there were some missing libraries.  What happened was, the current user was on MS Office 2010, and someone else opened up the database in a newer version of MS Office.

In the “References” box, I saw that it said “MISSING: Microsoft Excel 15.0” .  MS Office 2010 equates to version 14.0

Access was “smart” enough to upgrade the library that was needed when the other user opened it up in MS Office 2016.  Then when the original user in Office 2010 (Version 14) tried to open it, it couldn’t find the reference (Version 15).

The solution was to update the reference (uncheck the version 15, re-check version 14), and tell users that if they’re using the new version of MS Office, to use a different instance of the database.

Read the article

MS Excel vba saveas specify directory

I had a need for, when an MS Excel document opened, for it to prompt the user to re-save the file in a certain location.

Add a module with:

Private Sub Auto_Open()

End Sub

… this will automatically run when the file is opened.

The line of code I needed was:

Application.GetSaveAsFilename InitialFileName:=”C:\temp\”

Read the article

  • 11.24.2016
  • VBA

MS Access Queries for Delete and Make Table don’t work with VBA

have some VBA code was running a query that would delete the contents of a table; and another query that would do a “Make Table”.  Normally, when a Make Table query is run, a table would be recreated (i.e. overwritten if it already exists).  For some reason, this wasn’t working.

Solution: in my VBA code, I manually ran a SQL statement to delete the contents of the table.  I think the VBA code was running an “Append” Query (without deleting the initial table, first).

 

Read the article

VBA Add Weekdays only

I needed to add weekdays to a date.  You can’t use the built-in VBA code DateAdd() (even if you pass in the parameter “w”).  I found a function, here:

https://support.microsoft.com/en-us/kb/115489

The trick is though, you have to leave the function named DateAddW() – for some reason when I tried to change it to something else, it didn’t work!

Read the article

  • 08.30.2016
  • VBA

Debugging individual function in VBA doesn’t work

I have some code (function or subroutine) in MS Access that normally I could go into the “immediate” window, type “?” plus the function name (ex. “?sendemail“) and it would run.  It wasn’t working.

Apparently because the code was in the form instead of a module, it was making a difference.  Once I created a new module in MS Access and pasted the code into there (from the form), I was able to run that function separately.

Read the article

  • 07.24.2016
  • VBA

Get properties of MS PPT (Office) file

A neat little VBA script that can tell you the properties of a PPT file (which could be translated to other office products)

Sub ListBuiltInProperties()
Dim x As Long
On Error Resume Next
Debug.Print “BEGIN ====================================”
With ActivePresentation.BuiltInDocumentProperties
For x = 1 To .Count
Debug.Print “Property Number: ” & CStr(x)
Debug.Print “Property Name: ” & .Item(x).Name
Debug.Print “Property Type: ” & .Item(x).Type
Debug.Print “Property Value: ” & .Item(x).Value
Debug.Print ” ”
Next
Debug.Print “END ====================================”
End With
End Sub
Custom Properties:
Sub ListCustomDocumentProperties()
Dim x As Long
On Error Resume Next
Debug.Print “BEGIN ====================================”
With ActivePresentation.CustomDocumentProperties
For x = 1 To .Count
Debug.Print “Property Number: ” & CStr(x)
Debug.Print “Property Name: ” & .Item(x).Name
Debug.Print “Property Type: ” & .Item(x).Type
Debug.Print “Property Value: ” & .Item(x).Value
Debug.Print ” ”
Next
Debug.Print “END ====================================”
End With
End Sub

Read the article

  • 07.16.2016
  • VBA

Get data from SQL Server in Excel

You can use this VBA code to query a SQL Server database, right from Excel:

 

https://gist.github.com/stevesohcot/47d73642ff0c9475d99e89d0a2d141d1

Read the article

excel vba get column letter

I needed to dynamically delete a column in my Excel Spreadsheet (the “Grand Total” of a Pivot table, after I copied/pasted it).

 

I’m also using a “Count” variable incase one isn’t found, it’ll just know to stop

I’m using “objXL.” as I’m manipulating this from MS Access

 

The trick was to get the cell ADDRESS, split it by a “$”, and then when it’s an array, take the second item (at position “1”)

 

 

Dim Count As Integer

    Dim GrandTotalColum As String

       

    Count = 0

    GrandTotalColum = “”

   

    Do

        objXL.ActiveCell.Offset(0, 1).Select

        Count = Count + 1

       

        If objXL.ActiveCell = “Grand Total” Then

            arrAddress = Split(objXL.ActiveCell.Address, “$”)

            GrandTotalColum = arrAddress(1)

                       

        End If

       

    Loop Until objXL.ActiveCell = “Grand Total” Or Count = 50

Read the article

MS Access – function to merge weekends into Friday

I have to run a “daily” report, but take data from the weekends and put it into the previous Friday.
So I detect the day of the week, and if it’s Saturday I subtract one day, if it’s Sunday I subtract 2 days:

DateIntoFri: IIf(Weekday([entrydate])=7,DateAdd(“d”,-1,[entrydate]),IIf(Weekday([entrydate])=1,DateAdd(“d”,-2,[entrydate]),[entrydate]))

Read the article

MS Access VBA unable to copy row due to validation

Having recently solved the issue in MS Access not being able to copy a row of data in a datasheet,   I had to add a validation to one of the rows to ensure there was data in it.  But then I ran into a similar copy/paste problem; this time it was complaining that the “new” row didn’t have a validation.  I got around this by checking the Primary ID (an auto number) to make sure that it wasn’t 0:

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo PreventCopyError:

    If Me.cboCostCenter = “” Or IsNull(Me.cboCostCenter) = “true” Then

        ‘ Only do this for existing entries – NOT new ones (if you copy/paste)

        If Me.ID <> 0 Then

            MsgBox “ERROR: Cost Center is Required”, vbCritical, “INVALID ENTRY”

            Cancel = True

        End If

    End If

PreventCopyError:

 End Sub

Read the article