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'd like to have a macro where I
1. Search for the word "cat" or "CAT" or "cAt" or any combination of upper/lower case.
2. I want the macro to stop when it finds that character string and allow me to see what it found.
3. I want the macro to generate a YES NO CANCEL message box
     3a. YES will
            i.    extend the selection to the next hard return (enter key) symbol
            ii.    backspace one character from the hard return character, and
            iii.   exit the macro.
     3b   NO will deselect what it found, move forward one character and go to step 1 above.
     3c  CANCEL will exit the macro.
0
Important Lessons on Recovering from Petya
LVL 10
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

I had this question after viewing MS Access Using VBA Split Word Docx file into separate Doc for Each Page.

Everything is working as "advertised" but the code is inserting an additional (blank) page for each of the pages in the original document except for the last page in the merge document. I have tried to figure this out for over a day but I am bumping up against my limited knowledge. Could someone please help?
0
I had this question after viewing Word macro to send selected text a predfined email address whilst preserving the formating in the selection.
The code provided as solution to this question works fine.
Sub EmailSelectedText()
'
' EmailNew Macro
'
'


Dim doc As Document
Dim SelText As String, eSubject As String
Dim olApp As Object
Dim sUserName As String
 
'     sUserName = Get_User_Name()
Set doc = ActiveDocument
SelText = Selection.text

If Len(SelText) = 1 Then
    MsgBox "Please select a Text to be emailed.", vbExclamation
    Exit Sub
End If
eSubject = Selection.Words(1) & " " & Selection.Words(2)

Set olApp = CreateObject("Outlook.Application")
With olApp.CreateItem(0)
    .To = "email.address@gmail.com"
    .Subject = eSubject
    .Body = SelText
   ' .display    'Remove this line and uncomment the next line to send the email
    .Send   'Uncomment this line to send the email
End With

Set olApp = Nothing
MsgBox "Handover Emailed for: " & eSubjct
' MsgBox "Handover Emailed for: " & eSubjct

End Sub

Open in new window

I would like this code to work for some of my colleageus who are on the same company Intranet as me. When I have created this macro for my colleageues and put this code on their machine, (for some but not other colleagues), the code does not work and I get an error message box (as shown in the embeded image).
VBA Error Message ScreeshotThe error message does not give any details. It just shows and icon and a help button and when I click the Help Button it takes you to a non-specific Micrsoft Page on VBA. Please note that the code works for me fine, if I log into my Windows Account on the same machine (the machine on which it gives error to a colleague when they are logged in through their own window account).
I would appreciate advice what could be blocking this code from working for other people. Thank you for your help. I think the error is not related to Macro Security, because other macros work fine on my colleague's machine.
0
I receive survey data from multiple third party vendors.  The data comes to me in a crosstab table format in excel.  I need a way to make the table into regular format, so I can define relationships to other tables.   I need a vba procedure/function that can change the format automatically.

Example format - Question are S1, S2, S3, S4A
RespondentID      S1      S2      S3       S4A      S4B
1      1      1      2      1      
2      1      6      1      2      1
3      2      3      4      2      1
4      2      6      2      2      3
5      1      4      5      2      2

The format I needed
QuestionID      ResponseID      Value
S1                               1                 1
S2                               1                 1
S3                               1                 2
S4A                               1                 4A

I will include the access database I am working with.  The table I am trying to transform is called - M_RespondentAnswers. This needs to relate to other tables in relationship window. I trying to create something like a star schema in access, with survey being the fact table and the other tables like a dimensions for reporting purposes.
MIKE_SURVEYS_2017.accdb
individual_columnlisting.xlsx
0
Good Morning Experts,

I'm running into an issue trying to do an HTTP Post of XML data.  I know I'm messing up the syntax here, but can't figure out what should go where.  Here's what I have:

Set objXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
               
GetStringURL = "https://secure.efaxdeveloper.com/EFax_WebFax.serv?"
               
GetStringPost = "id=99999999&xml=" & strXML
           
objXMLHTTP.Open "POST", GetStringURL, False
           
objXMLHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
               
objXMLHTTP.send GetStringPost
           
eFaxResponse = objXMLHTTP.responseText
               
Debug.Print eFaxResponse
   
Set objXMLHTTP = Nothing


The server is rejecting the post because it says it isn't receiving my user ID, which is 99999999 number.  The URL is supposed to go over as:

https://secure.efaxdeveloper.com/EFax_WebFax.serv?id=9999999999&xml=<the URL-encoded XML value goes here>

I know I have the various bits above in the wrong spots and the URL's probably not coming across as requested, but no matter how I move them around it doesn't seem to accept the post.

Any thoughts would be appreciated!

Thanks!

Tom
0
access 2010
form
subform

I have a form called "main"
subform called "dbo_t_redbook_pricing_escalation_detail_subform"

I need to loop through 2 columns
on the subform to see if my conditional formatting has changed the fore color/background color to red ?

background/fore color
if it does i need a msgbox or boolean alert.
I will  be calling the alert from a Command button on my main form.



Thanks
fordraiders
0
I know this is probably a simple question but I am going to ask it anyway.

I want to create usernames to be first initial of first name and the full last name.  How do I do this.

First name is stored in the field First_Name and Last name is stored in Last_Name.

Thanks
John
0
I have several hundred fillable PDFs that I am trying to complete programatically in an Access database.

I have cobbled together something that will enter the required data (this is vastly cut down to highlight the issue)

Public Sub FillForm (strPhone, strAddress, strUserName, strEmail as String)
Dim acroApp As acroApp
Dim PDDoc As AcroPDDoc
Set acroApp = New acroApp
Set PDDoc = New AcroPDDoc
Set avdoc = CreateObject("AcroExch.AVDoc")
filenm = "L:\Sandbox.pdf"
If avdoc.Open(filenm, "") Then
    Set PDDoc = avdoc.GetPDDoc()
    Set pdfform = CreateObject("AFormAut.App")
    pdfform.Fields("Daytime telephone").Value = strPhone
    pdfform.Fields("Email address").Value =strEmail
    pdfform.Fields("Correspondence").Value = strAddress
    pdfform.Fields("undefined_148").Value = strUserName
    PDDoc.Save 1, filenm
End If
End Sub

This worked fine on the pdf I was using to test but it seems that although all the forms look the same the number of undefined fields differs so what would be "undefined_148" in one is "undefined_207" in another or "undefined_122" in another etc.
It is always the next field after "Correspondence" though so what I need is some way of moving one field on from the "correspondence"  or finding the tab index of "Correspondence" and then referring to whatever the next field is called by its tab index.

Any help would be greatly appreciated.

Thank you
Richard
0
Hello Experts,
I recently took over maintaining and developing VBA applications at my company.  The original developer is not available. One program I have taken over is an Access 2007 system for entering data on 8 different tablets. The code works everyday, however the process of completely understanding just how it is working is quite challenging (and also generally considered a lower priority project, compared to development).    Each tablet has it's own local db, with forms and a 'main' table.  There is a Master db on the network that has vba in it to pull data from the 8 tablets.  This is not a true front/back split situation, as most of the data truly lives independently on the tablets.

The tablets have forms which pop up on timers for users to enter values -- some every 10 minutes and another every 2 hours.  After interacting with the form, the values are stored in the underlying 'main' table.  

We have recently become aware of some gaps in the main table -- the program is running, the forms are popping up, and the users are interacting with them as usual.  No error messages pop up.  However, when one views the main table, you can see the values are not recorded.  In the past week this has occurred on 3 different tablets.  Closing and reopening the Access program seems to fix the problem, however, there is no good way currently to become aware of this problem to know to 'fix it'.

My colleague and I plan to attempt a compact and repair on the programs this …
0
Okay, so I'm trying to use the runas with smart card credentials.  It works, but only through the command line window.  Instead I would like it to automatically bring up the standard GUI that shows the smart cards.

Example:  I want to use getmac to grab a mac address of a computer on the network.  I can only do this with credentials.  We use smart cards only.  I'm trying to get that info, redirect it to a simple text file, and then display that text file later.  Yes, I can get it to work in the command line window, but I want to select the smart card from the GUI instead.  Any way to do this??

VBA Code:

Shell "runas /smartcard getmac /s " & ipaddress & " >>MACINFO.TXT"
Shell "notepad MACINFO.TXT"
0
Free Tool: IP Lookup
LVL 10
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.

Dear Experts:

I would like to change line 3 of this code snippet to take the following circumstance into account:

The user-defined building block entry has now been saved to 'Building Block.dotx' instead of the Normal Template.

How would I have to change line 3 to accomodate for this new requirement?


Set rng = ActiveDocument.Paragraphs(2).Range
rng.Font.Size = 18
    NormalTemplate.BuildingBlockEntries("My_TOC_Just_Set_Names").Insert Where:= _
        Selection.Range, RichText:=True

Open in new window


Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Hi Experts,

Please can i get your help as me and a coleaugue cant fathom a formula or VBA to undertake this.

I have a challenge where i have a text data table with upto 1000 parts that is stored in a single cell as text values with either a space or comma as a seperator and i am trying to lookup a text value from antoher worksheet and return the job number value in the adjacent column like a v look up.#

The formulas we have tried have failed and now thinking to VBA which isnt something we are great at.
Data-Master-Line---Job-Level-MASTER.xlsx
0
For control purpose I was thinking that, if  a sales tax invoice is approved in the application then it should never be reprinted again the same way we do not allow the approved sales invoice not to be deleted. I know we can still delete unapproved invoice but once approved it is locked , now I want to do the same with reprinting:

(1) I'm thinking of designing a VBA code like below:

Dim Cancel as Interger
IF " approved" = Dlookup(" Status", " tblCustomerInvoices"," [InvoiceID] = " &Me.CboInvoiceID) Then
MsgBox " Please note that approved invoices cannot be reprinted"
Cancel = True
Exit Sub
End If

Can the above code stop the printing  or are there better ways of doing it , any suggestion is very welcome

I have not yet tried the code, I can clearly state here that the code is my own thinking in term of financial internal controls

By the way assuming it is okay which event should I place it on a form used to print the invoices

Regards

Chris
0
Hey there
I have a VBA code for changing the shape's fill from my computer files. But I want it to load the pictures from a hidden sheet in my workbook. I don't know how can I achieve it with VBA.
I want to sent this file to other people, so my pictures need to be in this workbook.
Any advice will be greatly appreciated.

p.s. I use office 2016.

Sub Test()

ActiveSheet.Shapes.Range(Array("Rectangle 16")).Fill.UserPicture "C:\Users\Ashkan\Desktop\s8.png"

End Sub

Open in new window

0
I have this VBA that works great but I want to add a specific sheet name to it.  The sheet name is MAIN_PN.  What would the code be with sheet MAIN_PN added to it?

Sub Trim()
Dim c As Range
For Each c In Range("A1:A8225")
   c = Application.Trim(c)
Next
 
End Sub
0
Good Evening Experts,

I'm submitting an XML string via HTTP Post.  Within the XML, I need to include any related PDF's as Base64 encoded strings.  For example, I have a PDF located at c:\test.pdf on the local hard drive.  (The file location will always be known and doesn't require any user intervention or file pickers.)  I just need a function that will use the file path provided to Base64 decode the PDF into an ASCII text string that I can insert into my XML string.

Thanks!

Tom
0
hi,

I can use VBA to download csv files published from google sheets with the function below which works well. However I also want to download csv files from another site that has the following format:

https://www.airbnb.co.uk/transaction_history/csv/XXXXXX?pending=1

(this is the URL that shows in the download history)

However all I get in the attached HTML file. Is there a way I could alter the VBA code to obtain the csv?

Thanks

Function DownloadFile(myURL, saveToPath)

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile saveToPath, 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If

End Function

Open in new window

airbnb_txn_pending.csv
0
I have a macro that I am currently running to remove tasks from a project plan that a) have zero work effort and b) aren't key milestone tasks, which is a custom field that we have added.

At the time that the macro runs, there are approximately 1850 rows in the project plan that the macro executes againt to remove the tasks, and the current run time can be upwards of 20 minutes (on average, about 15 minutes). While the macro will successfully execute each time, I am looking for ways to optimize the code to reduce run time. From the research I've done to date, I am not able to figure out a way to modify the code. I explored whether it would make sense to build a progress bar (or make use of the status bar) to inform the user of the macro progress, but as I understand it, the addition of this functionality, will further slow down the code.

Any help anyone can offer would be MUCH appreciated - thank you!

Sub DeleteMsProjectTask()
    DeleteTasks ActiveProject
    Dim sp As Subproject
    For Each sp In ActiveProject.Subprojects
        DeleteTasks sp.SourceProject
    Next sp
    MsgBox ("Done")
End Sub
Sub DeleteTasks(prj As Project)
Application.ScreenUpdating = False
    Dim NumTasks As Integer
    NumTasks = prj.Tasks.Count
    Dim t As Task
    Dim mileTsk As String
    Do While NumTasks > 0
        Set t = prj.Tasks(NumTasks)
        If t.OutlineLevel > 1 And t.Work = 0 Then
            mileTsk = t.GetField(FieldNameToFieldConstant("Key …
0
I'm creating an excel file from a database using a vbscript.  It all works great... but, in a few columns, I have a very long column name, for example, "Number of Missed Appointments" and I want to put that on 3 lines in a cell so it looks like:
Number of
Missed
Appointments
And I want the cell to autofit to the width of the widest line (in the above example, Appointments).

Maybe this will help convey what I'm trying to do/want:

Example of what I get, don't want, and need
I'm using:

WorkSheetObject.Cells(2,13).Value = "No of missed" & vbCrLF & "appointments"
WorksheetObject.Cells(1,13).EntireColumn.Autofit

Open in new window

0
Free Tool: Port Scanner
LVL 10
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Hi

  I am using acrobat pro 11 to convert a word doc to pdf using vba in word 2013. I need to know how to change the security setting within vba to restrict editing of the pdf doc.
 Thanks.

 Here's my code :

Sub Créer_PDF()
'
' Créer_PDF Macro
'

Dim CurrentFolder As String
Dim FileName As String
Dim myPath As String
Dim UniqueName As Boolean

UniqueName = False

'Store Information About Word File

  myPath = ActiveDocument.FullName
  CurrentFolder = ActiveDocument.Path & "\"
  FileName = Mid(myPath, InStrRev(myPath, "\") + 1, InStrRev(myPath, ".") - InStrRev(myPath, "\") - 1)

  ActiveDocument.ExportAsFixedFormat _
        OutputFileName:=CurrentFolder & FileName & ".pdf", _
        ExportFormat:=wdExportFormatPDF, _
        OpenAfterExport:=False, _
        OptimizeFor:=wdExportOptimizeForPrint, _
        Range:=wdExportAllDocument, _
        From:=1, _
        To:=1, _
        Item:=wdExportDocumentContent, _
        IncludeDocProps:=False, _
        KeepIRM:=False, _
        CreateBookmarks:=wdExportCreateHeadingBookmarks, _
        DocStructureTags:=True, _
        BitmapMissingFonts:=True, _
        UseISO19005_1:=False
End Sub

Open in new window

0
Hello all,

I need to build a table that has which cost center should be mapped different service codes.  

I have a list of almost 35K records that I need to map.  In a perfect world, I could just say 000001 to 000100 belongs to 5010 then 000101 to 000200 belong to 5020.  Well that does not work as I have some ranges within a range that belongs to a different cost center or in one section every other code changes to a different cost center really only 2 (000300 = 5040, 000301 = 7485, 000302 = 5040, 000303 = 7485 and so on.) I still would like to have some SQL code that would read in the first record say OK here is the start of my range and it goes to cost center xxxx then look at the next record it is the same cost center so it is the top of the range, go to the next one it is the same cost center so it is now the top of the range.  Finally, the cost center changes so it is the start of the new range for the new cost center and I do the same logic again.  Does this sound right?  I just need a little help getting started.

*** UPDATE ***
10/19/2017
I built the table manually but I would still like to create a script in SQL or VBA that will take my listing of codes and map a range out for each section.  The listing is around 25,000 lines and my range table is about 4,000 lines.  It took me a few hours to create it.  Creating the range in SQL is my preference, but I can do VBA as well.  Thanks again.


10/18/2017
I have started creating a VBA Function in Access to …
0
I had this question after viewing Excel how to update data connection link from cell value.


the attached workbook used to work. now it does not.  i get error access denied on the    .LoadSettings strNewURL

how can i overcome this issue,  if i manually import the same string it will work, but it does not work with the VBA

Sub ChangeDataSource()
Dim tbl As ListObject
 
strNewURL = Sheets("mySheet").Range("A1")
 
Set tbl = Sheets("Data").Range("A1").ListObject
 
With tbl.XmlMap.DataBinding
    If .SourceUrl <> strNewURL Then
        .ClearSettings
        .LoadSettings strNewURL
        .Refresh
    End If
End With

End Sub

Open in new window

EE.xlsb
0
This project would take me two hours and I don't have time this week.
If you are an expert who already has similar code in your toolbox, please post it.

If you can't help, do not worry because I  will be able to get to it next week.


I have many workbooks which have from 1 to 20 sheets. A few of the sheets have some common misspellings which I want to review, and possibly change.  

For instance  curent should be current  and 'ore' should be 'or'.   My cells occasionally have aberrations like <<send us iron ore>>, so I must eyeball all cells before any cell gets changed.


I want three macros: myFindAll   ReviewNext   ChangeAll:

dim allCells as collection  ' static module level variable
sub myFindall(list as string)
    ' example  myFindAll("curent to current; ore to or; disastor to  disaster; etc to etc;")
    ' this would find the text in every worksheet and collect it

function ReviewNext()
   static currentSheet as long, currentArea as long, currentCell as long
   ' this function would advance to the next cell in the collection.  
  '  if the cell should not be changed I manually insert an ague ` character in the middle of the string
  ' for instance  <<send more iron or`e>>

Function ChangeAll(list as string)
 '  will use excel's find a replace.
 this code is trivial and I will code it myself
0
Hello

I am new to Experts Exchange, I came across it when searching the internet for help with VBA UserForm coding.  I landed on https://www.experts-exchange.com/questions/28706684/VBA-Code-to-Populate-Userform-with-Existing-Records-Edit-them-and-add-them-back-to-the-worksheet.html  which not only answered my search but helped me to complete my UserForm.  Basically, the form on that thread was exactly what I was doing.  So I downloaded the solution and set about completing my form, copying and editing the code.

All was going well considering I haven’t used VBA for years (10+) and my coding skills are a bit rusty.  My problem is when I test my form I get “runtime error 9: subscript out of range” for the SEARCH, ADD and UPDATE buttons.  Set ws = Sheets("Sheet4") seems to be causing the problem.

I’ve also noticed that if I start the form from any other sheet than the sheet with the data in it the form is basically empty.  Since I have set the Workbook to open on the USER GUIDE sheet, as this has a button to open the form, this causes a problem.

Well as I say I’m a bit rusty and I’ve been at this for days, reading and re-reading the code, search after search online as well as VBA Help, and now I think I have gone code blind (can’t see the wood for the trees) and my rusty skills are making it hard to find what’s wrong.  If someone could take a look at the attached file and point me in the right direction I would be most grateful.

The above thread was solved by …
0
Hello

Can anyone tell me why the following is returning 3070

"?" not recognised as valid field name or expression.

rs.FindFirst strFind
   Dim nodX As MSComctlLib.Node

   Dim strBook As String
    Do While Not rs.NoMatch
      Set nodX = tv.Nodes.Add(, , , rs!Stockcode)
      nodX.Bold = True
      strBook = rs.Bookmark
      addChildren tv, nodX, rs, rs!Stockcode
      rs.Bookmark = strBook
   rs.FindNext strBook
   Loop

Open in new window

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.