VBA

10K

Solutions

3K

Contributors

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

Hello Experts:

In a previous post (https://www.experts-exchange.com/questions/29114146/Add-remove-trendline-to-Excel-graph-through-VBA.html?anchor=a42656598¬ificationFollowed=211366882#a42656598), I needed assistance with developing VBA code that would allow me to add/hide trend lines (via command button) to an existing histogram.
Expert Subodh has provided a superb solution that exactly does what I needed.

Now, I am requesting assistance with modifying the existing code (see attachment) where a single cmdButton allows to add/hide trend lines to all histograms on the worksheet.   Please see additional details in the attachment.

Thank you for your help in advance.
EEH
Command-Button-to-Add-Trendline-v03.xlsm
0
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

I need to display the advanced attributes of "Encrypt" attributes.
However, it does not display the value of Attributes when I saw in the properties of the files/folders in the location.
It should display DAE for encrypted folder, A for non encrypted file, AE for encrypted file.

Please advise.

Below is the code  ->

Sub MainExtractData()

    Dim NewSht As Worksheet
    Dim MainFolderName As String
    Dim TimeLimit As Long, StartTime As Double

    ReDim X(1 To 65536, 1 To 12)

    Set objShell = CreateObject("Shell.Application")
    TimeLimit = Application.InputBox("Please enter the maximum time that you wish this code to run for in minutes" & vbNewLine & vbNewLine & _
                                     "Leave this at zero for unlimited runtime", "Time Check box", 0)
    StartTime = Timer

    Application.ScreenUpdating = False
    MainFolderName = BrowseForFolder()
    Set NewSht = ThisWorkbook.Sheets.Add

    X(1, 1) = "Path"
    X(1, 2) = "File Name"
    X(1, 3) = "Last Accessed"
    X(1, 4) = "Last Modified"
    X(1, 5) = "Created"
    X(1, 6) = "Type"
    X(1, 7) = "Size"
    X(1, 8) = "Owner"
    X(1, 9) = "Author"
    X(1, 10) = "Title"
    X(1, 11) = "Comments"
    X(1, 12) = "Attributes"

    i = 1

    Set FSO = CreateObject("scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder(MainFolderName)
    'error handling to stop the obscure error that occurs at time when retrieving DateLastAccessed
    On Error Resume…
0
I have created a piece of code that sits behind a report via a command button.
The code is:
Option Compare Database
Option Explicit

Function FileExist(FileFullPath As String) As Boolean
  Dim value As Boolean
  value = False
  If Dir(FileFullPath) <> "" Then
    value = True
  End If
  FileExist = value
End Function

Private Sub btnClose_Click()
    DoCmd.Close acReport, Me.Name
End Sub

Private Sub btnExportPDF_Click()
    
    Dim fileName As String
    Dim fldrPath As String
    Dim filePath As String
    Dim db As Database
    Dim rs As Recordset
    Dim ProjectName As String
    Dim answer As Integer
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qry_BOMReport")
    
    With rs
        fileName = fldrPath & !EquipmentID & "-" & !PartNumber & ".pdf"
        ProjectName = !ProjectName
        rs.Close
    End With
    
    If ProjectName = "BSS" Then
        fldrPath = "C:\QA\Project\BSS\"
    End If
    If ProjectName = "KBT" Then
        fldrPath = "C:\QA\Project\KBT\"
    End If
    
    filePath = fldrPath & fileName
    
    'check if file already exists
    If FileExist(filePath) Then
        answer = MsgBox(prompt:="PDF file already exists: " & vbNewLine & filePath & vbNewLine & vbNewLine & _
                        "Would you like to replace existing file?", Buttons:=vbYesNo, Title:="Existing PDF File")
        If answer = vbNo Then Exit Sub
    End If
    
    'On Error GoTo invalidFolderPath
    DoCmd.OutputTo objecttype:=acOutputReport, 

Open in new window

0
Experts:

In the attached XLS, I am using a command button to add trend lines to the graph.

I also have a 2nd command button ("remove trend lines").   I tried to replace the ".Add" with ".Delete" for all three series; however, that syntax throws an error message.

My questions:
1.   What is the correct syntax that will allow me to delete the trend lines from the graph.
2.   Also, I would like to *combine" the two functions into one... maybe through an IF statement indicating the following:

Pseudo:
If command button caption = "Add" then
add trend lines
Else
remove trend lines
End if

Open in new window


How can the above be accomplished?

Thank you,
EEH
Command-Button-to-Add-Trendline-v01.xlsm
0
Hello,

First post so apologies if I do anything wrong...

I have a macro in Outlook 2013, when an email is selected I can click the macro to run and an input message appears which prompts the user to enter a category to assign the email to (in this case I need the user to assign it with a number)

I have the below code that I dug up on this site provided by David Lee (BlueDevilFan) - This works great, however, if possible I want this to also trigger a task to be created with the email and that category still assigned, if possible having the email body appear in the task and as an attachment, I'd need the task window to appear so it can be altered if required and then assigned out to someone

The other tricky bit (for me) is that we are creating tasks by dropping emails into a shared inbox task list, not our own, so I would need the code to create a task in a specific task list and not just the users own task list, can anyone help?

Sub CategorizeMe()
    Dim olkItm As Object, olkCat As Outlook.Category, strCat As String
    
    strCat = InputBox("Enter Category")
    
    On Error Resume Next
    For Each olkItm In Application.ActiveExplorer.Selection
        If olkItm.Class = olMail Then
            olkItm.Categories = strCat
            olkItm.Save
            olkCat = Session.Categories.Item(strCat)
        End If
    Next
    On Error GoTo 0
    Set olkItm = Nothing
    Set olkCat = Nothing
End Sub

Open in new window


Many thanks

S

P.s. pls dont be mad at me for marking this as high priority, it is in my little world (:
0
The path below is where sync to:
This PC\SM-N920V-TheNote5\Phone\Documents

Open in new window


I need an automated way to sync files to a "Portable Media Player" connected to a Windows 10 PC.

The "Portable Media Player" is actually as Samsung Galaxy Note 5: an Android device.

Drag and drop works just fine, but I need to truly automate the process.

I can use VBA built in to MS Access, VB script or even a good fashioned batch file.

NOTE: I use SyncBack to synchronize some folders on my system, but it will not recognize the path to the Note5.
0
Hi All,

I want to delete all the review comments in excel at once. I mean, I have multiple tabs and each tab has review comments, it is time consuming task to run through all the tabs and use clear comments option.

Can I get VBA to delete all review comments in entire workbook at once ?
0
sample.xlsxI'm very much a beginner with VBA. I've usually gotten away with solving my problems with advanced formulae and avoiding VBA in the past. However I don't think I can solve this problem without it - I've reached the limit of my expertise!

I have been trying to remove syntax which is appearing when I export data from a sharepoint site into an excel spreadsheet.

Each cell will have a "product" value, which can have multiple values. In Sharepoint the cell has multiple values such as: 1.1.1.3-Fruit, 1.1.1.3-vegetables, 1.1.1.3-sweets.
However (doing some searches I know this has been seen before) when it is exported from Sharepoint, the values look like this: 1.1.1.3-Fruit;#259;#1.1.1.3-vegetables;#260;#1.1.1.3-sweets;#261;#. The x.x.x.x references are a configuration management number which I need to keep, which stops me from just stripping all numbers in the cell.

I had put in a formula to do some substitution to remove the ";#" values and replace with " ", but this leaves the number behind. I'd then used nested substitutions to remove the 259, 260, 261, etc as well, but the limitation on how many nested substitutions prevents this working for all cells in the "product" column.

I thought about using a lookup table, but I this can only provide limited results as well, using Vlookup for example.

I've thought about trying to do a replace in VBA and replace all possible values with blank space (eg ;#000;# to ;#300;#), but not sure how to …
0
Question:
I want to use vba --> Placing multiple markers on a Google Map

What I can do now:
I currently have code that builds the below string and sends it to the default browser with a click of a button.  It shows directions to all 3 addresses.

https://www.google.com/maps/dir/123+Main+st+Kansas+City+MO+/10000+E+59th+St,+Raytown,MO/8500+state+Line+Rd,+Leawood+ks

What I would like to do :
Have something that would place 3 markers on a Google Map with the 3 addresses.
If able also give me some control over color and size of the markers.
It is important that it can be done by a click of a button as opposed to copy and paste

What I am looking for :
If you can give me the string I need for the browser, I can build the string in code and use --> FollowHyperlink

Thanks in advance for any help
LJG
0
I am attempting to setup Rest API in VBA and need some help walking through the steps. Can anyone help me by walking me through step by step for that I can learn how to do this for future projects?
0
Cloud Class® Course: Microsoft Office 2010
LVL 12
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

I'm trying to embed an Outlook Popup warning that warns the Sender when they hit the send button, if 2 or more recipients (To, CC or BC) of the email contain 2 different domains. For example: To contains (johnsmith@yahoo.com); CC contains (billthompson@gmail.com). Another example: To contains (Johnsmith@yahoo.com;billthompson@gmail.com)

See code that I have so far, it just is not working:
---------------------------------------------------------------
The message code to use for the popup is:
        prompt = "This email contains receipients to multiple domains:" & vbNewLine & strMsg & "Are you sure you want to send?"
        If MsgBox(prompt, vbYesNo + vbExclamation + vbMsgBoxSetForeground, "Warning! About to send to messages to multiple domains") = vbNo Then
            Cancel = True
        End If
0
Hello
Can you please help with the attached file if possible.

The attached file is macro enabled file and I need to protect all cells contain formulas ( highlighted in yellow color ) and any additional cells will contain formulas in future too.

i tried to protect the sheet but the Macro will not work unless I remove the protection.

 Any ideas how to protected the cells without  and keep the macro working in same time.
Filter-Test.xlsm
0
How to manage error handlers in importing mails from outlook to excel through VBA?

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Sales")
i = 1
   For Each OutlookMail In Folder.Items
    If OutlookMail.ReceivedTime = Range("From_date").Value And OutlookMail.Subject = (" updates")  Then
'        Range("eMsail_subject").Offset(i, 0).Value = OutlookMail.Subject
        Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
'        Range("eMail_text").Offset(i, 0).Value = OutlookMail.Body
       
        i = i + 1
     End If
   
Next OutlookMail
 msgbox "operation done!!."

 If i = 0 Then
' OutlookMail.ReceivedTime <> Range("From_date").Value Then
        msgbox "No Login/Logout found for the given date"
End If

But it is not working properly .

Here i need if mails are available for mentioned date it has to show success message  and if no mails are available it has to show message as no mails.

Please help!

Thanks in advance..
0
I had this question after viewing Need to Add Prefix to Tab Names.

what if I need the excel tab name prefix as FL.1-2005 FL.2-2006 and FL.3-2017
0
I sometimes get into a mode where I cannot copy text from a vba program into some cells in my worksheet

When I use ctrl c to copy then ctrl v to paste, an older clipboard entry was pasted.  (I was very confused at first, but I eventually figured out there was a problem with the office clipboard.

This problem used to happen once every several months, but lately, it has been happening every few days.

For instance the Office Clipboard looked like this:
 office clipboard
I can click on the lower entries and they paste fine. But, when I click on the first entry, nothing happens.

If I close Excel, the reopen it, everything is back to normal for a while.

I will eventually try fixing this with  an Office repair, but right now I just want to know if anybody else is having this problem ?
0
Hello

I have a worksheet with 4 columns

DateofBusiness   UnitID   CampaignID    Participate

Where Dateofbusiness is each day of year
And CampaignID is a unique number that will be 1 to 100. Each "campaign" will have each day of year, so sheet will have each day of year listed anywhere from 1 to 100 times in column A.
And Participate will be a 1 or blank - this will be the cell I am attempting to select.

To get to the correct "Participant" cell, I need to search through the "CampaignID" column and find the defined one, and then search the Dateofbusiness to find the defined one. I have been able to do this using one of the fields but am looking for a way to do the lookup and select the correct cell using both parameters.

I have been able to use this shared code to find the cell, but again it is only searching with one parameter

thank you for any recommendations

If Trim(FindString) <> "" Then
        With Sheets("TEST001").Range("A:A")
            Set rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
           If Not rng Is Nothing Then
                Application.Goto rng, True
                rng.Offset(0, icid).Select
                ActiveCell.Value = 1…
0
How is it possible for me to concatenate several cells in Excel without using using CONCATENATE with lots of arguments, e.g. CONCATENATE(A1, ",", A2, ",", A3, ",", A4, ",", A5, ",", [....]) ?  I have several dozens of cells to combine in this way and I am wondering if there is a quicker way. Thanks.
0
When I query values from a database into Excel, the numbers come in exactly like they are entered or with the trailing zeroes truncated.  For example, 2.00 may come in as 2, or 1.90 may come in as 1.9 with 1.98 coming in as 1.98.  But there is certain data that may come in this same column that the numbers are all whole numbers.  

Is there code that can be applied that will detect the formatting of the contents of each cell and determine the most decimal places used by any one cell in the column, and then format the entire column to display the same number of decimals.

Attached is a file with two examples of how data may come in, and how I would like it displayed.
Formatting-Example.xlsx
0
MS Access Form Display Attachment file name in textbox with Attachment datatype.  Initially created a form for users to include attachments.  Now others need to review these requests and I need this form to display the previously attachment file names within a textbox.  How can I do that?  The attachments are saved to a field within a table, just need the attachment file names to be displayed and also need the capability of viewing the attachments as well.
0
Cloud Class® Course: Microsoft Windows 7 Basic
LVL 12
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Update Word Document with Data entered in Excel
sp
I have Excel sheet displayed on the screenshot above.
I also have word documents with the name that matches exactly the name of each Comp-Name.

BATH.docx
MONR.docx
STAR.docx
JUSGB.docx




Each word document has a table with the same column names as the Excel sheet

I would like to have the respective word document table updates automatically  its data to match the new entered data in Excel .
for instance If I have a new purchase, I can manually add it to a new Excel row, and the program should add that new row automatically to the corresponding word document.

Example:
I have new purchase for a company MONR
On Excel , I will add new entry:
MONR      TX      3/3/2018      3/8/2018

on a corresponding word document MONR.doc, I should have the new entry added automatically to the table in word.

Any help will be very much appreciated.

Thanks
0
In column A, each row contains text that contains various information, but the text always contain one of three words such as "cat", "dog" or "bird".  I need the formula (or VBA code) to search the cell in column A, and assign a number in the adjoining cell in column B based on the word found in column A.  For example, "cat" = 1, "dog" = 2, and "bird" = 3.  Assigning a number for particular words will allow me to have better control over the sorting that I need to do later.
0
Hello Experts!

I have unbound forms in my Ms Access app which I use for posting journals if the debits & credits are equal. I select the journal number from a combo box (A small query) and run the update query that is all perfect, now to improve my form further I want to be seeing the actual value in a debit and credit column as well. I understand that I need a query like the way we use queries in reports but I do not know how to use any on a form so that if I do the following:
(1)      Select a combo Box , then the form  show debits & credits with related record, probably I’m required to add also a control that will subtract debits & credits if zero then post succeed, if not post cancelled ( do not worry about the VBA required on checking the debits and credits I have no issue here).  

Here the record source is a query not a table to supply a form.

Regards

Chris
0
Hello experts,

I have the following flat file attached (Input Sheet).
I am trying to build a vba procedure that covers the following requirements:
1-Check if Output sheet if so delete and create a new blank
2-Transfer the various unique values in column H, J and AI
3-Consolidate the sum values for the various month from AJ to BV split by column AI

I attached dummy file with expected Output sheet.

I think that we can proceed steps by steps, such as copy unique values related to H & J.
For this we can use the following procedure:

Sub CopyRangeUniqueValues(FromWSheet As String, FromRange As String, ToWSheet As String, ToRange As String)
'Sub treats first cell in FromRange as a header label, and will copy it to ToRange even if it is duplicated in the list
Dim celHome As Range, rgDest As Range, rgSource As Range
Dim n As Long
Set celHome = ActiveCell
Set rgDest = Worksheets(ToWSheet).Range(ToRange)
Range(rgDest, rgDest.End(xlDown)).ClearContents

With Worksheets(FromWSheet)
    n = .UsedRange.Row + .UsedRange.Rows.Count - 1
    Set rgSource = .Range(FromRange & n)
End With

rgDest.Worksheet.Activate
rgSource.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rgDest, Unique:=True
Application.GoTo celHome

Open in new window


Concerning the consolidated sum we can also go ahead with a formula reported in macro:

Sub LoopFormula2(strwsName, strColflag, strCol As String, strColName As String, formula As String)

'Call LoopFormula2("ImportCC", "1", "D", "Test", "=clean(sheet1!G2*sheet1!I2)") 'example of formula
    Dim ws As Worksheet
    Set ws = Worksheets(strwsName)
    ws.Activate
     '==>Clear Destination Column
    Range(strCol & ":" & strCol).Clear
    UsedRows = ActiveSheet.UsedRange.Rows.count + ActiveSheet.UsedRange.Rows(1).Row - 1
    If strColflag = 1 Then
        Range(strCol & 1).Value = strColName
    End If
    '==>Clear Destination Column
    RightFormula = Replace(formula, "1048576", UsedRows)
    '==>ApplyFormula
    Range(Cells(2, strCol), Cells(UsedRows, strCol)).formula = RightFormula
    Range(Cells(2, strCol), Cells(UsedRows, strCol)).Value = Range(Cells(2, strCol), Cells(UsedRows, strCol)).Value
End Sub

Open in new window


Thank you in advance for your help.
0
A general question. I have often wondered, when VBA is doing a lot of stuff on Excel you get a 'not responding' message while the screen goes haywire (oddly). But given a few about 10 seconds it comes ok.
This happens even if I turn Application.ScreenUpdating = False

Question 1: what is the reason for this? Not enough RAM?
Question 2: can it be prevented? (as it scares users, and me!)

Thanks.
0
I receive emails daily about registrations from a certain email address. In this email there are 1-3 links. The link text is "Download" and it refers to a link where the files are saved.

So if there is one attachment, it will say:
some text: Download

For two it will say:
some text: Download
some text: Download

and for three:
some text: Download
some text: Download
some text: Download

What I am looking for as a vba macro is to be able to select these emails and then run the macro. The macro should print each email and then go to the link and print the attachments in these links one by one for all emails selected.

The order should be print email, print link 1 attachment, print link 2 attachment (if present), print link 3 attachment (if present) and repeat.

I am looking for a vba solution as I don't have rights to install anything else on the computer.

Appreciate your help.

Edit: I must mention here that I am completely new to outlook-vba and below has been generated by copy/paste/edit from here. It is working to print the emails. I now need help with printing the attachments by following the hyperlinks in the email.

Below is the code used to print the email.

Option Explicit

Public Sub PrintDelete()
    Dim objOL                      As Outlook.Application
    Dim objMsg                     As Outlook.MailItem
    Dim objAttachments             As Outlook.Attachments
    Dim objSelection               As Outlook.Selection
    Dim i    

Open in new window

0

VBA

10K

Solutions

3K

Contributors

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.