Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

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
On my app, I have a subform that has a data source linked to a query. However, whenever I start the master form, it first loads the subform and, if there are no records in the query, it actually creates a record (!).
What I want is a readonly query that does not generate an (almost) empty record - its master key, linked to the main form is correct.

I have tried opening the query as "snapshot":
Set q02_exameSet = LifeLingerDB.OpenRecordset("tblExame", dbOpenSnapshot)
but it still creates a new record!
can anyone help?
0
Free Tool: Port Scanner
LVL 11
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Experts - I have an excel workbook with 3 sheets.

"Calendar"
"Resources"
"Final"

"Calendar" - This sheet will have current month dates ("Date, Day, Month, Week, Qtr etc") Ex: Current month will have 1st Jan to 31st Jan data. Similarly every new month, data here gets refreshed.
"Resources" - This will have EmpID & Name - Constant data

What I need is...,
in "Final" sheet, For Each date in the calendar, list all resources details
Ex:
Calendar has
1/1/18
1/2/18
....
1/31/18

Resources has
123 ABC
122 CCC
111 BBB

Final Should have
1/1/18 123 ABC
1/1/18 122 CCC
1/1/18 111 BBB
....
and so on till 31st Jan.

Kindly help me with a macro
0
What VBA code would I use to determine the new range if a user copied/pasted the range to a new location. (See Examples)

Example1:

Starting Range: C20:F30
User Moves Range to: H4
Solution: In this case, if the user cut C20:F30 and pasted it into cell H4 the new range would be: H4:K14.

Example2:

Starting Range: AB42:AD60
User Moves Range to:DD50
Solution in this case, if the user cut AB42:AD60 and pasted it to cell DD50 the new range would be: DD50:DF68

Example3:

Starting Range: AB42:AD60
User Moves Range to:A7
Solution in this case, if the user cut AB42:AD60 and pasted it to cell A7 the new range would be: A7:C25
1
looking for V_OUT sheet

for example im looking for 467

appear 7 times in each column
65434677665v1--1-___121.xlsm
0
Hi experts,
How can I  differentiate oracle error between connection error and other errers in vba.
I tried the code bellow. But not only when internet cable is disconnected but also when sql syntax error happened , the code show ORACLE CONNECTING ERROR.

Public Function TEST() As Boolean
    Dim strSql As String
    Dim oraDS As OraDynaset
    On Error GoTo ERR
    strSql = ""
    strSql = " select COLUMN from TALE "
    Set OraRec = New ADODB.Recordset
    OraRec.Open strSql, OraCon, adOpenKeyset, adLockReadOnly
       
    Exit Function

ERR:
    If OraCon.State <> adStateOpen Then
        MsgBox "ORACLE CONNECTING ERROR"
    Else
        MsgBox "ORACLE SQL SYNTAX ERROR"
    End If
End Function
0
Hello-   I am using a IN SELECT statement  to add a date to my query in MSACCESS,  In (select AD from Admit_Date). Where AD is a field from the local table Admit_Date.

I want to call a date range from two local tables  the two statements I have is  In (select AD from Admit_Date)   and

I tried something like these, but it does not  seem to work:

Between (In (select AD from Admit_Date) and (In (DC from Discharge_Date))
or
Between In (select AD from Admit_Date) and In (DC from Discharge_Date)
 

Is there a better way?
0
Have this solution

then


need get like this

090909090990909090909090909090909909.PNG5653247890344.xlsm
0
Each month, we run a report to perform a distribution of fees for various fire districts in the county. This data is dropped into this excel program on the "Data" worksheet.  From there, a staff member has been sorting the data in Column E (Fire District Description) and then cutting and pasting the rows of data for that fire district to the matching worksheet.  For example, rows 2A through J8 would be copied to the worksheet named FD1 and pasted in cell A8 (we leave row 7 blank between the headers).  We only want the data from column A thru J to copy over to each worksheet as there are formulas in columns L thru R that will calculate once the data arrives.  I am sure we could automate the formulas to generate as well only when data is present, but I am new to macros and would be satisfied if we could simply get the data to go to the appropriate worksheets by using a button to click once the data is dumped on the "Data" worksheet.

I've allowed for data up to row 4,000 as I don't believe we will ever exceed that number of rows.  
I have attached a sample file.

Pay no attention to the other three worksheets (REPORT-ALL FDs, JE and RECAP). I left them in place in the event that adding in other worksheets would alter the macro so I wanted you to see the exact order of the worksheets.

Each month, we would like to automate this process so that once we dump the data on the "DATA" worksheet, we can use a button to click and have the data copy over automatically.  We would …
0
I'm using the code below and it's in a never ending loop. Thoughts

Public Function Import_System_Access_Reports()

Dim strFolder As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strFile As String
Dim strTable As String
Dim strExtension As String
Dim lngFileType As Long
Dim strSQL As String
Dim strFullFileName As String
Dim varPieces As Variant

 With Application.FileDialog(4) ' msoFileDialogFolderPicker
     If .Show Then
         strFolder = .SelectedItems(1)
     Else
         MsgBox "No folder specified!", vbCritical
         Exit Function
     End If
 End With
 If Right(strFolder, 1) <> "\" Then
     strFolder = strFolder & "\"
 End If
 strFile = Dir(strFolder & "*.xls*")
 Do While strFile <> ""

     lngPos = InStrRev(strFile, ".")
    strTable = "RawData" '<- this could be a constant instead of a variable
Set db = CurrentDb()
' make the UPDATE a parameter query ...
strSQL = "UPDATE [" & strTable & "] SET FileName=[pFileName]" & vbCrLf & _
    "WHERE FileName Is Null OR FileName='';"
Set qdf = db.CreateQueryDef(vbNullString, strSQL)

strFile = Dir(strFolder & "*.xls*")
Do While Len(strFile) > 0
    varPieces = Split(strFile, ".")
    strExtension = varPieces(UBound(varPieces))
    Select Case strExtension
    Case "xls"
        lngFileType = acSpreadsheetTypeExcel9
    Case "xlsx", "xlsm"
        lngFileType = acSpreadsheetTypeExcel12Xml
    Case "xlsb"
        lngFileType = acSpreadsheetTypeExcel12
    End Select
    strFullFileName = strFolder & 

Open in new window

0
Hi I am getting the above error on a VNA statement, when trying to set the SQL definition of a query on ms/access.
Can anyone help?

Screen-Shot-2018-01-18-at-15.42.02.png
Screen-Shot-2018-01-18-at-15.42.11.png
0
Free Tool: Subnet Calculator
LVL 11
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I had this question after viewing how to convert m/d/yyyy to d/m/yyyy.

please see attached example dummy file, i need a macro to to clean up the messed data input.

there are two worksheets in the attached workbook.  Sheet called "messedup" is what i have right now.
the sheet "Cleaned" is the desired solution that after i run the macro it should give me that.

any help is appreciated.
EE2.xlsx
0
A user would like to enter ranges in an Excel workbook. What VBA would I use that would label in column C whether the ranges he/she entered are valid or not.

EXAMPLE:

Row       Column:B            Column:C
  1          A1:B200                  Valid
  2          BC10: DD10           Valid
  3          A:B200                    Invalid
  4          C200:A1                  Invalid

The code should say row 3 is an invalid range because it missing the row reference after the "A".
The code should say Row 4 is an invalid range because column "C" is before column "A".
0
Hello, I would like to make some edits to my code to make it work with different types of worksheets. Currently I'm copying and pasting fixed cells, but I would like to incorporate search/match functions in case the information i need are in different cells on different worksheets.

Worksheet Information:
- B2 to B5: Titles of department info
- C2 to C5: Corresponding department info
- Row 8: Header for products
- Cell A8 = serial number. Cell A9 = "Product Information"
- If the department's worksheet has no products, Cell A9 would say "NIL"
- However, some departments might have different formats (eg extra blank column or extra row which cause the code to copy the values

Code's current function:
1. Add 4 columns on left
2. Copy and paste C2 to C5 (now G2 to G5)
3. Copy rows and paste on another sheet (from row 9 to last row)

     
        'Loop each worksheet
            Dim wscount As Integer
    Dim a As Integer
    wscount = ActiveWorkbook.Worksheets.Count
    For a = 1 To wscount
        'activate worksheet (a)
        Worksheets(a).Activate
        'skip if NIL
        
        'Add column and paste department info
        Dim i As Integer
    
Columns("A:D").Insert shift:=xlToRight, _
      CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow

    
    'Loop for cells with content
i = 9
    Do While Cells(i, 6) <> "" 'Currently this includes "NIL"
        Range("G2").Copy
        Cells(i, 1).PasteSpecial Paste:=xlPasteValues
       

Open in new window

0
looking for V_OUT sheet

for example im looking for 65 it appear 8 times

but when hit find 2 it only show 3 times
65434677665.xlsm
0
have this solution need can button to clean the HIST_DTA
29078876--2-23421.xlsm
0
need the percent as show here
needed

222222222222222222222222222222222222.PNG29078870aaaaa.xlsm
0
need create an history to be show  in HIST_DTA






of the selected  i mean to compare ( what  need to be selected )

888888888888888888888888888888888888.PNG29078859b.xlsm
0
1-need the find numbers be show as the result is  show  in A4   V_OUT sheet

2-need show the total columns od data in b1  V_OUT


666666666666666666666666666666666666.PNG8598744458.xlsm
0
I want to be able to enter details on an Access form, which will form the basis of an Outlook calendar appointment. Not only in my calendar but also another team calendar, which i wanted to hard code. I have done the code below:

Private Sub Command127_Click()
' Save record.
DoCmd.RunCommand acCmdSaveRecord

'add to Outlook.
Dim olApp As Object ' Outlook.Application
Dim olappt As Object ' olAppointmentItem
Set olApp = CreateObject("outlook.application")
Set olappt = olApp.CreateItem(olAppointmentItem)

Set olappt = olApp.CreateItem(1)
With olappt
.Start = Me!ContractStartDate & " " & Me!ContractStartTime1
.End = Me!ContractStartDate & " " & Me!ContractStartTime2
.Subject = Me!CaseTitle
.location = "Contract Start Date"
.Body = ""
.reminderminutesbeforestart = 15
.reminderset = True
.MeetingStatus = olMeeting
.recipients.Add ("123@abc.co.uk")
.recipients.resolveall
.Save
End With
Set olApp = Nothing
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Appointment Added!"
End Sub

However, it shows in my calendar fine, I check the appointment, the team calendar name has been resolved fine, but it doesn't appear in there calendar. Is there some code I can add to achieve this?

Any help would be greatly appreciated.

Thanks
Gareth
0
Get expert help—faster!
LVL 11
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Sheet1 is a linked worksheet, so every cell contains a formula.

Sheet2 is a validation spreadsheet to ensure that all minutes entered in Sheet1, Column E have an associated date entry in Sheet1, Column D. To that end, I'm trying to write a validation formula that meets this criteria:

If Sheet 1, Column E has a number >= to 1, and Sheet1, Column D does not have a date, then populate Sheet2 with "Missing", otherwise "".

=IF(AND(Sheet1!$D2=0,Sheet1!$E2>=1),"Missing","")

The above formula is not working, as despite Sheet1, Column D missing dates in cells D7:D11, Sheet2 does not indicate this by populating B7:B11 with the word "Missing".

I have included a spreadsheet to demonstrate the issue.

Thanks,
Andrea
EE_validation.xlsx
0
I had this question after viewing How to add a parameter to this custom function for the number of columns?.

Function CombineText(rg As Range) As String
    Dim col As Integer
    If rg.Rows.Count = 1 Then
        For col = 1 To rg.Columns.Count
            If rg.Cells(1, col) = "" Then
                CombineText = CombineText & "00"
            Else
                CombineText = CombineText & Left(rg.Cells(1, col), 2)
            End If
            If col < rg.Columns.Count Then
                CombineText = CombineText & "-"
            End If
        Next col
    End If
End Function

In this function, the first three characters of cells are in the format "01-", "02-", etc. is picked up from cells from a number of columns.  I would now like to convert the "01-" to A, "02-" to B, etc.  There are no numbers above 26.  If a cell is blank, I would like its value to "X".
0
I have VBA code that grabs a customer name and assigns it to a variable called Cus. How do you display the variable on an Access report?
0
have this solution but then  need to count the coulmns with data

also
when an number appear need be color green the cell

5555555.PNG456778654.xlsm
0
each column have  numbers

V_OUT sheet is where to work with
 

example  03  in each column  
 show the count by column in row 6 above the column
then the total count in b6

AAAA23123.PNG674398_out.xlsm
0
I had this question after viewing How to write a custom Excel 2016 function that gets first two characters of cell if numbers?.

The current custom function works with 3 columns.  I would like to be able to use a different number in other situations.
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.