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,

I would need to create an excel vba to list down both encrypted & unencrypted files & folders
and the results will be output into excel file where the unencrytped files & folders will be highlighted in the cells/rows in the results file.

Please advise how can I do this?

Thank you so much.
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

Hello.  I have two questions please, if I may:
Q1.  How to delete excel rows based on criteria in known column after source data import?
Q2.  How to import source data into excel table?

I am exporting data from a 'Master' working excel worksheet to a 'Temporary' excel workbook worksheet, and then importing specific department information from the Temporary excel worksheet into the applicable specific 'department' workbook worksheet (35 departments).  Note, the department spreadsheets are set up as a table as there are additional columns with formulas (refer Q2.)

I have a master working data file that contains 25 columns of data, and in the 26th column, a key to identify the end applicable specific department.
This worksheet has all my required formulas and product mapping, so that only the clean and required data is ultimately imported in the specific department worksheet (first 14 columns of data).

I have used the code based on the previous question "Excel VBA: Import data from a closed workbook (posted 2018-01-18), and I can successful import and export my required data into my required columns.  

Q1.  How do I delete the rows in the Source (Temporary) worksheet, based on the data using the key identifier which is located in row 14, after the 'Copy from Source to Target WB' has been completed, and prior to the Source workbook being closed?

Note in version of 'Copy of Source to Target WB', I have used the following:

SourceWS.Activate
For i = Last Row to…
0
Hi,

I am using the following code to protect worksheets in excel:
Private Sub Workbook_Open()
For Each ws In ThisWorkbook.Worksheets

    ws.Protect Password:="UNITI"
    If ws.Name <> ShInputForm.Name Then
        ws.Visible = xlSheetVeryHidden
    End If
 Next
 
 With DisclaimerForm
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Show
 End With

 Application.ScreenUpdating = False
End Sub

Open in new window


The sheets gets protected. However, when I unprotect the sheet, it does not require a password.
How can i resolve this?

Thanks
0
Case: I have a CommandButton and an unbound ComboBox in a form (RowSourceType: list of values - populated from another form using VBA).

Goal: When a user clicks on the CommandButton, the selected item in the ComboBox to be deleted from this ComboBox.

Attempts: I used in the Click event of the CommandButton, the RemoveItem method of the ComboBox, which needs the index of the item-to-delete.
To get the index of the selected item, I tried to use the Selected property of the ComboBox, looping through all the ComboBox items, but the Selected property keeps returning 0 regrdless of the selection.

Can you please tell me how can I achieve this goal?
0
I am unable to authenticate to webservice with my VBA code.

Below is the code :-

Sub Test_GetQuote() Dim s As String s = VRLogin() MsgBox s End Sub

Function VRLogin() As String

Dim sURL As String Dim sEnv As String Dim xmlhtp As New MSXML2.ServerXMLHTTP60 ' enter below web service url sURL = ""

sEnv = "<?xml version =""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelop xmlns:ns1=""http://www.g1.com/services/WorldGeocoder"">"
sEnv = sEnv & "<soap:Envelop xmlns:ns2=""http://cxf.apache.org/bindings/xformat"">"
sEnv = sEnv & "<soap:Envelop xmlns:soap=""http://schemas.xmlsoap.org/wsdl/soap/"">"
sEnv = sEnv & "<soap:Envelop xmlns:tns=""http://www.g1.com/"">"
sEnv = sEnv & "<soap:Envelop xmlns:wsdl=""http://schemas.xmlsoap.org/wsdl/"">"
sEnv = sEnv & "<soap:Envelop xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">"
sEnv = sEnv & "<soap:Envelop targetNamespace=""http://www.g1.com/"">"

sEnv = sEnv & "<soap:Body>"

sEnv = sEnv & "<keyByAddressRequest>"   'Request to Web service

    sEnv = sEnv & "<address>"

    sEnv = sEnv & "<Country></Country>"
    sEnv = sEnv & "<City></City>"
    sEnv = sEnv & "<StateProvince></StateProvince>"
    sEnv = sEnv & "<PostalCode></PostalCode>"
    sEnv = sEnv & "<AddressLine1></AddressLine1>"
    sEnv = sEnv & "<AddressLine2></AddressLine2>"
    sEnv = sEnv & "<County></County>"
    sEnv = sEnv & "<AddressLine3></AddressLine3>"
    sEnv = sEnv & "<RecordID>1</RecordID>"
    sEnv = sEnv & …
0
Hi Guys,

I want to move a specific mails from mailbox to sub folders ("invoice") using a script under outlook. i'm interested on received mails which have excel files attachments with cells A1='invoice".

Thank you for your help.
0
If coloumn A and Coloumn C  of sheet2 are same then copy the data of sheeet1  of same row and paste the data to sheet3
If Coloumn A and Coloumn B  of sheet2 are same then copy the data of sheeet1  of same row and paste the data to sheet4
Highlighted colour is only for understanding purpose,In actual file there is no highlighted colour
I have to do all this by vba
sample-file-001.xlsm
0
Yellow highlighted colour is only for understanding purpose in actual file there is no yellow highlighted colour
in actual file there is only blue highlighted colour
nothing to do with yellow highlighted colour coloumn
If a row has unhighlighted data after the highlighted cell then delete the data till highlighted cell
See the result in sheet2
I have putted the result in sheet2 but I want the result in same sheet
i have to do all this by vba only
plz see the sample file
Book1.xlsm
0
I need to filter project records in Microsoft Access according to criteria determined by selections made on a form, using about 60 checkboxes as well as about 5 combo boxes and 5 text fields. I thought maybe Access's Filter-by-Forms would work, but it turns out I need to be able to handle much more complex filtering than that feature is capable of -- although I need it to work in a similar user-friendly manner. The criteria would be a mix of MUST MEET and INCLUDE IF PRESENT values. Note that this is for a customer with users that would be totally incapable of building queries or filters any other way.

An example would be to retrieve projects where ProjectManager was "Joe Expert" (combo box) and CompletionDate was "after 12/31/2016" (text box). Of those projects, I would only be interested in those where crafts included "Electrical" work (check box) or "Plumbing" work (check box) or with "Schools" as a vertical market (check box). So the first two would be MUST MEETs and the last three would be INCLUDE IF PRESENTs.

The approach I am considering would be to first allow the MUST MEETs to be designated by the user by having, for instance, their associated label's background color set to green by a double-click event. Any control without a green label would be an INCLUDE IF PRESENT. When the criteria were all selected on the form, the user would hit a button which would first (in VBA) loop through and examine all the form's controls and store references to the MUST MEETs in…
0
Good Afternoon,

I am working to create a simple database for a group of 6 users.  They are currently working from an Excel spreadsheet and updating results manually as they review records.  I am creating the database to get them away from the spreadsheet and into something much more stable.

The main table in my database has approximately 5035 records.  Each user will be assigned 200 records at a time to review.  I need a simple...and I do mean simple...way for the manager to assign groups of records from the main table to each analyst as needed.

I am not fluent in VBA and am currently using Access 2010.

Help is greatly appreciated!
Sheri
0
Cloud Class® Course: MCSA MCSE Windows Server 2012
LVL 12
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Hi,

I am using the following code on opening an excel workbook. I am hiding some sheets and opening only one of the sheets with the following code.

Additionally, I would like to fade out the sheet on opening.

Private Sub Workbook_Open()

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> Sheet8.Name Then
        ws.Visible = xlSheetVeryHidden
    End If
 Next
 
 With DisclaimerForm
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Show
 End With
 Application.ScreenUpdating = False
End Sub

Open in new window


Also, there is a popup on opening. If the user selects the checkbox I use the following code. As soon as the user checks the check box, I would like to unfade everything.
Private Sub CheckBox1_Click()
Dim ws As Worksheet
 
Application.ScreenUpdating = False
 
If Me.CheckBox1.Value = True Then
    Application.ScreenUpdating = False
    'ThisWorkbook.Unprotect ""
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> Sheet8.Name Then
            ws.Visible = xlSheetVisible
        End If
    Next
    End If
    Unload Me
End Sub

Open in new window


How should I accomplish fading and unfading?

Thanks
0
I develop VBA add-in for Office apps and utilise the customUI XML framework to specify the UI.

I have often wondered if it's possible to avoid the situation where I need the same control to appear in multiple parts of the UI without having to create multiple unique control IDs and hence manage that in the callback code. For example, let's say I need a button in a right-click content menu "Show Object Info" and I need that feature to appear for multiple different selection types. Can I use qualified IDs (idQ) to achieve this somehow. Basically, how can I have duplicate IDs in the same XML definition whose callbacks fire the same code in a VBA add-in?

Example working snippet using the unique IDs "Test1" and "Test2":

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<contextMenus>
		<contextMenu idMso="ContextMenuPicture">
			<button id="Test1" label="Show Object Info" onAction="OnAction"/>
		</contextMenu>
		<contextMenu idMso="ContextMenuTable">
			<button id="Test2" label="Show Object Info" onAction="OnAction"/>
		</contextMenu>
	</contextMenus>
</customUI>

Open in new window


Example snippet using a single duplicate non-unique qualified IDs "Test" which presents the control in the UI but the callbacks don't work (no code is run):

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" xmlns:yp="http://youpresent.co.uk">
	<contextMenus>
		<contextMenu idMso="ContextMenuPicture">
			<button idQ="yp:Test" label="Show Object Info" onAction="OnAction"/>
		</contextMenu>
		<contextMenu idMso="ContextMenuTable">
			<button idQ="yp:Test" label="Show Object Info" onAction="OnAction"/>
		</contextMenu>
	</contextMenus>
</customUI>

Open in new window

0
if a complete row is completely same with any other row then paste the data to sheet2 and if not then paste to sheet3
i have to do all this by vba
highlighted colour is only for understanding purpose in actual file there is no highlighted colour
sample-file.xlsm
0
I found a snippet of VBA code written by Alex Paras online that pulls from password protected websites. When running this code, a Security Dialog prompting for a username/password appears automatically. Nonetheless, I noticed some machines at my company do not show the dialog box asking for the username/password and therefore the code no longer works.

All machines have Excel 2016 and the most recent patch. The code snippet is below. I'm sorry as I know this is not much to go on. Any ideas on what would cause a username/password dialog not to appear when running query tables?

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.urbics.com/Urbics.php?hs=a97cd706q9948q11e1qbbacq00259002436c&pgreq=AVTotals&aid=4517", Destination:=Range("$B$4"))
.Name = "q?s=goog_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1,2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

End With
0
Plz look into my actual file
if a row is completely same with any other row in  a sheet then delete that complete row
i am attaching my actual file plz look into it
i have 150000 rows , in the attachment i have puuted half data
i have to do this by vba only
Book1.xlsb
0
Hello to all of you.

I would like your help on a macro on excel

For the attached file, I want a macro that does :

1) it compares all the cells in B column with the number that I have in cell  I1. This number will be 6 in some cases, 5 in others, 7 etc ...

2) when it finds a cell in B column that has a number equal or greater than the cell in I1 (6 in this case), to copy the deg of that cell
in H column of the newSheet8.

for that case of 6, the H column of newSheet8 would have the degs:
3
20
26
33
56
etc


Any help?
0
I have a general question which I will describe using the following specific example.

Let's say I want a function that shows the current setting for Excel's Find dialog.  For instance if I had recently did a find looking for Values I want the function to return xlValues.


I know how to change the current lookin value, but I do not know how to extract the previously value.
For instance, the following subroutine will open the find dialog and change the current setting to be xlValues.

FUNCTION ShowCurrentFindLookIn()
Application.Dialogs(xlDialogFormulaFind).Show ,xlValues
end sub

I am really week when it comes to dialogs.  For instance when I look here
I see that xldialogformulafind has 6 parameters named text, in_num, at_num, by_num, dir_num, match_case, match_byte.

I only understand  the first 2, and cannot figure out the others.  Nor can I extract the current values of any of them -- they seem to be "input only" parameters.

I am really confused and hope someone can straighten me out.

rberke.

P.S.    I am sure someone will ask "why do you want to do this?"  so let me explain. Literally hundreds of times a day I use Excel's search xlFormulas.   I type ctrl f  "credit" {enter} without looking at the dialog.  Every once in while this practice burns me,  because I do not notice that I had recently looked at xlValues.

A ShowCurrentFindLookIn …
0
Hello,
is it possible to create a spreadsheet which will automatically assign date and time in one cell on the action?
I would like to scan a barcode in cell A1 and when I scan, excel will automatically assign date and time (with minutes and seconds if can) of that scan in cell B1. So I can track when was each scan created or scanned.
Also, is it possible to create VBA scrips where that Excel spreadsheet will automatically on a predefined time in a day do file/save as on specific folder in Desktop?
Thanks in advance.
Nikola
0
My requirement is to grab two texts between two flags placed in word file and write in to excel - 2 columns
Flags can be [mydate] and [/mydate]
and [mytext] and [/mytext]

e.g. [mydate] 2018-07-14 [/mydate] blah blah [mytext] this is my text [/mytext]

It should be repeated until all occurrence in the file.

I am using following code as guided by GrahamSkan

Option Explicit

Sub GrabUsage()
Dim FName As String, FD As FileDialog

Dim WApp As Word.Application 'Object
Dim WDoc As Word.Document 'Object
Dim WRange1 As Word.Range
Dim WRange2 As Word.Range

Dim ExWks As Excel.Worksheet
Dim ExR As Excel.Range

Dim r As Integer
Dim bTesting As Boolean
Dim bVisible As Boolean
Dim bNewWordApp As Boolean
Dim bFinish As Boolean

    bTesting = True
    Set ExWks = Selection.Worksheet
    r = 1

    If bTesting Then
       FName = "I:\Allwork\ee\29108962\InDoc.docx"
    Else
        'let's select the WORD doc
        Set FD = Application.FileDialog(msoFileDialogOpen)
        FD.Show
        If FD.SelectedItems.Count <> 0 Then
            FName = FD.SelectedItems(1)
        Else
            Exit Sub
        End If
    End If
    
    ' open Word application if necessary and load doc
    On Error Resume Next 'supress error checking for one line only
        Set WApp = GetObject(, "Word.Application")
    On Error GoTo 0
    If WApp Is Nothing Then
        Set WApp = CreateObject("Word.Application")
        bNewWordApp = True
    End If
    'make word application visible after 

Open in new window

0
Cloud Class® Course: CompTIA Cloud+
LVL 12
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

I created several macros in MS Word for a letter I am using.

When I send the letter (email) the letter the macros do not transfer to the next users computer?

How do I get the MS Word macros to transfer with the letter when I send it?
0
Below is a section of MS Word VBA code which removes duplicate paragraphs from a document. It retains the first instance of each paragraph it finds, and deletes any subsequent instances ("duplicates").  Does anyone know how I might amend the code so that instead:

1) it deletes the first and all subsequent instances of each paragraph except the last instance (the one closest to the bottom of the document) which it retains? PS: since posting I have figured this one out.

2) It deletes all instances of a paragraph which has duplicates, retaining none?

3) In the process of trying to solve (2) I realise that I don't really understand how the 'collect duplicates' section works. If anyone could explain that to me I would be very grateful!

Sub DeleteDuplicateParagraphs()
  Dim p As Paragraph
  Dim d As New Scripting.Dictionary
  Dim t As Variant
  Dim i As Integer
  Dim StartTime As Single

  StartTime = Timer

  ' collect duplicates
  For Each p In ActiveDocument.Paragraphs
    t = p.Range.Text
    If t <> vbCr Then
      If Not d.Exists(t) Then d.Add t, New Scripting.Dictionary
      d(t).Add d(t).Count + 1, p
    End If
  Next

  ' eliminate duplicates
  Application.ScreenUpdating = False
  For Each t In d
    For i = 2 To d(t).Count
      d(t)(i).Range.Delete
    Next
  Next
  Application.ScreenUpdating = True

  MsgBox "This code ran successfully in " & Round(Timer - StartTime, 2) & " seconds", vbInformation
End Sub

Open in new window

0
Is it possible to send an email through Word VBA (0365), where I can add 2 attachments, use a specific email template that is stored on a network drive and have that users signature added to the email template.

I have got several different attempts working using code examples off the net that allows me to send emails but only at the sacrifice of one of the above requirements.
0
Good Morning,

I have a simple excel template that keeps a record of job..

I want to add some functionality using VBA so that when a job is added the the spreadsheet it automatically emails the row data for that job.

I would like the VBA to run and check for new data when the spreadsheet closes automatically together with the email

0
Hi ,
    I am looking for an excel macro( or anything to get the functionality) to do following
   
   In  my excel sheet (Excel 2013)    Column A  ( A2 - A300)   has list of folders ( RelativePath)      Source is  D:\Vault  (For example ,if the relative path is  FoldMain\Fold1  the fullpath is D:\Vault\FoldMain\Fold1)

    The macro need to copy the folders in the list to a given relative destination ( For example   D:\Vault\FoldMain\Fold1   should copy to D:\VaultCopy\FoldMain\Fold1
     
      The folders in the list can have subfolders in it,   the subfolder also should be copied    (For example if D:\Vault\FoldMain\Fold1  has a folder Fold1Sub in it, that should be copied )
0
Hi All,
I'm working with an MS Word template using fields linked back to another system.  The text in the fields are a variety of color codings.  I'm trying to create a macro that, On Open, will set all of the text to black.  Can anyone help me?  Thank you!
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.