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'v create a project knows as item banking question. each item has its doc file and connect by its file path name. here is the problem.

i have selected item 1, 3, 4 and 6 (tick as yes) in query. each item has file path name as below:
    item 1 as D:/1.doc
    item 3 as D:/3.doc
    item 4 as D:/4.doc
    item 6 as D:/6.doc

i want to copy all selected doc file using vba in access. anyone can help?
0
Announcing the Most Valuable Experts of 2016
LVL 6
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Ever since i added the 3rd worksheet to my workbook, I'm getting a run time error 9, out of script every time i attempt to click on the Auto Liab Only button on Sheet1.  schedules-revised.xlsm

Any help would be greatly appreciated.
0
i have lists of words in excel column across 20k rows

COL A                                      COL B
apple, orange, pear           www.fruit.com
beer, wine, rum                   www.drinks.com

i need it to do this

COL A                                    COL B
apple                                  www.fruit.com
orange                                 www.fruit.com
pear                                 www.fruit.com
beer                                 www.drinks.com
wine                               www.drinks.com
rum                                 www.drinks.com
0
Hi,
On the attached spreadsheet I need to pull the row "Total" for each sales Person, from the  " Final Amount" column "I"  to a new sheet.
In the new sheet I need the name to show up with all the middle fields empty( but still showing up)
I tried to do a vlookup but it does not look like the smart way to do it.
I have put in a result tab to show what I need the pulled data to look like.
Thanks
Testing-file-prev-period-data-pull.xlsm
0
I sometimes notice one or more icons with an Expert's picture at the bottom of my questions with the label Viewing. I am just curious and wondering what does that indicate.
0
I am using Microsoft Access 2013 (I think) and I use DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml,
"File Name", "C:\Detailed\MyView" & ".xlsx", True,

The workbook page names comes out as "File_Name" I need the space. Thanks for any help.

Graham
0
hi there,

I'm sure there's quick, easy way to do this but my lack of sleep is hindering my thinking so hoping someone can lend a hand:
I have 3 reports (each different). They are based off one query. I need to run a vba code that pulls the right report based on what's in the table.

Here is the code:
 If DLookup("Cat", "qryList") = "A" Then
        stDocName = "rptA"
        DoCmd.OpenReport stDocName, acPreview
    ElseIf DLookup("Cat", "qryList") = "B" Then
        stDocName = "rptB"
        DoCmd.OpenReport stDocName, acPreview
    ElseIf DLookup("Cat", "qrylist") = "C" Then
        stDocName = "rptC"
        DoCmd.OpenReport stDocName, acPreview

    End If

Open in new window


What I want to do is:
1. User selects an item
2. Item in table has A, B and C records in the following order:  A, B, A, A, C
3. Report pulls up A rpt, B rpt, A rpt, A rpt and C rpt respectively.

Is this possible? Hope that made sense.
I just need some guidance/ideas.

Thanks in advance.
0
Experts...

I had an Access report that worked fine. I was asked to embed a sub report into the main report to include more information. In Access the Report/sub report looks fine, but when I view the report in Print Preview mode, I see a multi-page report where
-the main report header section displays fine, but the remainder of the main report is covered by the subreport on page 1
-in the remaining pages, the subreport is fixed and takes up all the real estate of the page, while the main report scrolls in only the top inch of the page. So
Page 1 = Header and subreport
Page 2 = 1st inch of main report at the top + fixed subreport
Page 3 = 2nd inch of main report at the top + fixed subreport
etc

The parent report can scroll, but only in the top inch that displays the header!

So what am I doing wrong? I know this must have something to do with my subreport being in the Page Footer section of the main report
0
Hi,

I have attached a spreadsheet with some code and functions, but I want to know if I can achieve the following.

Spreadsheet has 7 tabs.  The tabs I am interested in is tabs 2 - 7

Each tab has one row of data.  Now if you go to tab 2 - Risks, the go to column J and in the dropbox click Action.  You will now see that the row has gone.

Go to the Action tab, and you will see that the row appears here, and that is correct.  I column B you will see an R.  As it was moved from the Risk tab, it was enetered as a R for Risk.  But as it now resideds in Action, I want the cell automatically update to an A.

This movement and changes will only aplly for rows that move between Tabs 2-4,  Risk, Action, Isse and Dependancy.

Please let me know if any more info is needed.
Sample-Project-Raid-Log-0.1a.xlsm
0
I am implementing a close button to an excel file that is going to be accessed from a link on our intranet.

Once the user has finished using the file, they can close the file. I also want to check if there are any other Excel files open and if not also close the Application. Current script is:

Sub ExitFile()
    FileCount = Application.Workbooks.Count
    If FileCount = 1 Then
        ActiveWorkbook.Close SaveChanges:=False
    Else
        Application.ActiveWindow.Close
        SendKeys ("%{F4}")
    End If
End Sub

Open in new window


Currently this does not work, it closes the file but does not close the Application. I have put a stop in after the FileCount and it confirms that there is only one file open.

I have tried various statements for closing the application, including SendKeys as above, but it still doesn't work.

Please help; I am sure this is a simple thing but I can't seem to get it this morning.

Thanks
Rob
0
[Webinar] How Hackers Steal Your Credentials
LVL 9
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

then ORD-M sheet and ORD_VER need have column J
29046162ordordmGGG4al.xlsm
0
How would I write a vba inputbox (Ok and Cancel) that does the following:
-dialog title of "Enter ECM"
-gives user a head start with a value of 5000000
-result needs to be 12 characters and begin with a "5".  Error: You entered an invalid value: strECM  'That's my InputBox variable name, please try again
-Cancel button or Red X in upper right corner, Exits Sub
-Blank input, and chars < 12, errors and says try again.

So, 500000012370 is a valid input,    400000012370 is not.  50000001237 is not....
Please advise and thanks.
0
then ORD sheet  and ORD_VER need have column I
29046156--1-NUM-MG.xlsm
0
then NUM-M  and NUM_VER  need have column G
num4all.xlsm
0
I would like to Find the word {"keyword", "second", "third", "etc"} and highlight in Bold or yellow on all slide of the ppt. In shape, groupitem, table, chart, notespage etc. A below code works only in shape.

Could you please help me to modify the below code to work also in table, smartart, chart, notepage etc..

option Explicit

Sub HighlightKeywords()
Dim sld As Slide
Dim shp As Shape
Dim txtRng As TextRange, rngFound As TextRange
Dim i As Long, n As Long
Dim TargetList

'~~>  Array of terms to search for
TargetList = Array("keyword", "second", "third", "etc")

'~~> Loop through each slide
For Each sld In Application.ActivePresentation.Slides
'~~> Loop through each shape
For Each shp In sld.Shapes
    '~~> Check if it has text
    If shp.HasTextFrame Then
        Set txtRng = shp.TextFrame.TextRange

        For i = 0 To UBound(TargetList)
            '~~> Find the text
            Set rngFound = txtRng.Find(TargetList(i))

            '~~~> If found
            Do While Not rngFound Is Nothing
                '~~> Set the marker so that the next find starts from here
                n = rngFound.Start + 1
                '~~> Chnage attributes
                With rngFound.Font
                    .Bold = msoTrue
                    .Underline = msoTrue
                    .Italic = msoTrue
                    '~~> Find Next instance
                    Set rngFound = txtRng.Find(TargetList(i), n)
                End With
     …
0
How can I merge multiple rows into one cell delimited by a semi-colon  - Thank you
0
Hi Experts,

In Outlook, is there a way to create a custom group  summary?

For example, currently the default summary reads:   Categories: My Category (21 items)

Can I add to that with SUM(hours), where hours is number column to read: Categories: My Category (21 items, 30hours)

Any help will be greatly appreciated.
0
Hi Experts!

    I have some code that I'm using to create a checkoff list with.  The code was working great until I adjusted the control buttons at the top.  Basically all i did was space them out more evenly and made the timer box bigger.  In doing this I had to delete the first text box and it's checkbox at the top of the form to make room for the adjustment.   Once complete, I went to launch the macro, It's giving me a run-time error now.  Would one of you .vba guru's take a look at this to see where I'm going wrong at and what I need to do to correct it?  Attached is the run-time error itself along with the debug item that it's choking on within the form code.  Thanks a million for your help!

Run-time error
Debug Error within Form Code
0
Master I do have here a file for operation and maintenance.I want some codes before the user will open the excel,it will pop up and ask for the user name and password.It will record the log in & log out of the user on another sheet.Please see the attached file below.
WO_Maintenance-Form.xlsm
0
Free Tool: Path Explorer
LVL 9
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 Guys I just wanna ask.I do have here codes for generating Request # but its randomize and I need a combination of alpha-numberic characters that will generate request # by sequence not randomize and combine with aleast 2 letters.Please see the attached file below.
WO_Maintenance-Form.xlsm
0
I have the following statements:

Set Dbs = CurrentDb()
Set xR = Dbs.OpenRecordset("Select Rm1Bd1,Rm1Bd2,Rm1Bd3,Rm1Bd4 FROM QUOTESDETAILS Where Resid = 1")

I need to create the sum of all of the variables. Is it possible to use something similar to this:

For I = 1 to 4
  xSum = xSum + xR!["Rm"&I]
Next

Thank you for any help!
0
What is proper VBA code to forward an e-mail, even if Outlook is Closed, if the sender, of the received email,  is in my Contacts. Otherwise ignore email.

Cook09
0
Here is the code

Function EIGEN_JK(ByRef M As Variant) As Variant

'***************************************************************************
'**  Function computes the eigenvalues and eigenvectors for a real        **
'**  symmetric positive definite matrix using the "JK Method".  The       **
'**  first column of the return matrix contains the eigenvalues and       **
'**  the rest of the p+1 columns contain the eigenvectors.                **
'**  See:                                                                 **
'**  KAISER,H.F. (1972) "THE JK METHOD: A PROCEDURE FOR FINDING THE       **
'**  EIGENVALUES OF A REAL SYMMETRIC MATRIX", The Computer Journal,       **
'**  VOL.15, 271-273.                                                     **
'***************************************************************************

Dim A() As Variant, Ematrix() As Double
Dim i As Long, j As Long, k As Long, iter As Long, p As Long
Dim den As Double, hold As Double, Sin_ As Double, num As Double
Dim Sin2 As Double, Cos2 As Double, Cos_ As Double, Test As Double
Dim Tan2 As Double, Cot2 As Double, tmp As Double
Const eps As Double = 1E-16
   
    On Error GoTo EndProc
   
    A = M
    p = UBound(A, 1)
    ReDim Ematrix(1 To p, 1 To p + 1)
   
    For iter = 1 To 15
       
        'Orthogonalize pairs of columns in upper off diag
        For j = 1 To p - 1
            For k = j + 1 To p
               
                den = 0#
               …
0
Hi,
I currently have Excel importing data from an Access db, however, the database is moving to a Virtual Machine that we have with MS Azure.
I am unsure what I need to do to connect to the db once it's moved.  The Excel will still be on my local machine.
We currently login to the VM via Remote Desktop Connection.  Within the VBA, what do I need?  Do I need to enter the password (as I do when I connect via RDC)?

thanks in advance
Kerry
0
I'm trying to use VBA to insert my SQL Developer query into the command text for a connection to an Oracle database in excel. My SQL code is:

select * from
 (SELECT *
  FROM PS_JOB
  WHERE PS_JOB.EFFDT =
    (SELECT MAX(J.EFFDT)
    FROM PS_JOB J
    WHERE PS_JOB.EMPLID = J.EMPLID
    AND PS_JOB.EMPL_RCD = J.EMPL_RCD
    AND J.EFFDT        <= SYSDATE
    )
  AND PS_JOB.EFFSEQ =
    (SELECT MAX(J1.EFFSEQ)
    FROM PS_JOB J1
    WHERE PS_JOB.EMPLID = J1.EMPLID
    AND PS_JOB.EMPL_RCD = J1.EMPL_RCD
    AND J1.EFFDT        = PS_JOB.EFFDT
    )
  ) pj

but if I enter this into my VBA procedure, I get a syntax error.

Can someone advise how I can enter this code in VBA?
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.