VBA

11K

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

I am posting form data to a URL using VBA from MS Access.  However, I need to end up on the URL that I'm posting the form data to and I can't seem to do this since the url ends in .php.  How do i land on the same URL that i just posted to?
       
URL = "https://websiteX.com/tastein/enterxx.php"
        Set IE = Nothing
        Set IE = CreateObject("InternetExplorer.Application")
        IE.Navigate "about:blank"
        IE.Visible = True
       
        Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
       
        xmlhttp.Open "POST", URL, False
        xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        xmlhttp.send (sPayLoad)
        Response = xmlhttp.responseText
        While IE.busy
        DoEvents
        Wend
        IE.Document.Write Response
0
Fundamentals of JavaScript
LVL 13
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

I have a VBA add-in that works fine on lots of different Windows PCs.

Recently, several users reported that the add-in shows the following message numerous times, one after another, when trying to load either as an application add-in file or via the source macro-enabled Office file:

System Error &H8000FFFF (-2147418113). Catastrophic failure

Microsoft's help page that this message links to simply states "Visual Basic encountered an error that was generated by the system or an external component."!

After repeatedly clicking OK or or holding the Enter key to dismiss this repeating message, the following message is eventually displayed:

Compiler error. Out of memory.

I have issolated it to a specific model of Dell XPS laptop but they are well spec'd with Windows 10 Pro 64 bit, 16GB RAM and 2.2 GHz Intel Core i7.

The project has many modules/userforms/classes so it's not possible to post all of the code here, or even easily issolate where it's going wrong due to the nature of the error messages and behaviour of VBE after dismissing them.

Once out of the error message loop above, the following text in bold is highlighed in a code moule window:

Optional ByVal CompareMode As VbCompareMethod = vbTextCompare, _

Public Function QSortInPlace( _
    ByRef InputArray As Variant, _
    Optional ByVal LB As Long = -1&, _
    Optional ByVal UB As Long = -1&, _
    Optional ByVal Descending As Boolean = False, _
    Optional ByVal 

Open in new window

0
I have a simple Update query and I can not understand why I'm getting this parameter request.

When I call DoCmd.OpenQuery "qCreateOeReportDueDate" I am getting the "enter parameter" request as seen in the picture attached.

What is triggering this request? What prerequisite is not being completed to trigger this?

query qCreateOeReportDueDate
UPDATE ProblemLog SET ProblemLog.[OE Report Due Date] = [QREVALUE_DueDate];

Open in new window


FUNCTION THAT IS BEING RUN TO TRIGGER THE REQUESTParameter Request
Public Function CreateProblemLog()
DoCmd.SetWarnings False
Module1.DeleteXLLog
DoCmd.OpenQuery "qMakeProblemLogTable"
DoCmd.OpenQuery "qCreateOeReportDueDate"
DoCmd.OpenQuery "qUpdateProblemLogMonthDayYear"
CurrentDb().TableDefs("ProblemLog").Fields("ScrapRecordTag").Name = "DIAMTracking"
CurrentDb().TableDefs("ProblemLog").Fields("SkpiProblemLog_Date").Name = "Occurance Date at Customer"
CurrentDb().TableDefs("ProblemLog").Fields("MyMonth").Name = "Month"
CurrentDb().TableDefs("ProblemLog").Fields("MyDay").Name = "Day"
CurrentDb().TableDefs("ProblemLog").Fields("MyYear").Name = "Year"
CurrentDb().TableDefs("ProblemLog").Fields("TagNumber").Name = "Customer Claim #"
CurrentDb().TableDefs("ProblemLog").Fields("QPRQPINumber").Name = "Other Tracking #'s (RMA#, SLIPS#, e-CIMS, etc)"
CurrentDb().TableDefs("ProblemLog").Fields("Customer").Name = "Customer"
CurrentDb().TableDefs("ProblemLog").Fields("Quantity").Name = "Final Reject Qty"

Open in new window

0
I have an MS Access database and need to interface with a C# DLL that is about to be created for me (as a COM object).  My C# guy has no experience in interfacing with MS Access, and I have only interfaced Access with COM objects that I have purchased (EASendMail etc).  

We have run a few tests, and I can half interface with his test DLL (once referenced), but the Intelisense isn't working correctly.  However, whilst I am sure that we can overcome that one easily enough, it's the return messages that I am struggling to get my head around.  How do I tell Access to listen for them and then, trigger an event/function?

I know this is a little vague - but I could really do with some pointers to get started.
0
Let's say I have two tables:  TableA and TableB - FieldA has some values similar in FieldB and I want to do a compare.
The problem with TableB and it's FieldB - the values are longer than what's found in TableA, FieldA.  

To explain further I provided a sample database:

Table A - FieldA
University191803ABC
UNIVERSITY1923032304
University192305H2307
UNIVERSITY19121F51
UL158

TableB - FieldB has:
UNIVERSITY19251
University191803
UNIVERSITY20101
UNIVERSITY192303
UL1580AH14L

I want a query listing with a join to show the following results:

(Table A, TableB combined)

FieldB, FieldA
University191803ABC, University191803
UNIVERSITY1923032304, UNIVERSITY192303
UL158, UL1580AH14L

These match because the first set of characters match- from the start of the value.  

(University191803 for the first one.  University192303 for the second row.)
The first two are 19 characters that match whereas the last example grabs the first 5 cause of the smaller value: UL158....

How would I go about creating such a query in Microsoft Access?
ee-joinleftdata.accdb
0
Hello.  I am trying to Capture Style Header Data In A Variable using VBA. I have code that I am using to Loop thru a Word Document.
 I am looking for all "TBD"s Ex. <TBD 6.1>.  I have the code to find the TBD.  I look for it 2 ways.  I look for the ones in a Table.  I also look for the ones that are not in a Table.
The code I have finds and captures all of the data I need for the Table.  I need help with the "TBD"s that are NOT in a Table.

When A TBD that is not in a Table is found, I need to capture the data in the Style Header 1 or Style Header 2 or Style Header 3 or Style Header 4, etc.
Whichever Style Header that is above that TBD needs to be found.   When it is found, I need to capture that data in a variable.  Please see the Sample Attachment.
When <TBD 1-4> is found, I need the data in Header 3 captured in a variable.  1.1.2  AVENGERS TASKS (IN DESCENDING PRIORITIZED ORDER)
I am running this code from MS Access.  When the data is captured, it will added to  an MS Access Table one at a time.  I have that code and it is working.

 Now a few things.  This is the big picture of what I need.
I don't need an Array.  I need to find and capture them one at a time.
When TBD is found, somehow mark that location.  
Capture the data of the Style Header above that TBD in a variable.
When the data of the Style Header is captured,  I need to go back to the TBD that was found so the search for the next TBD starts there.
       The data will be added to the MS Access …
0
I have an Access front end and SQL back end.  I have a form that has a web browser on it.  The web browser points to a particular control named txtFunctionalLocation to complete the Control Source's URL .  This web browser is kept hidden on the form by an On Open event.  However, once the cboFacilitySearchcombo box is utilized to search for a particular project, it activates the macro below to make the Web Browser control visible.

macro.jpg
The problem I have is that sometimes there's an invalid (placeholder) in the txtFunctionalLocation control box that causes errors in the web browser control.  The error comes from an invalid URL.  The place holder in this position is a numeric integer.  The real Functional Location value will always start with "X" (or possibly some other letter in the distant future).  I need to figure out how to convert the macro pasted above into a VBA code to carry the same functions but only make the web browser control visible if the txtFunctionalLocation control has a value starting with "X" (or any letter).  The "X" is usually followed by a series of numbers.

I found this article:
https://support.office.com/en-us/article/use-like-criterion-to-locate-data-65b07c8a-b314-435a-8b48-2b911856d4f9 and was thinking of maybe [!0-9]* but I'm not sure if it would be correct and how to structure the VBA with everything.
0
What I am trying to do
      I need to print a slip without giving the option to the user to print the same slip again or guess the next MySerial Number
      
      
      
      What I wish is some VB Coding, whereby
      When I click on "Print the Slip" Command Button
      Value only is pasted in $L$1 is using the formula =VLOOKUP(L2,MyRange,2,FALSE)
      Value only is pasted in $L$2 is using the formula =PrintSr
      User is given a timer message of 20 seconds to check whether the printing is OK or REPRINT the Slip again if required
      If the User chooses "OK' on the message box OR if the 20 second time window expires, then
a      the Cell in row Corresponding to Value given in cell $L$2 above (i.e., PrintSr) Column C on sheet RangeNo in MyRange is Marked "Yes"
      e.g., if we Press "Print the Slip", then the Cell C8 on Sheet RangeNo corresponding to serial number 7 in Column A is Marked "Yes"
      resulting in changing the value of "PrintSr" to 8 and consuming the Number 7 in the List
b      MyRange is sorted in ascending order based on Column D

EEE0001.xlsm
0
My customer has changed their portal login page and my download code no longer works. I think the only change was the actual home login page.

I'm trying to re-code this in order to have Access login and Navigate to some data that I download daily. The target webpage recently re-wrote their login page. Therefore, my login system no longer works. I think the back-end is still good. It's just the login that has me messed up.

FUNCTION THAT ENTERS LOGIN VARIABLES IN ACCESS
I removed most of it. I'm only listing the code where my variables are not transferring to the target web-page.

**I did NOT include everything because I didn't want to populate the page with code that is NOT needed here.**
Access Code
 For Each drp1 In QPR.Document.Forms
 'MsgBox drp1.Name
    If drp1.Name = "Login" Then
         With QPR.Document.Forms("Login")
        .User.Value = (myUserName)
        .Password.Value = (myPassWord)
        .submit
        End With
    End If
  Next drp1
 

Open in new window



CODE WHERE I SET MY VARIABLES IN ACCESS

Private Sub Command155_Click()
'Assign the user inputs to variables that will be used in the Web application.
'Tell VBA what to do when the user does not input a date format
On Error GoTo NotADate

    myStartDate = Format(CDate(txt_StartDate), "mm/dd/yyyy")
    myEndDate = Format(CDate(txt_EndDate), "mm/dd/yyyy")
    myNAMC = txt_NAMC
    myUserName = txt_UserName
    myPassWord = txt_Password

    'Inform the user that this program is operating 

Open in new window

0
I have an Office VBA add-in that's causing a problem with one issolated user amongst many others that are not experiencing the the same issue. I've issolated it to the Windows API Sleep function.

This is the declaration I'm using, the 64 bit version coming from official Microsoft documentation (I also tried LongLong instead of Long):

#If VBA6 Then
  ' from Microsoft's Win32API_PtrSage.txt
  Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
  Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Open in new window


When trying to call this with this example in the same module:

Public Sub TestSleep()
    Sleep 1000
End Sub

Open in new window


This particular user sees this error message:

"453 Can't find DLL entry point Sleep in kernel32"

They are using Office 2016, 32 bit (build 10730) on Windows 10, 64 bit and I have had them check the version of the kernel32.dll which is 10.0.16299.1029, 3/6/2019 (my PC has 10.0.1734.648, 06/03/2019)

What can I do now to establish the cause of the issue and fix it?
0
Exploring ASP.NET Core: Fundamentals
LVL 13
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Hi

I am running a macro that splits data and add rows etc. What I would like to do now is get one cell to equal another. I want the user to click on a cell (so this will be the active cell) and the macro goes to the bottom row and the active cell will equal this. I hope this makes sense. Please let me know if I can explain anything further.

TIA
0
Hello experts,

Since today I have the following error message after execution of a vba procedure which use ADODB.Connection.
2019-04-08_16h38_08.png
Sub SQLQueryOut2(wsName As String, strSQL, strInitialRange, strServer)

Dim objRS
Dim SQL

Set wb = ActiveWorkbook
Set DestSh = Nothing
On Error Resume Next
Set DestSh = Sheets(wsName)
Set wsConfig = Worksheets("Config")
On Error GoTo 0

    If DestSh Is Nothing Then
        Set DestSh = wb.Sheets.Add(After:=Sheets(wb.Sheets.Count))
        DestSh.Name = wsName
        wsConfig.Select
    Else
        Sheets(wsName).Cells.ClearContents
        wsConfig.Select
    End If
    
Set objRS = CreateObject("ADODB.Recordset")


Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=" & strServer & ";" & _
"UID=;" & _
"PWD=;" & _
"Database=;"


SQL = strSQL
objRS.Open SQL, objConn

On Error Resume Next
Set rs = objConn.Execute(SQL)
On Error GoTo 0
If rs Is Nothing Then
MsgBox "SQL query reported at row " & rw & "  is not properly set up unable to transfer  data."
Exit Sub
End If

 For Idx = 1 To rs.Fields.Count
        Sheets(wsName).Range(strInitialRange).Offset(0, Idx - 1) = rs.Fields(Idx - 1).Name
    Next

    Sheets(wsName).Range(strInitialRange).Offset(1).CopyFromRecordset rs

Set objRS = Nothing
Set objConn = Nothing

End Sub

Open in new window

Any change has been performed from my side.

Do you have advice on this?

Thank you very much for your help.
0
How do I run macros in all open workbooks

I have 6 macros to be available in any workbook that I open. I have tried saving as personal workbook, but it only works if i open personal workbook is there a way that i can do it without personal workbook
0
Hello All,

I have enabled a Incident report on Transport Rules in Exchange 2016. It sends a below information to specific mailbox.

I would like to get this as report in Csv format. where the below mentioned headings as colums. Is it possible?

I already tried the Search-Mailbox -logonly option, but it does not show the below information in csv.


Report Id: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 This email was automatically generated by the Generate Incident Report action.
 Message Id: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 Sender: xxxxxxxxxxxxxxxxxx
 Subject : ABC
 To: xyz@abc.com
 Severity: High
 Override: No
 False Positive: No
 Data Classification: Credit Card Number, Count: 1, Unique Count: 1, Confidence: 80, Recommended Minimum Confidence: 85, Location: CCD.txt
 Rule Hit: Sensitive information, Action: AuditSeverityLevel, RejectMessage, GenerateIncidentReport

 Location: CCD.txt
 ID Match: Credit Card Number, Value: xxxxxxxxxxxxx,
 Context: Credit Card xxxxxxxxxxxxxxx

Thanks
Krish
0
Is there any way where we can copy the values during vlookup and paste them in a location and makes the values exist even when the reference sheet gets deleted.
0
Background

I have a piece of VBA code which deletes multiple rows on a worksheet depending on if the values on each row are TRUE or FALSE in a specific column (a 'conrtol range'). The worksheets as switched as a master loop (above the level of this function) that cycles through multiple sheets and workbooks.

This is used in the Excel 2013 and 2016

Naturally the code searches for matching rows and builds up a range object and only once the loop has ended is that rage deleted.

Code extract

I can't send a copy of the actual workbook because it is too large, too complicated and I don't have time to explain how it works - besides it is confidential.

I have listed below an extract of the key code that is failing (with the command that fails in bold).

    ' Build up a list of rows which are valid
    For Each rngCurControlCell In rngSheetControlRange.Cells
    
        If rngCurControlCell.Value = True Then
        
            If rngRowsToDelete Is Nothing Then
                Set rngRowsToDelete = rngCurControlCell.EntireRow
            Else
                Set rngRowsToDelete = Union(rngCurControlCell.EntireRow, rngRowsToDelete)
            End If
            
        End If 'rngCurControlCell.Value = True
    
    Next rngCurControlCell
    
    ' Delete the rows
   If Not rngRowsToDelete Is Nothing Then rngRowsToDelete.Delete

Open in new window


Problem / Issue

Sometimes this code works and sometimes it does not.

I have put various debugging in the code and can confirm that the code always finds the target rows ('rngRowsToDelete ' in the extract above) and always builds up the range object correctly.

HOWEVER ... sometimes the delete works and sometimes it does not - even though it it run against the same worksheet. No error is generated.

Has anyone come across a similar situation / have any thoughts ?
0
Please be gentle, I'm learning and I haven't worked with RecordSets and there are likely to be a number of "concerns" regarding the following code!   My learning issue is that I have a Word userform and I'm accessing an Excel spreadsheet that has 4 rows.

Field 1 -  Field 2 -    Field 3
1              Tom         Smith
2              Dave        Brown
3              Sally         Jones
4              Jane         Frank


I CAN search and return records on Field 1 (the number) but not Fields 2 or 3.  This might be something to do with how the record set is handling rows or fields but I don't understand what is happening at "search time" that works fine on Field 1 but not Fields 2 and 3.    

A couple of pointers to get me going again would be appreciated - I'm stuck!

Many thanks
Tolan

My code:

' Connecting to an Excel Dataset using ADODB.
    Dim RS As ADODB.Recordset, rsCount As ADODB.Recordset
    Dim cN As ADODB.Connection
    Dim sqlGetTbl As String
    Dim sDataSource As String, sDataTable As String
    Dim sProvider As String

' set the recordset as the file and spreadsheet
    Set cN = New ADODB.Connection
    Set RS = New ADODB.Recordset
    sDataSource = "\\server\user\fred\FeeEarnerSigList.xlsx"
    sDataTable = "[Sheet1$]"

' the format to be used
    sProvider = "Microsoft.ACE.OLEDB.12.0;"
    sDataSource = sDataSource & ";Extended Properties = 'Excel 12.0 Xml;HDR=Yes;IMEX=1';"

' open the spreadsheet
0
Hello all

Another search question I have this form that I need to extract certain data based on the headers.  So the criteria is I need to search from A:AY for "6. File Number"  there is data after I need that data in sheet4.a1.  then I need to search for "Address of Borrower" and copy the data after thatn in sheet.b1.
0
I have data in cell A47, that has a parenthesis at the end.  I need to extract just the numbers and leave out the ")"

Example is  77,965.52)  I need a formula to give me 77,965.52.
0
CompTIA Network+
LVL 13
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Hi Experts,  I copied a code from net and the code is able to obtain Hard Drive Serial number in Windows 10 but not in Windows Xp. Is there anyone who can help me using the code below to get the HDD S/N in Windows Xp? Any help please... thanks!


Private Sub Command2_Click()
'Do not disAble
Dim oWMI As Object
Dim sComputer As String
Dim oItems As Object
Dim oItem As Object
 
'your Computer
sComputer = "."
 
' WMI-Objekt
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" _
  & sComputer & "\root\cimv2")
 
' WMI-query
Set oItems = oWMI.ExecQuery("Select * from Win32_DiskDrive")
For Each oItem In oItems
  On Error Resume Next
  
  List1.AddItem "Index: " & oItem.Index
  List1.AddItem "InterfaceType: " & oItem.InterfaceType
  List1.AddItem "DeviceID: " & oItem.DeviceID
  List1.AddItem "Model: " & oItem.Model
 

 List1.AddItem "SerialNo: " & oItem.SerialNumber
  List1.AddItem "-------------------------------------" & vbCrLf
  On Error GoTo 0
Next
Set oItem = Nothing
Set oItems = Nothing
Set oWMI = Nothing
End Sub

Open in new window

0
I have a data in column a from row 1 to row 288, I need to find the cell that has the words "OPEN END MORTGAGE DEED" and copy that cell and all the cells below it into Sheet "Step1" Cell A1
0
I have an excel workbook I created with

DoCmd.OutputTo acOutputQuery, "qry_chart_sold_ppsf_year", acFormatXLSX, sfilename, Autostart:=False

I would like to add 3 additional worksheets to that workbook, names worksheet_1. worksheet_2, and worksheet_3.

The data for the worksheets is from qry_1, qry_2, and qry_3

What would be ms vba code?

Thanks,

WCGee
0
I have a macro I adapted from another question on Experts Exchange (https://www.experts-exchange.com/questions/27585165/Microsoft-Word-Macro-to-Find-Copy-and-Paste-Text-with-Specific-Style-to-a-new-Document.html) that I am trying to enhance.

I want it to extract all text that has a user-defined style to a new document but also to include the numbering if the style is a numbering style. I added these lines to achieve the latter, but they do not do the job:
Set oRange = oDoc.Range(Start:=rText.Start, End:=rText.End)
oRange.ListFormat.ConvertNumbersToText wdNumberParagraph
'Then undo so that numbers revert to the original in oDoc
 oDoc.Undo

Open in new window

The idea was to convert the numbers to text to preserve them in the output document but revert to their dynamic state in the source document (this macro would be a great tool to check to see where the list numbering is off wherever it occurs in the document).

The macro also goes into an endless loop when entering ‘Body Text’, ‘Body Text 2’, ‘List Number’, List Number 2, but not ‘Body Text 3’, ‘List Number 3’ (of those I tested). I added a counter to catch the endless loop at some point and display a message box to that effect, but I don’t understand why the macro does this on some styles and not on others.

Can someone help prevent the endless loop and get the macro to properly include the numbering when the style contains numbering?
Extract_Text_by_Style-Test--Mar-30-.docm
0
Hi experts!

I doubt you can help but it can't hurt. Whenever I create a column chart in Powerpoint the labels are positioned well but as soon as I change the chart type to 3D the labels are too low and you can't read them. I normally have to manually click on and move each label above each column, which is a nightmare when I have twenty of them. Any solutions you  can think of? Ideally I'd love to use VBA but I don't think you can use it on charts.
0
Hi, I would like to send email via lotus notes using access vba. It works well but I need help to insert a html or .jpg  signature in the mail. Could you please help me with access vba code?

Thanks
0

VBA

11K

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.