VBA

12K

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 am having problems inputting data from .csv file into an Access 2010 temporary table.  The data is output from a web application called Clicktools. Please refer to the enclosed database file:Testinput.accdb and the function doimport() in Module1.

The enclosed file Testfile1.csv contains a single comma delimited record (Andrew Everyman), where the last field is a mixture of alpha and numeric characters. The field in the Access table tmpDDPaymentsData is defined as Text(255).

If you run the doimport() function  all required fields will be imported correctly. However if you change the file name in the doimport() code to import Testfile2 (which contains the same record mixed in with other records where the last fields are all numeric characters) the import routine will not load the last field of the Andrew Everyman record. All other records load correctly.

Thanks for your help

TestInput.accdbTestFile1.csvTestFile2.csv
0
Exploring ASP.NET Core: Fundamentals
LVL 19
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.

In excel vba I need to have my set range change negatives to positive numbers and negatives to positive.
I can not seem to get it to fire.

Here what I am have been trying:

Private Sub Worksheet_Change(ByVal target As Range)
   
    'On Error Resume Next
         
    If Not Intersect(target, Range("H3:H13")) Is Nothing Then
        Set rng = Intersect(target, Range("H3:H13"))
        For Each r In rng

        Me.Unprotect
       
        Application.EnableEvents = False
       
        target.Value = target.Value / 100
       
     Next r
       
    End If
       
           
    If Not Intersect(target, Range("R3:R13")) Is Nothing Then
      Set rng = Intersect(target, Range("R3:R13"))
        For Each cell In rng
      'Me.Unprotect
     
      'For Each c In target.Cells
          cell.Value = -cell.Value
          cell.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
      Next cell
           
      End If
     
           
      Application.EnableEvents = True
     
    'Me.Protect
   
End Sub
0
Hi

I have an Access form with a combobox that has a button next to it where the user can add
more information to a table that is the data source of that combobox.When the user closes the form
that was opened with the button I want the combobox to automatically be refreshed with the additional data
Do I requery the combobox on the other form? or do I use some way to requery the combobox when the user goes back to it with their mouse?
0
I want to change a text in a function.
I have $A$2 Want to change to $A2.

Thank you,
0
Hi,
I would like to put custom tool bar in my Ms-Access application.  Not the predefined tool bar, but one that I can customize for an accounting application I am doing:
Something like this:

SALES                         PURCHASES               RECEIPTS                     PAYMENTS
Cash Sales                 Local Purch.               Cash Rec.                    Cash Payments
Credit Sales               Foreign Purch.           Linx Rec.                     Cheque Payments
Other Sales


The titles will be the headings... and the items below will be drop-down list that will appear when click on the heading.
Thank you.
0
Is there any way to determine what the current error handling is in vb6?

Default error handling crashes the program displaying the error message.

If you use:
ON ERROR GOTO Label

Open in new window

The program goes to the point in the code with the label and continues executing (presumably, you have defined what to do here to mitigate the error)

If you use:
ON ERROR RESUME NEXT

Open in new window

The program will continue ignoring errors until it either can't run at all, you handle the errors, or you disable the resume next option.  I often (but not always) use ON ERROR RESUME NEXT to allow the program to continue and then I explicitly check for an error number and handle the error there.  For example:
ON ERROR RESUME NEXT
X = 5
IF X = "bob" THEN X = "Bob"
IF ERR.NUMBER <> 0 THEN
   'Handle the error
END IF 

Open in new window


And to disable graceful error handling, you use
ON ERROR GOTO 0

Open in new window


But is there any way to tell if graceful error handling is on?  Any way to tell that the last time an ON ERROR statement executed it was set to go to a label, resume next, or goto 0?
0
Good Morning,

I want to be able to attach all PDF's that are located in a folder to an email.

I was going to use something like this

.AddAttachment "D:\folder\*.pdf"

Any ideas/
0
trying to post data from a recordset to sheet.

I have the folloeing recorst created in code:

But trying to loop through the data and place on the worksheet properly


dB2.CommandTimeout = 400
                With rs2
                 .ActiveConnection = dB2
                 .Open strsql
                End With



 
J = rs2.RecordCount

ActiveSheet.Range("A2").Select
      
For i = 1 To J  'rng.Count
    

Do Until rs2.EOF

      
        
        ' place data into sheet
        rng(i).Offset(1, 0) = rs2.Fields(0) '
        rng(i).Offset(1, 1) = rs2.Fields(1) '
        rng(i).Offset(1, 2) = rs2.Fields(2) '
        rng(i).Offset(1, 3) = rs2.Fields(3) '
        rng(i).Offset(1, 4) = rs2.Fields(4) '
        rng(i).Offset(1, 5) = rs2.Fields(5) '
        rng(i).Offset(1, 6) = rs2.Fields(6) '
        rng(i).Offset(1, 7) = rs2.Fields(7) '
        rng(i).Offset(1, 8) = rs2.Fields(8) '
        rng(i).Offset(1, 9) = rs2.Fields(9) '
        rng(i).Offset(1, 10) = rs2.Fields(10) '
        rng(i).Offset(1, 11) = rs2.Fields(11) '
        rng(i).Offset(1, 12) = rs2.Fields(12) '
        rng(i).Offset(1, 13) = rs2.Fields(13) '
        rng(i).Offset(1, 14) = rs2.Fields(14) '
        rng(i).Offset(1, 15) = rs2.Fields(15) '
        rng(i).Offset(1, 16) = rs2.Fields(16) '
     
 

        ActiveCell.Offset(1, 0).Select ' move down to the next row 
       
      rs2.MoveNext
    Loop
 Next i

Open in new window



Thanks
fordraiders
0
proper passing of variables to sql server procedure in excel vba office 365 (64 bit)

Is this the correct way to pass variables to sql server stored procedure ?

Dim strStart As String
Dim strEnd As String


Dim myValue As Variant
Dim myValue2 As Variant
myValue = InputBox("Supply a Start Date Please. Format YYYY-MM-DD")

myValue2 = InputBox("Supply a End Date Please. Format YYYY-MM-DD")
 

Dim sQuery As String
    Dim i As Long
    Dim SearchCodes As String
    Dim dB2 As New ADODB.Connection
    Dim rs2 As New ADODB.Recordset

Dim dbconnstring As String
Dim strsql As String


Set dB2 = New ADODB.Connection
dB2.CursorLocation = adUseClient




dbconnstring = "provider=sqloledb;Server=B025.us.xxxxx.com;Database=PROGRAM_WORK;Uid=xxxx;Pwd=zzzzz;"

' IS THIS LINE CORRECT ? BELOW
strsql = "EXECUTE [program_work].dbo.[sp_Clm] ] N'" & MyValue & "' & N'" & MyValue2 & "' ;"



dB2.CommandTimeout = 400
                With rs2
                 .ActiveConnection = dB2
                 .Open strsql
                End With


-----  more code

Open in new window

0
Hello, I need a macro to transposed dates in five columns into one column.

Columns A-H is the raw data
Column J-M are the desired results
If possible, I need the results to only display dates because every record doesn't have dates across all five columns so sum will be blank
The key fields are A,B,C and they need to be displayed on the results, in Order of Pers.no. then ascending in date order
Transpose-Dates-in-many-columns-int.xlsx
0
CompTIA Network+
LVL 19
CompTIA Network+

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

I am modifying a procedure for a custom Query By Form. The original procedure searches multiple fields in a table, but I am modifying it to search only single field in a table

The modified code is in the click event of the Search - Test 1 button on frmMainMenu. You can view the original unmodified procedure in the click event of the Search - Test 1 (All Orig. Code).

Here are the text box names:

txtDateFrom -- Date From
txtDateTo -- Date To

Here is the modified code:
' First, validate the dates
    ' If there's something in Date From
    If Not IsNothing(Me.txtDateFrom) Then
        ' First, make sure it's a valid date
        If Not IsDate(Format(Me.txtDateFrom, "mm/dd/yyyy")) Then
            ' Nope, warn them and bail
            MsgBox "The value in Contact From is not a valid date.", vbCritical, gstrAppTitle
            Exit Sub
        End If
        ' Now see if they specified a "to" date
        If Not IsNothing(Me.txtDateTo) Then
            ' First, make sure it's a valid date
            If Not IsDate(Format(Me.txtDateTo, "mm/dd/yyyy")) Then
                ' Nope, warn them and bail
                MsgBox "The value in Contact To is not a valid date.", vbCritical, gstrAppTitle
                Exit Sub
            End If
            ' Got two dates, now make sure "to" is >= "from"
            If Format(Me.txtDateTo, "mm/dd/yyyy") < _
                Format(Me.txtDateFrom, "mm/dd/yyyy") Then
                MsgBox "Contact To date must be greater 

Open in new window

0
Need help with overcoming 3035- system resources exceeded error carrying out an INSERT sql statement.

Please note image attachment that shows error message - (along with value of what sEntireLineItem is) and following code:
The yellow highlight should be on Currentdb.Execute sSQL

      sSQL = "INSERT INTO [usgaapfileswtags] ([tagID], [usgaapfilesid], [usgaaprow], [usgaaplineno], [usgaapvalid]) VALUES (" & lTagID & ", " & lIDFileName & ", '" & sEntireLineItem & "', " & lCounterTag & ", " & bValidTag & ")"
      CurrentDb.Execute sSQL
      DoEvents

Open in new window


So obviously the sql INSERT statement will be pretty long

I already do this earlier in the code:

If Len(sEntireLineItem) > 65500 Then
        sEntireLineItem = Left(sEntireLineItem, 65500)
        
      End If

Open in new window


And I do have usgaaprow as Long Text as a data type.  I did check all the other values are quite small and match without a problem.  I know it's the variable: sEntireLineItem

Here is the value of sEntireLineItem string itself:
  <us-gaap:BusinessCombinationDisclosureTextBlock contextRef=''FD2018Q4YTD'' id=''Fact-82E23980F0AD591486D298F5A3EA3736''>&lt;div style=''font-family:Times New Roman;font-size:10pt;''&gt;&lt;div style=''line-height:120%;padding-top:18px;font-size:10pt;''&gt;&lt;font style=''font-family:inherit;font-size:10pt;font-weight:bold;''&gt;ACQUISITIONS, GOODWILL, AND ACQUIRED INTANGIBLE ASSETS&lt;/font&gt;&lt;/div&gt;&lt;div 

Open in new window

0
Values to be identified as duplicates will be in the second (B) column (with header). I will need to identify rows with duplicate values in that column, but preserve all values in the other columns of that row.

It would be good to insert the string, "Duplicate" (without quotes) in the column to the far left of the duplicates other than the 1st instance. For the 1st instance, mark as "First Instance", would be desirable. Then I can sort on that column. Non-duplicates should not be marked at all.

Spreadsheet has 75,000 rows.

Using an Inline Function would probably be preferred if possible, rather than a nested VBA loop type macro. But please do what you think best. I'm unable to find a reasonable solution searching the web. -- BTW:  CONDITIONAL FORMATTING hangs the program with too many ROWS.

Sample Content: (sorry, but the "Indent" tags are not working). The point is that the values in other columns of those rows do not affect the logic.
Column A is Blank

Column-B-Title   Column-C-Title   Other-Columns-w-Title
5555
Some-Value
5555
Some-Other
5555
Not Important
3333
Some
3333
Thing
XXXXXX
xyz
XXXXXX
abc
Anything
pqr
Else
stu

(Note: Sorry about all the edits. Couldn't find a "Preview" button prior to publishing. Not familiar with this new interface.)
0
I have a weird problem that just creeped up.

When i click on any button on my form.
It seems to take focus to another textbox(tabindex 0 ) ...and always require a second click to execute any code behind  the button ?

any suggestions ?

fordraiders
0
Hello,

I have code that works, but it only works if the user enters data into the sub-form. If the user enters data into the form and not the sub-form, the sub-procedure does not run and it exits the form saving the form's data (upon the user clicking Save and close cmd button) without running the sub-procedure which I have all my data validation checks.

Is there a way I can force the form to run the sub-procedure without having the user enter data into the sub-form?

Thanks


The sub-procedure is an "On click" [Event Procedure] with a command button  "Save and Close"


Private Sub cmdSave_Click()

' error checking to make sure all fields are completed
' this was part of RLL's original code
Dim ErrMsg As String
Dim ErrVal As Integer

'By adding a series of Isnull statements, if the sum is not zero then there is a null field
'part of RLL's original code
' we will use this after we check the start and stop dates
ErrVal = IsNull(dvsAIPGroup_Name) + IsNull(dvsRegion_Id) + IsNull(dvsInstrumentType_Id) + IsNull(dvsAIPGroup_StartDate) + IsNull(dvsAIPGroup_StopDate) + IsNull(dvsAIPGroup_CreationDate)


''check to see if we have any dates that are in the wrong order.
Dim sql As String
Dim rs As DAO.Recordset
Dim i As Integer
i = 0
On Error GoTo ErrorHandler

' This sets the query def here. Pulls the data into our passthrough query
CurrentDb.QueryDefs("ptqry_CheckDates").sql = "SELECT dvsAIPGroupTarget_ID, dvsAIPGroup_Id" & _
" ,dvsAIPGroupTarget_StartDate, 

Open in new window

0
I need a VBA code to write formula in a cell which multiplies two VBA variables.

Dim Var1 as integer, Var2 as integer.
Var1 = 1
Var 2 = 2
 and something like:

ActiveSheet.Cells(1,1).Formula = "=" & Var1 & "*" & Var2

and another formula where I multiple cell value by variable, i.e.

ActiveSheet.Cells(1,1).FormulaR1C1 = "=RC[1]" & "*" & Var1 & "*" & Var2
0
trying to update a record in sql server
getting error message when a field is blank  ( = null)


These are unbound  textboxes on a form.

R.Edit
' 13 FIELDS SKU INFORMATION
      R![SKU] = Nz(Me("SKU_FRM"), "") ' SKU
      R![QTY] = Nz(Me("QTY_FRM"), "") ' QTY
      R![TARGET_PRICE] = Nz(Me("TARGET_PRICE_frm"), "") ' TARGET_PRICE
      R![TARGET_GP] = Nz(Me("TARGET_GP_FRM"), "") ' TARGET_GP
      R![CURRENT_PRICE] = Nz(Me("CURRENT_PRICE_FRM"), "") ' CURRENT PRICE
      R![CURRENT_GP] = Nz(Me("CURRENT_GP_FRM"), "") 'CURRENT_GP
      R![VENDOR_GUIDELINE_GP] = Nz(Me("VENDOR_GUIDELINE_GP_FRM"), "") ' FLOOR_GP
      R![PRODUCT_DESCRIPTION] = Nz(Me("PRODUCT_DESCRIPTION_FRM"), "") 'PRODUCT_DESCRIPTION
      R![TARIFF_ITEM] = Nz(Me("TARIFF_ITEM_FRM"), "") '  TARIFF ITEM
      R![RENEWAL_SKU] = Nz(Me("RENEWAL_SKU_FRM"), "")  '  RENEWAL SKU
      R![RENEWAL_OLVP_QUOTENUMBER] = Nz(Me("RENEWAL_OLVP_QUOTENUMBER_FRM"), "") '  RENEWAL OLVP ITEM
      R![APPROVED_PRICE] = Nz(Me("APPROVED_PRICE_FRM"), "") ' APPROVED PRICE                                                                             error
      R![APPROVED_GP] = Nz(Me("APPROVED_GP_FRM"), "") ' APROVED GP                                                                                               error

R.Update

Open in new window


i thought  NZ would take care of a blank value ?


These 2 fields may not have data in the textbox,
but i still need the code to finish the .update

Thanks
fordraiders
0
As you can see from my screen shot, I’m using the following sub routine to link various worksheets from ExcelFile.xlsx into my database. As shown below, I’ve linked only four worksheets. However, my actually worksheet has many more tabs that I need to link. Also, the tab names are not constant. In other words, I could have three worksheets named like: 1111, 2222, 3333. Then at a later time for the exact same file, I could have four worksheets with multiple other names like: AAAA, BBBB, CCCC, DDDD.

Right now, it’s a manual process for me to update this code each time a sheet name changes or is removed. I’d like to modify this code to be more dynamic by accomplishing the following:

1.      First delete all database table objects that have a string of 4 characters in their name (e.g. 1111, 2233, AABC)
2.      Link all worksheet tabs that have a string of 4 characters in their name from ExelFile.xlsx

I'm using MS office 2016. Any Expert suggestion on this is greatly appreciated! =)

Private Sub LinkSpreadSheets()

Dim Fpath As String, _
  XLname1 As String, _
  tb1 As String, _
  tb2 As String, _
  tb3 As String, _
  tb4 As String

Fpath = Environ("USERPROFILE") & "\Documents\databases"

XLname1 = "\ExcelFile.xlsx"
                   
tb1 = "1001"
tb2 = "2001"
tb3 = "3001"
tb4 = "F008"

With DoCmd

    .DeleteObject acTable, "1001"
    .DeleteObject acTable, "2001"
    .DeleteObject acTable, "3001"
    .DeleteObject acTable, "F008"

    .TransferSpreadsheet acLink, , tb1,

Open in new window

0
echo %username% and environ("%username%") are not working.  

This morning echo %username% and environ("%username%") stopped working.  
It affected all vba programs and batch files in my windows 10 (1903 build 18362.418) laptop.

The following screen shots show the problem.
ss1ss2
I am working from home using windows 10 "always on vpn" to connect to a Microsoft Server Essentials 2016 domain.  
I suppose it is time for yet another reboot, but I have a lot of stuff open.  Does anybody have an idea of what I might try?


Background:

Saturday evening everything was fine and I went to bed without closing several Explorer folders.
While I slept, the connection got lost so on Sunday morning the Explorer windows showed "the directory name is invalid".

I reconnected, and continued to work with no obvious problems.
But, about a half hour later one of my batch files failed because %username% was not working.  

My documentation of the problem is shown in the above screen shots.
0
Bootstrap 4: Exploring New Features
LVL 19
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Hi all, i have 2 unbound text boxes costA and CostB and 1 bound text box rate in continuous form. now i am writing vba code to get rate value depending on costa and costb values.
if costa and costb values are null then rate = 0
if costa has value then rate = costa or
if costb has value then rate = costb
please note that in continuous form we will be having either costa or costb value. we don't have both values same time. one value will be always null
whatever code i am trying to modify it is either working for costa or costb but not for both.
  If (IsNull(CostB) = True And IsNull(CostA) = True) Then
   
        Rate.Value = 0
     ElseIf (IsNull(CostB) = False And IsNull(CostA) = True) Then
     
        Rate.Value = CostB.Value
        Else
        Rate.Value = CostA.Value
     
        End If

can someone tell this is happening because of continuous form?
0
I am getting the error "Too few parameters 2 expected" when trying to run the following SQL

Update tblFirearms Set SerialNo = 'SN4333', ManufacturerFK = '10', Model = 'M77', Calibre1FK = '6', Calibre2FK = '', BarrelSerialNo = '', ActionTypeFk = '2188', SourceFK =3, StatusFK =5, SourceComment = 'Registered: 9/11/2019 3:18:27 PMarm in bad condition', ProcessingOfficer = 'DM', Image = 'C:\Users\murbr\Documents\Office Experts\CAVR\Media\Arms\Gun6.jpg,C:\Users\murbr\Documents\Office Experts\CAVR\Media\Arms\Gun10.jpg', Action = 'Registered', Notes ='arm in bad condition' Where FirearmPK = 74
0
Hi

I have a SQL statement that I want to load data to a grid with in Access. What is the best way to do this
My statement is    strSQL = "SELECT * FROM " & T & " WHERE FirearmFK = " & FirearmPK
0
I am doing a schedule.
  Time -  Duration  - Name
Another Learning exercise for me..
I have 3 Combo Boxes with Start Time, End Time, Duration.
By Selecting the days start time I will fill the Time Column Starting with selected TIME to End Time.
  For Duration, My Combo Box, is setup with 15 minute increments.  15, 30, 45, 60, (which I will extend to additional time later).
1. What I would like to try See Attach;
  Start time =  L3, Then autofill the time down Column L to Selected End Time/ but each Cell is = 15 minutes. So an 1 hour appointment cells M3 to M4 would be Filled.
And the conditional formatting of the Duration cells is or was the question.  How would I use Conditional formatting or VBA to fill the corresponding cells under Duration, when each appointment is selected:
ScheduleLearn.xlsx
0
Experts,

I have an excel spreadsheet that will have data align horizontally.  I would like to have this automated and by clicking a button the data will convert to a vertical format, maintain all formulas, formatting and copied to a new tab sheet.  the input of data needs to be done in a horizontal format, but creating pivot tables need to be done with the data in a vertical format.

I know how to convert using the transpose in excel but that will take a lot manual effort and it doesn't always transpose correctly.

I am attaching a file of the horizontal format, but data and formulas have not been added.
0
Need help with reading line by line a large .nc file converted .txt using Microsoft Access DAO Recordset.

I'm providing a sample database as to my attempts to read a linked txt file that I basically renamed from a .nc data file.
If you take a look - there is a module:  modEExchange with a sub:  PullNonValidTagLineItemsOnly that I'm trying to get working correctly.  
To test you open the immediate window and enter:  Call PullNonValidTagLineItemsOnly("0001130310-19-000016", 7)  per eeexample2.png.

Basically I've identified some tags within this file that don't seem to find the ending </us-gaap and now I'm taking that and trying to go to that exact line no within that text file and put in a separate table until I come across either that ending </us-gaap or  - the next 10 line items within that text file at least

I understand doing this can be tricky cause text files have limitations when you are looking at this line by line and the string limitations as to width.  I made sure though the data file is long text for the Field1 and have linked the text file with the advanced link specifications in image:  eeexample1.png.

Per eexample2.png - the first tag to come up is CommitmentsAndContingencies on line number:  19175 within the text file..based on the sSQL looping through the DAO.REcordset ...  once I grab the first tag I loop through the linked table:  0001130310-19-000016 and am now trying to go to that exact line no - to start pulling that tag - into table:  …
0

VBA

12K

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.