VBA

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Share tech news, updates, or what's on your mind.

Sign up to Post

I understand MS Access vba code within MS Access but not familiar with interfacing with other applications such as internet explorer.  Trying to automate user tasks.  I have developed code based on internet research and my close but not there.

This is what the user currently has to do and what we are trying to accomplish:

1.  Fill out the Container field with a text string (need to automate the pass of this value); I have done this in code below

2.  Press RETURN on keyboard which then reloads the web form to show a drop down list for the Mfg Order field.  This list has checkboxes on the left side with corresponding text values on the right side (need to refresh this reload); don't know how to do this and this appear to be the crutch of solving the problem in order to get to 3. below.

3.  The user then clicks only one checkbox of interest (need to automate the pass of this value)

4. Click on the ViewReport button (need to automated this)

Eg of Container value is "07016216" (this value is captured from my MS Access Form; just hardcoded below)
Eg of Mfg Order value to select is "ZMCSVC177759" (remember, this drop down list can have many values which the user has to pick only one from the drop down list; this value is captured from my MS Access Form; just hardcoded below)


Many thanks!!!!!



Code below:

Public Function cam()
     
     'make sure you add references to Microsoft Internet Controls (shdocvw.dll) and
     'Microsoft HTML object …
0
Free Tool: Subnet Calculator
LVL 9
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I have a table called tbl_employee (sql database). On my form I have an unbound text box called TESTPTO. That text box gets populated with data based on a function. In my tbl_employee I have a field called PTOValidation. It is this field that I would like to store the value of my TestPTO unbound test box data. I tried =[TestPTO] and it gives me the result but does not store in the table. What is this newbie missing?
0
I need to scrap a portion of the text from a table that is created after I enter an "ID" and and click search on a web page. The table is also located on 1 of 3 tabs that are created after the search is complete. I am currently able to enter the product "ID" and click search creating the table but  am not able to extract the data.
I have been trying to to use:

 Container = IE.document.getElementsByClassName("contId")(0).innertext
Worksheets("Sheet1").Cells(2, 3) = Container 

Open in new window

 

For some reason it seems like VBA doesn't read any of the HTML code on tab-1 which is where the text that I need to scrap is. I think this because as I work my way through the elements of the HTML code using getElementById("").outerHtml or getElementByClassName("").outerHtml I can return the line of code. However when I reach the tab I require "tab-1" it skips past to "tab-2". If I use an element that is unique to tab-1 I get Automation Error: Object variable or With block variable not set.

I have attached the HTML code.

Thanks in advance for your help
HTML.jpg
0
My macro uses Internet Explorer to open PeopleSoft Financials and then run a public query. It has been working for a long time until recently. I'm aware of an update to Internet Explorer on our machines in early July and am wondering if that has caused this issue.

The macro is throwing an "Object has disconnected from its clients" error message.

Here is how the variable for Internet Explorer is defined: Public ExpApp As InternetExplorer

Here is the code where the error is thown:
Set ExpApp = CreateObject("InternetExplorer.Application")
ExpApp.Visible = True

ExpApp.navigate vLogin

Do Until ExpApp.readyState = READYSTATE_COMPLETE   ' ERROR IS THROWN HERE
MyTimer
Loop

Do Until ExpApp.document.Title <> "PeopleSoft 8 Sign-in"
MyTimer
Loop

Do Until ExpApp.readyState = READYSTATE_COMPLETE
MyTimer
Loop

vLogin is a string with the URL to the PeopleSoft login screen MyTimer is a little function that waits 1 second each time it is kicked off

This macro has been around a while so I imagine that this is pretty old code. I'm hoping there are a few minor changes I could make instead of re-writing this macro.  I've attached the code for the macro.

Any suggestions would be greatly appreciated. Thanks for the help......
Code.docx
0
Please see attached sample. Using sheet1 as the source, I would like a piece of vba to move each row to an output sheet. For each row it would check cell R to see if the series number had changed. When it detects that it has changed it will need to position back to the rows preceding the change in column R value and overwrite cell A for all rows that have the same value in cell R with the very last value of cell A that it finds in the series. Probably easier to understand when looking at the sample output.

Each entire row including the header row should be copied to the output sheet. There will be 1000's of rows in my real data.
sample-docseries.xlsx
0
Hi Guys,

Advised to come here by a friend.

I am currently using the below VBA to send auto BCC from Outlook 2010. However, I'm struggling to adjust this to send the BCC from a specific email. I use 2 different emails from within my account, and only need the BCC to be sent from one, not both. Any assistance would be great!

Private Sub Application_ItemSend(ByVal Item As Object, _
                                 Cancel As Boolean)
        Dim objRecip As Recipient
    Dim strMsg As String
    Dim res As Integer
    Dim strBcc As String
    On Error Resume Next

    ' #### USER OPTIONS ####
    ' address for Bcc -- must be SMTP address or resolvable
    ' to a name in the address book
    strBcc = "emailreplyarchive@eonenergy.com"

    Set objRecip = Item.Recipients.Add(strBcc)
    objRecip.Type = olBCC
    If Not objRecip.Resolve Then
        strMsg = "Could not resolve the Bcc recipient. " & _
                 "Do you want still to send the message?"
        res = MsgBox(strMsg, vbYesNo + vbDefaultButton1, _
                "Could Not Resolve Bcc Recipient")
        If res = vbNo Then
            Cancel = True
        End If
    End If

    Set objRecip = Nothing
End Sub
0
I have a simple worksheet (attached). I want to display the Page # of ## in cell F5, where # is the current page number and ## is the total number of pages.
ABC-Company----TB---1705-Rev7EE.xlsx
0
There are two tables, one for Student and one for Borrowed Books. In the Microsoft Access (2010, 2013), it is easy to display a form Student based on the table Student, and other form BorrowedBooks based on the table Borrowed Books with their Record Source pointed.

How to do Form BorrowedBooks showing records for the current student showing on the Form Student one at one time? I'm looking to learn both VBA script and using the built-in controls to achieve the result.

Relationship between has been built. And it's one student to many books relationship.
0
I have users who gather research from government websites, for each subject they are printing 5 or 6 webpages as separate pdf's.  

A macro would help (IE,  but Chrome might be ok) so they can click a button on several webpages, then click to complete saving it all to a single PDF would be just the thing.
VBA or Javascript script as a button on the browser perhaps?

With these workstations  we use PDF Creator (we use Version 1.7.3), which has a  useful"collect/wait" feature, just need to figure out how to use it in script.
0
Hello,

I am trying to help a small company that is using an .mdb database in Access 2007.  Their previous programmer left little documentation, unfortunately.  The problem occurs when they call some routines that are intended to create a PDF file from an Access report.  The routines were written by Stephen Lebans, and are supposed to be safe and stable.  The Lebans routines are called by VBA code.

Here is some more info.  The company has a small network with two workstations.  On one workstation everything works fine; it is able to call the Lebans routines and create a PDF file.  On the second workstation the code appears to execute but no PDF is created.  Adobe Acrobat DC was installed recently on both PCs.  The problem creating PDF files on PC#2 began after they installed Acrobat DC.

I will probably call Adobe tech support to get their help.  But before that I hope someone can help with several questions I have.

Question 1 - In the VBA editor, in Tools -> References, I see that "AcrobatPDFMakerForOffice"
is checked.  Please let me know if I should have checked some other reference instead.

Question 2 - The "AcrobatPDFMakerForOffice" in Tools - > References  seems to refer to the following file: "c:\Program Files (x86)\Adobe\Acrobat 2015\PDFMaker\Office\AcrobatPDFMakerForOffice.tlb",
which is a type library file.  Does that seem OK?  Or should it point to a "dll" or "exe"?

Question 3 - On PC#1, which is creating PDFs correctly, if I click the Office …
0
Free Tool: ZipGrep
LVL 9
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

I have an Excel sheet that hides rows depending on whether the user is or isn't a member of a particular group.
Recently, the code has just stopped working. Without any adjustment to the actual code (I know this because I password protected it and 100% certain I have not amended it)

The Macro errors out on line Set grp = GetObject(strPath & strGroup & ",group")

Full code below...

Function IsUserMemberOf(strDomain As String, strGroup _
  As String, strMember As String) As Boolean
  Dim grp As Object
  Dim strPath As String

  strPath = "WinNT://" & strDomain & "/"
  Set grp = GetObject(strPath & strGroup & ",group")
  IsUserMemberOf = grp.IsMember(strPath & strMember)
  Set grp = Nothing
End Function

Function GetCurrentUser() As String
    GetCurrentUser = Environ("USERNAME")
End Function

Function GetCurrentDomain() As String
    GetCurrentDomain = Environ("USERDOMAIN")
End Function

Open in new window


and the line of code that calls the IsUserMemberOf function...
If IsUserMemberOf(GetCurrentDomain, "GroupName", GetCurrentUser) Then blnUserPermittedSheetAccess = True Else blnUserPermittedSheetAccess = False

Open in new window


What could be wrong?
0
Hello,

I have a form that have a subform. I want to export data from subform to excel. The export function is working fine but I want to export specific field which is not happening. As shown in attach DB in frmListBox, there is field in subform as Country and then there is one more field as Full Name, I want that when Country is filter from form it should show Full Name and Country field will be hidden in subform  but when export button is pressed it should export Country field (the hidden one, can unhide it also if hidden field cannot be exported but full name field should not be exported) ,same is the case with Product and so on.I need help for country, then for the rest I will handle.

Any help would be appreciated.

Thank you.
Wsm93--1-.accdb
0
I am using the following lines of code to freeze panes on a sheet, but it has to get focus of the sheet.
Is there anyway to freeze panes w/o activating a current sheet and acting on a selection ?

    Sheets(SheetName).Rows("2:2").Select
    ActiveWindow.FreezePanes = True

Open in new window

0
In Microsoft Access, I have a form which currently opens up a number of pdf's (currently in Acrobat Reader). My goal is to open up the same number of pdf's in IE, but have them grouped by color. The way the sort of the list is made, the first tab in a group is an image of a purchase order, the following tabs are pdf's of vendor invoices. Often times there is a one to one relationship between purchase order and invoice, however on a fairly regular basis there may be more than one invoice to the purchase order. I would like to make it clear to the user by colored tabs, the relationship of Purchase Orders to Invoices.

The selection criteria and Path has been commented out. Printing works fine, but is also commented out at the moment.

Private Sub Command0_Click()
Dim db As Database
Dim rst As DAO.Recordset
Dim StrSQL As String
Dim PathName As String
Dim PO As String

Set db = CurrentDb()

StrSQL = "SELECTION CRITERIA"

Set rst = db.OpenRecordset(kaySQL, dbOpenDynaset, dbSeeChanges)
Do Until rst.EOF
  PathName = "SomePath" & rst("Image-no") & ".PDF"

 CreateObject("Shell.Application").Namespace(0).ParseName(PathName).InvokeVerb ("Open")
 
 'CreateObject("Shell.Application").Namespace(0).ParseName(PathName).InvokeVerb ("Print")

  rst.MoveNext
Loop
rst.Close

End Sub
0
Hello, I was wondering if it's possible that I could set up a customer pivot table to repeat these four things for each customer?  

Example:
Customer A
Customer B

Customer A  | Jumbo
Customer A  | Large
Customer A  | Medium
Customer A  | Small

Customer B  | Jumbo
Customer B  | Large
Customer B  | Medium
Customer B  | Small

Thanks
0
How do I Create a variable that is the lowest of group excluding zeros
Example attached
db5.mdb
0
Hello ~

With Access 2010 in multiuser Front End / Back End configuration, Outlook 2013, referencing Microsoft Outlook 15.0 Object Library

Following 'on click' event results in OCCASIONAL / INFREQUENT Run-time error: '-2147023170(800706be)' - "Automation Error: The remote procedure call failed."  It crashes on line #48: Set objMailItem = olkApp.CreateItem(olMailItem)  Subsequent attempts succeed.

Additionally, and even less frequently, I receive Run-time error '462' - "The remote server machine does not exist or is unavailable."
This error occurs on line #47:  Set olkNameSpace = olkApp.GetNamespace("MAPI")  Subsequent attempts succeed.

I am particularly surprised this error happens infrequently - maybe one time out of twelve±, most usually within the first three or four attempts, then less often.

-> Would appreciate your thoughts and illumination!

I will be away for the next day + this only happens on client's computers, so I will have to apply suggestions at their site.
I do not do the IT work on client's machines, leads me to wonder if all service releases have been applied(???).  - Will check.

With Best Regards, Jacob

Private Sub Command1037_Click()
    'On Error GoTo Err_Command1037_Click

    Dim stDocName As String
    Dim cancel As Integer
    Dim msg As String

    Dim bOK As Boolean  '121511
    Dim dtStart As Date
    Dim strPdfFile As String

    'Update a sent box if it's empty
    If IsNull(Forms!frmOrders!ordInvSntDT) = True Or 

Open in new window

0
I have a list of business name and their address including zip code in different columns which is quite long and can't do manual search on google. Now I want to pull information about their website, email and phone on google. I am just curious to know if its can be done.

Can we do a similar search thru Facebook?

If So, I am looking for both solution

I am new to VB / Macros. So, looking for detailed instructions help with code as well as how to apply it.

Thank you!
Gaurav
0
Hi Team,

I am trying to save a webpage, with below code, but I just want to know if there is frequent disconnection in internet, my code fails, may I know to keep the page for waiting or is there any exception I can write for below code.

oXMLHTTP.Open "GET", "url", False
    oXMLHTTP.send

    If oXMLHTTP.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.type = 1
        oStream.Write oXMLHTTP.responseBody
        If fso.FileExists(appPath & "\tes1.html") Then fso.deletefile (appPath & "\tes1.html")
        oStream.SaveToFile appPath & "\tes1.html"
        oStream.Close
    End If
0
Free Tool: IP Lookup
LVL 9
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I need to convert VBA code that updates or inserts data into a Table to Stored Procedure in MS SQL.  Attahced is a snippnet of the VBA code I need to convert.   Hoping I can get some guidence on what needs to change ( I am sure a lot) but could use some help to get started.
NeedtoConvertStoredProcedure.txt
0
I have this code my form

Private Sub Form_Unload(Cancel As Integer)

If AllowClose = False Then
        Cancel = True
        MsgBox "Use the Form -> Close option to close this form"
        Me.Visible = True
    End If

End Sub
+++++++++++++++++++++
But when I use this code to close I get a run time error, ps I am new to access VBA

Private Sub Command0_Click()


On Error GoTo HandleError
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close acForm, Form_frm_two, acSavePrompt
   
HandleExit:
    Exit Sub
HandleError:
    MsgBox Err.Description
    Resume HandleExit

End Sub
0
1) I was asked by my colleagues if when we add a new line, that it didn't go to the bottom, can it be coded to be alphabetized by the Employee name in the list(last name, first name).

2) I was asked if we could search and go right to a particular record in the form vs. scrolling. For example, I want to update only Sample 3. How can I bring that record up in the form? Can we put that name up, or some other unique ID like a phone number, that only the user would know?
0
Hi Team,
Need vba sub or function for the below requirement.
I got three excel sheets, with 5 columns in each sheet.

 I need to search the first column (which is company name) on sheet1 against other two sheets column1,  here names are not consistent

 for example
sheet1: column1: has "ABC Ltd" and
sheet2:column1 has "ABC"  but
sheet3;column1 (which is standard for names is ABC US Limited).

This way I need to copy the data in other columns from sheet2 (if data matches) to sheet 1.

Can you please help.
0
I am at long last trying to learn VBA, and have a Kindle version of Excel VBA Programming for Dummies by John Walkenbach. I have followed exactly the instructions he gives for creating the simplest of macros to create my name and the date, in bold text and 16pt. I followed the instructions EXACTLY, and do not get the expected result. I attach my spreadsheet with this Question, and also a screenshot of the Visual Basic Editor shown in the Dummies book.
What have I done wrong? I want to be able to go through this book and learn VBA, but with this bad start I am already foxed.
Any ideas?Macro-Lesson-No.-1.xlsmThe 'Dummies' VB Editor page screenshot
0
I am getting an intermittent error when trying to open Excel using MS Access 2010 vba.  The relevant part of my code is shown below.
The error I am getting is:  Automation Error  The object invoked has disconnected from it's clients
As you can see from the code below I have an error routine which will allow the program to either use an instance of Excel that is already open or open a new one.  But, I get the error on the line:  Set xlObj = GetObject(, "excel.application").  It doesn't make it to the error handling routine.
I've used this method of opening excel for a long time but this error just started showing up recently and, as I mentioned, it's intermittent.  Sometime is works, sometimes it doesn't, on the same computer.
Any ideas on what is causing this and what I can do to avoid it?


Private Sub FormatTheSpreadsheet(spreadsheetname)
On Error GoTo Err_FormatTheSpreadsheet

Dim xlObj As Object

Set xlObj = GetObject(, "excel.application")

With xlObj
     ' Code for formatting spreadsheet
End With

Exit_FormatTheSpreadsheet:
    Exit Sub
Err_FormatTheSpreadsheet:
    If Err.Number = 429 Then
        Set xlObj = CreateObject("excel.application")
        Resume Next
    Else
        MsgBox Err.Description
        Resume Exit_FormatTheSpreadsheet
    End If
End Sub
0

VBA

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.