[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x

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

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
How to Use the Help Bell
LVL 10
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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
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
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 ***

I have started creating a VBA Function in Access to create this.  I have my CDM Table and a MAPPING Table.

I am placing the CDM data into a Recordset (myRS1) and the MAPPING table will use myRS2 for the updates.  

Here is what I am doing so far Untest and still just thinking as I am going.  Iknow I have to work on the logic when I get to the update section like I said this is the start of it still not the final version or even …
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
Free Tool: Subnet Calculator
LVL 10
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.

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
Hi

We currently have a database system which is a VBA front end and a local MYSQL backend connected by ODBC.

Users authenticate directly with the MYSQL backend to gain access to the system.

We have been advised by a security specialist to improve the infrasturure as they have suggested that as the users authenticates directly with the mysql db there is the posibility to sql injection issues.

Any advice on how to improve this would be gratefully received.

thanks
0
Hi,

I would like to have a dynamic DropDown menu in cell "R2" that would adjust based on the value in "N2".

The cell in N2 will not have a DropDown menu.

The data reference will be in Column Y to AA.

So if i type "John" in cell N2, the DropDown menu from R2 will have the value ("Winnipeg" and "Montreal").
If the value in N2 does not exist in column Y, the DropDown items would be empty.

Is that possible?  Is so, how can i do it?

Thank you for your help

Ex:
SampleDropDown-sample.xlsx
0
Hi,
I am working on a complex spreadsheet and I have range of formulas (17000 rows by 36 columns). I have defined number of named ranges on this data grid (range) that are referred across the spreadsheet quite heavily in formulas like Index/Match and sumifs.
It takes very long time to clear the contents of this range, manually and from the code.
I have set the calculations to manual, triad deleting column by column and cell by cell but doesn't help.
I have also tried pasting these formulas as value, even setting the Range1.Value = Range1.Value from VBA.
Nothing works, any action on this range is taking very long time.

Any suggestion. Any calculation mode that would stop the formulas to stop looking at this range and I can magically delete it quickly :)
0
I had this question after viewing audit trail for Access 2016 database changes.

I am trying to implement your code into my database and wanted to know, if there was a way to just copy the original data from the form/subform and save it temporarily so that if the user cancels the entry a roll back to the original is possible?

Is the there a way to copy the record as a whole?

I am new to access and have some knowledge of vba so any help is appreciated.

Regards
Matt
0
Hi,

I have a form that have field attachment, also there is textbox , i want that when user attach some document and save it then it's path should also be added to the textbox.

Is there a way to achieve this?

Thank you.
0
Hi

I am looking for a VB script that would allow to type full name or users name in a search box that would run against certain LDAP groups (43 groups) to confirm if the user exists. If the user exists the output should be display on the screen and save in CSV file.

These 43 groups exists in AD (LDAP).

Thank you, M
0
Hi guys/girls,
I have been searching for several days, and cannot find a definitive answer, so I'm asking directly :-(

I have a VBA script that is rule triggered to save certain mails to text files. Works fine.
Except: the mail body text also contains the from/to/cc/subject etc fields. I just want the actual body.

Any (simple) ideas?

I attached the script (which should be a nice loop, but I never got around to making it).

Thanks in advance
outlook-vba.txt
0
Hi,
I want to copy all text having same font colour from a specific column and then paste the text in PowerPoint at specific location using vba. And again while pasting the text i  want to remove the blank spaces.  Please help out. Thanks in advance.
0
Free Tool: Path Explorer
LVL 10
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Hi,
I am not able to navigate to the Form or Pop up which will be displayed after clicking on a button in website. When I search for the table with table ID; it is searching only for the IDs which are present in the website main page and not on the pop up which is displayed on the screenshot.
Can someone please me help me out how to take the data which is present in the pop up.
Thank you in advance.



   
      'above some code is presrnt.
      'I am clicking on the button which will open a POP up
      HTMLDoc.getElementById("ctlClaimTabControl_tabClaimExceptions_lnkButton").Click
   
      HTMLDoc=browser.document

    'my code
   
     Dim HTMLTables As IHTMLElementCollection
     Dim HTMLTable As IHTMLElement
     Dim HTMLRow As IHTMLElement
     Dim HTMLCell As IHTMLElement
       
       
     Set HTMLTables = HTMLDoc.getElementsByTagName("table")
     
     i = 1
     For Each HTMLTable In HTMLTables
     Worksheets.Add
     
     If HTMLTable.ID = "ctl02_grdExceptions" Then
     
    ' Range("A" & i).Value = HTMLTable.className
     'Range("C" & i).Value = HTMLTable.ID
     'Range("D" & i).Value = HTMLTable.innerText
     'Range("B" & i).Value = Now
     Rownum = 2
     
     'If HTMLTable.ID = "grdNotes" Then
     
     
     For Each HTMLRow In HTMLTable.getElementsByTagName("tr")
     
     'Debug.Print HTMLTable.innerText
       
        ColNum = 1
            For Each HTMLCell In HTMLRow.Children
                Cells(Rownum, …
0
Hello,

I am currently working on developing a VBA code and I got stuck on the following problem.

I have multiple VBA Codes which helps me format a table and the content within that table. First of all I am defining the table borders. Then, I am doing some formats on the cell selection. Is there a way to run the VBA without selecting the cells? If he finds borders to left and right on that row where I have the active cell, the to run the VBA between that borders?

Here are the codes on how I format the table borders, and then I set some format on some cells.

E.g. if I have D2 as active cell ( in a table from B2:H10) and I run macro to format as Subheading, the format will apply between B2:H2

Sub Callback1(control As IRibbonControl)

    Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
    Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
    Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
    Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
    Selection.font.Name = "Calibri"
    Selection.font.Size = "9"
    Selection.Interior.ColorIndex = 2.5
    Selection.RowHeight = 12
    Selection.VerticalAlignment = xlCenter
End Sub

Sub Callback2(control As IRibbonControl)

    Selection.RowHeight = 12
    Selection.font.Name = "Calibri"
    Selection.font.Size = "9"
    Selection.Interior.ColorIndex = 2.5
    Selection.font.Bold = True
    Selection.font.Color = vbBlack
    Selection.Borders(xlEdgeTop).LineStyle = …
0
Hi, I have a protected sheet which is long, and when users are halfway down and leave page to return it shows from where it was left, how do I force it to show from the top?  Using VBA
0
i am trying t o use this function in 64bit but it crashes at the copymemory line,Any fixes?

Private Function IsShellVersion(ByVal version As Integer) As Boolean

  'returns True if the Shell version
  '(shell32.dll) is equal or later than
  'the value passed as 'version'
   Dim nBufferSize As Long
   Dim nUnused As Long
   Dim lpBuffer As Long
   Dim nVerMajor As Integer
   Dim bBuffer() As Byte
   
   Const sDLLFile As String = "shell32.dll"
   
   nBufferSize = GetFileVersionInfoSize(sDLLFile, nUnused)
   
   If nBufferSize > 0 Then
   
      ReDim bBuffer(nBufferSize - 1) As Byte
   
      Call GetFileVersionInfo(sDLLFile, 0&, nBufferSize, bBuffer(0))
   
      If VerQueryValue(bBuffer(0), "\", lpBuffer, nUnused) = 1 Then
         
         CopyMemory nVerMajor, ByVal lpBuffer + 10, 2  '*** see below for explanation
       
         IsShellVersion = nVerMajor >= version
     
      End If  'VerQueryValue
   
   End If  'nBufferSize
 
End Function

thanks
0
I  have  data  something  like  these :


 35   6      27      36      1      8       3
3      47      2      26      28      40   22
46      22      37      40      42      36   3
6      37      24      27      18      4     36

I  want  count  duplicate  and  color then  depending  on times  no  repeated

 for  ex  :  6, 22,  27   repeated  twice ,  I  want  clor  them  red
                  3, 36          repeated  thrice,  color  them  green
   
Kindly  help  me  to  write  vba
0
Hi

I have an excel sheet with values like the following

A                      B                               C
Number         Type                          Created
123                  Call Opening           26/09/2017
123                  Show                        26/09/2017
123                  Total                         26/09/2017
124                  Call Opening           05/10/2017
124                  Show                        05/10/2017
124                  Total                         05/10/2017

I need to bring to sheet 2 values where created is in the last month and where type = 'Total' only

From the above example the output would be

A               B                    Created
Type         Number       Date
Total         123               26/09/2017

Using excel 2010

Thanks
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.