VBA

11K

Solutions

4K

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 have a report in excel where the total time is written like so:

100d2h54min29s

I was wondering / hoping someone had a formala to confert that data into a straight number of seconds.

IE:

8650469

Thank you,
0
Microsoft Azure 2017
LVL 13
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Any idea how to calculate time difference in vba ?

Suppose the login time is 9:20:00 am and I want to know the difference from 9:00:00am .

Thx
0
Try to set a OFFSET function but it returns an error. Any idea ?

Thx
Excel VBA
0
How would I write an If statement on the following filter result?  What if this returns nothing.  Usually I filter and copy, but if there is noting to copy?
I suppose I could grab the header too, and after c/p check from the header cell If ActiveCell.offset(1,0) = "" Then  Any other ideas?  

 ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:="=*DN_*", _
        Operator:=xlAnd

Open in new window

0
The variables lRow and lCol will only show the correct information on the first loop, the following loops is a report on the first loop. A similar thing happens if I use LastRow variable.


Public Function ExportToExcel(ReportFilterType)
' Tools > References > Microsoft Excel Object Library

On Error GoTo Error_Message
'Requires Reference to Microsoft Excel Object Library
 Dim rsMainList As DAO.Recordset
 Dim rsExport01 As DAO.Recordset
 Dim rsExport02 As DAO.Recordset
 Dim rsExport03 As DAO.Recordset
 Dim sqlExport01 As String, iCol01
 Dim sqlExport02 As String, iCol02
 Dim sqlExport03 As String, iCol03
 Dim xlObj As Object
 Dim xSheet01 As Object 'Upload File
 Dim xSheet02 As Object 'Batch Summary
 Dim xSheet03 As Object 'Batch Detail
 Dim strFolder As String
 Dim LastRow As Long
 
 On Error Resume Next
 DoCmd.Hourglass True
 Set rsMainList = CurrentDb.OpenRecordset("SELECT DISTINCT UPLOAD_ID, VISUAL_BATCH_ID, VISUAL_BATCH_TYPE, VISUAL_DATABASE  FROM VMTBL_NI_VM_ORCL_GL_UPLOAD_STAGING")
 If rsMainList.EOF Then Exit Function
 rsMainList.MoveFirst
 
 strFolder = CurrentProject.Path & "\"
 
 Do Until rsMainList.EOF
   
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add

    sqlExport01 = "SELECT Database, [Posting Date], [Account Combination], Co, Div, Fun, Rig, Job, AFE, Maj, Min, [I/C], [Debit Amount], " + _
    "[Credit Amount], [Total Amount],[GL Description], [Upload ID], [Batch ID], [Currency ID] " + _
    …
0
I am trying to run the code shown below in Access, but I get an “Object variable or With block not set” error. Please help me resolve the error.

Code:
Dim myApp As Object
myApp = CreateObject("InDesign.Application.CS6")

Dim myFileSystemObject As Object
myFileSystemObject = CreateObject("Scripting.FileSystemObject")

Dim myFile As String
myFile = myFileSystemObject.GetFile("C:\Temp\1.jsx")

I also tried, but I get the same error:
Dim myFile As Long

The following code runs without any errors in VB.Net:
Dim myApp As Object
        myApp = CreateObject("InDesign.Application.CS6")

       Dim myFileSystemObject = CreateObject("Scripting.FileSystemObject")

        Dim myFile = myFileSystemObject.GetFile("C:\Temp\1.jsx")
        myApp.DoScript(myFile, InDesign.idScriptLanguage.idJavascript)
        myApp = Nothing
0
I am getting the following error while trying to save my Access database as an ACCDE file. There is VBA code but nothing running

1
0
Hello experts,

The following procedure allows me to add string on multiple columns.


Sub Add_Specific_String_Multiple_Columns()
    
    Dim strSpecificChar As Variant    'New declaration in order to add numeric and non numeric values
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String
    Dim strToAdd As String
    
    On Error GoTo Error_Routine

    intWhich = 0
    intWhich_temp = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")

    If intWhich_temp = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If

    If IsNumeric(intWhich_temp) Then intWhich = intWhich_temp

    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
        & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    
    strToAdd = InputBox("Input the value that you want to add.", "String To Add!")
    
    If strToAdd = "" Then
        MsgBox "No input!", vbExclamation
        Exit Sub
    End If
    
    For Each strCol In Split(strColList, ";")
        

Open in new window

0
I have inherited a database where the Access objects are hidden and only a login form appears.  Where do I look to see how the other developer did this. I can see the tables etc by holding the shift key down
0
Dear all Experts!
I would like to thank you all for assisting me to almost finish my task as assigned by management, and many special thanks to Mr Jim for providing the VBA code below for send & receiving data from & to RS232 com gadget
The last bit though not much is another assistance required to receive the data from RS 232 which is coming in Json format, I’m fully aware he also provided a form on the scale part, but this appear to be slightly different:
Functional VBA Code:
Option Compare Database
Option Explicit

'-------------------------------------------------------------------------------
' modCOMM - Written by: David M. Hitchner
'
' This VB module is a collection of routines to perform serial port I/O without
' using the Microsoft Comm Control component.  This module uses the Windows API
' to perform the overlapped I/O operations necessary for serial communications.
'
' The routine can handle up to 4 serial ports which are identified with a
' Port ID.
'
' All routines (with the exception of CommRead and CommWrite) return an error
' code or 0 if no error occurs.  The routine CommGetError can be used to get
' the complete error message.
'-------------------------------------------------------------------------------

'-------------------------------------------------------------------------------
' Public Constants
'-------------------------------------------------------------------------------

' Output Control Lines (CommSetLine)
Public Const LINE_BREAK = 1

Open in new window

0
OWASP Proactive Controls
LVL 13
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Hello experts,
The following procedure allows me to identify values which are in based on two different ranges.
Sub Is_In()
    Dim wsSource As Worksheet
    Dim wsComaparison As Worksheet
    Dim rngSource As Range
    Dim ComparisonRange As Range
    Dim rCl As Range
    Dim LRSource As Long
    Dim LRComparison As Long
    Dim colSource As Long
    Dim colComparison As Long
    Dim cntMatch As Long
    
    Application.DisplayAlerts = False
    
    On Error Resume Next
    Set rngSource = Application.InputBox(Prompt:="Please Select any cell in your range source, in this range you will find the cells which are in your range to compare", Title:="Source Range Selection", Type:=8)
    
    Set ComparisonRange = Application.InputBox(Prompt:="Please Select any cell in the Range to compare", Title:="Select Range To Compare With", Type:=8)
    On Error GoTo 0
    
    If rngSource Is Nothing Then
        MsgBox "You didn't select any Source Range to compare.", vbExclamation
        Exit Sub
    ElseIf ComparisonRange Is Nothing Then
        MsgBox "You didn't select any Comparison Range to compare it with Source Range.", vbExclamation
        Exit Sub
    End If
    
    Set wsSource = rngSource.Parent
    Set wsComaparison = ComparisonRange.Parent
    
    colSource = rngSource.Column
    colComparison = ComparisonRange.Column
    
    LRSource = wsSource.Cells(Rows.Count, colSource).End(xlUp).Row
    LRComparison = wsComaparison.Cells(Rows.Count, 

Open in new window

0
I have a small table (another learning exercise for me),  In column A, I have Order Description.  In column B, I have Costs.
 I want to capture the order description using Xl Down, because the order may have 1 or 50 entries.. and that works fine.. Once captured, I am having a little trouble with moving it to the OrderDataBase page, (page 2).
  I want to use on Page 2, column A, XlUp to get to the next free row, and I can, but I don't know how to paste the information from the clipboard, nor do I know if that is the best method.
 
I know this is very simple for you guys but after hours of searching, I am lost..

Thanks,
Norm
0
Excel Search Cells by Format [Fill Color] follows no discernible directional pattern: across column, across column, down columns, down columns, across columns.

Attached is an example sheet.  If I run the Format Fill search for yellow fill, I get this series of results:
Z5, AH11, X25, X26, AX26, AX28, K34, O37, X60, AZ61, V62, BO36

I need the search to go down each column so that the cells would appear in this order:
K34, O37, V62, X25, X26, X60, Z5, AH11, AX26, AX28, AZ61, BO36

Have group of extracted quotes arranged thematically in columns.  Have highlighted some that I that I want to quickly review.  Need to be able to focus to each highlighted cell down the columns and then move over to the next column with a highlighted cell at the lowest number / nearest-to-the-top cell.  Continue down.  Move to the top of the next column with a highlighted cell.  

So, left to right, down, over and up columns, according to cells with the fill color in question.

Any way to do that?

Many thanks,

OT
search-direection-test-by-formattin.xlsm
0
Hi. I am not sure why the following error is happening

1
0
I am using VBA code to create a query for a report. I have a field that has both numeric and alphanumeric data. I need to search for a range of values on this field. When I populate a query using query design view, it looks like

QryDesign.JPG
The query works as expected.

When I attempt to create the same query with VBA, I get the following error:

SyntaxError.JPG
My 'where' clause end up like   ([ProOrdNumber] = Between "7927"  AND  "7930") and somehow I am missing an operator.

Can anyone help me?
0
Hello experts,

The following procedure allows me to extract string in between parenthesis.

Sub Extract_String_Between_Parenthesis()

    Dim strColInput As String
    Dim strColOutput As String
    Dim lngLastRow As Long
    Dim strTemp As String
    Dim lngRow As Long
    Dim strIO
    
    On Error GoTo Error_Routine
    
    strIO = InputBox("Please enter the column letters separated by a colon for the data in the form 'InputColum:OutputColumn'", "Choose Column Letters", "A:B")
    strColInput = Split(strIO, ":")(0)
    strColOutput = Split(strIO, ":")(1)
    lngLastRow = Range(strColInput & "1048576").End(xlUp).Row
    
    For lngRow = 1 To lngLastRow
        If InStr(1, Cells(lngRow, strColInput), "(") > 0 And InStr(1, Cells(lngRow, strColInput), ")") > 0 Then
            strTemp = Split(Cells(lngRow, strColInput), "(")(1)
            Cells(lngRow, strColOutput).Value = Replace(strTemp, ")", "")
        End If
    Next
    
    Exit Sub
Error_Routine:
    MsgBox Err.Description, vbExclamation, "Something went wrong!"
    
End Sub

Open in new window


I would like to add the following improvement:
Instead of exporting just string between parenthesis I would like to export string in between other characters.

The idea is to put two initial inputbox.
1-Inputbox "Enter the first character involved by the extract process" Example: "("
2-Inputbox "Enter the last character involved by the extract process" Example: ")"

If I can put just one inputbox like this:
1-Inputbox "Enter the first character and last character separated by ";" involved by the extract process" Example: "(;)"
It would be better.

If you have questions, please contact me.

Thank you for your help.
0
I generate an Excel worksheet with a specific name. However, the user may regenerate the same excel several times which causes a problem at the time of saving the file (if another excel with the same name is open). Is there a way to close any other workbooks with the same name except the currently active workbook?

I currently use the following code or workaround:

On Error Resume Next 'ErrorSave
objExcel.ActiveWorkbook.SaveAs lFilename
lCtr = 1
Do While Err.Number = 1004 And lCtr < 10
  MsgBox "There is other Excel open with the same name: " & lFilename & " Pls CLOSE the old version first..."
  objExcel.ActiveWorkbook.SaveAs lFilename
  lCtr = lCtr + 1
Loop
objExcel.ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Trim(lFilename) & cPDFType
On Error GoTo 0

However, if the user closes all workbooks then the save is successful but it still returns Err.number = 1004? Not sure why... Thank you so much for any help. Rg Michael
0
I need to insert a collection called Tax Class within Json format/Ms access VBA code which is now almost complete see current results below:

Current results
{
   "PosSerialNumber": "102010",
   "IssueTime": "2019-09-15",
   "Customer": 1,
   "TransactionTyp": 0,
   "PaymentMode": 0,
   "SaleType": 0,
   "Items": [
      {
         "ItemID": 1,
         "Description": "Apple (Rgb 350 ML)",
         "BarCode": "6009803227328",
         "Quantity": 15,
         "UnitPrice": 41,
         "Discount": 0,
         "Taxable": [
            {
               "Total": 615,
               "IsTaxInclusive": "True",
               "RRP": 52.8
            }
         ]
      },

Open in new window


Expected results

{
   "PosSerialNumber": "102010",
   "IssueTime": "2019-09-15",
   "Customer": 1,
   "TransactionTyp": 0,
   "PaymentMode": 0,
   "SaleType": 0,
   "Items": [
      {
         "ItemID": 1,
         "Description": "Apple (Rgb 350 ML)",
         "BarCode": "6009803227328",
         "Quantity": 15,
         "UnitPrice": 41,
         "Discount": 0,
         "Taxable": [
              "B"
               ]
            {
               "Total": 615,
               "IsTaxInclusive": "True",
               "RRP": 52.8
            }
         ]
      },

Open in new window

Amendment required on VBA

How do I include the new collection ("tax category") in the below VBA to hold ["B"] just below "taxable"

Private Sub CmdSales_Click()
  
'  Const SQL_SELECT As String = 

Open in new window

0
I have this:
ModelRangeFound: InStr([mODEL RANGE],[forms]![Form1].[word].[value])>0

It fails as it picks up the search word anywhere.

I was thinking of using regex to do the search term but how do I tell it to use the found word if its the first or second word only?

"RG125 bike year a6"  fails as it finds as a6 at end
"A6 quattro s line" This is valid

How do I get regex to find the search string but only return true if the search word is at the beginning of the sentence?

I dont know how to make that regex pattern
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. What Excel VBA code would I use to test if a value of 87 is present in a column named Col1 in a table named Table1 in an Access database in the same folder as the spreadsheet
0
Hi

What Excel VBA code would I use to insert records into an Access database in the same folder?

Thanks
0
Hi Experts,

I'm trying to make a macro to fill an Excel file by opening an Excel containing data.
It should fill the column based on the month of the year and it should count if :
- the column "Date de réf." is in the current year
- the column "Désignation" contains the description string in the Calendar file.
- the column "Statut util." has ok

Is there a solution to this?
0
excel vbA  OFFICE 365

What I need:
I need to reformat some raw data on a sheet and create a new sheet with a formatted look based on a Column(a).
Basically grouping Column(a) into different sections
I have attached 2 screen shots-before and after look.

BEFORE FORMATTING
before formatting

and
AFTER FORMATTING
AFTER
0
I want to filter a datasheet based on the type of user logged in.

For Example

Private Sub Form_Load()
If Forms!Login!Role = "Southern Auditor" then
Me.Filter = "(CUSTOMER = 'University of Derby')or (CUSTOMER = 'Coventry University')or(CUSTOMER ='University of Leicester')"
else
Me.Filter = "(CUSTOMER <> 'University of Derby')or (CUSTOMER <> 'Coventry University')or(CUSTOMER <>'University of Leicester')"
end if

If the above doesn't make sense I want the Southern Auditor  to only see the 3 Customers else anybody else logged in see all other customers (excluding the 3 above).

Thank you
0
Hi
In my Access database am using vba to import a spreadsheet containing data for a table where one column contains text and the other contains integers that are foreign key values for another table called Contacts. I want a user that prepares the spreadsheet to use the contact email and then have the import pull in the integer key value for that email address rather than the address. How do I achieve this? Do I use a technique in the spreadsheet or rather do something on the Access side
0

VBA

11K

Solutions

4K

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.