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.

Hi,
I get type mismatch error to right line below

        tm = Replace(Cells(rw, 3).Value, ".", "")
        If Trim(tm) = "" Then
            tm = ""
        Else
            tm = Right("0" & Hour(tm), 2) & Right("0" & Minute(tm), 2)
        End If

Open in new window

with this values. Please help.
20a.png
0
Experts,

in a previous ask to "Convert Months to Days for Total price Calculation". I had an issue with entering a "Renewal Date" into the userform. In the attached workbook is the fix.  

This issue was fixed but changes are not incorporated into this worksheet 29173473a.xlsm. Please incorporate the fixed code.


https://filedb.experts-exchange.com/incoming/2020/02_w08/1444654/29173473a.xlsm

https://filedb.experts-exchange.com/incoming/2020/02_w07/1443481/29170885a.xlsm
0
In Access I have a text box with the date formay d mmm yyyy. I want to check that the user has not entered a date that is after today. What Access VBA code would I use? Thanks
0
A bug in the attached script creates all blank output when the cell references have not data.

For example, cell BR10 is blank, so the "Numbers_3" sheet has not data. It has to do with blank cells, because when I put data in that cells I get cell data in "Numbers_3" sheet.

I would like to resolve it where I can have blank cells because if I have to put data beyond number 70 I will need another script that will delete any row containing any number above 70.


blank cells create blank resultsGreen represents blank cells

The attached file contains data in all the "Numbers_X" sheets but after the macro is run only Numbers_2 has data and it is the only filled line.
CreateCombinationsBug_sheetsblank-c.xlsb
0
Experts,

I need help with converting the attached link file worksheets Original Quote and RAW_DATA to a Table format with the ability to convert back to a range while having the code to still function correctly.

https://filedb.experts-exchange.com/incoming/2020/01_w03/1440849/29169174.xlsm
0
Hi I am getting the error "The action or method requires a file name argument" in the following

DoCmd.TransferSpreadsheet acExport, , "q_TransferTransactions", , True

Is it not possible to just export the query so that it opens in Excel?
0
The attached file with script removes "decis" but would like to combine both "remove Deci" scripts from prior posts into one script and make it faster.

The attached file contains two additional sheets to test with.
CombineRemoveDecisandMakeFaster.xlsb
0
The idea is to create a script that removes rows where there are 2 or more numbers from different "decis". A deci is defined as numbers from 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79, 80-89, and 90-99,

There should be:

1) a prompt would ask for input of "Maximum numbers allowed from different decis" or something similar. If the input is one (1) then
2) choose the sheet to be processed
3) sheet "Numbers_3" would be edited as follows:

 This process should be able to be repeated for any sheet similar to "Numbers_3"

Remove rows containing 2 or more numbers from different decis
Notice how all the green rows contain number from only 1 "deci". That is the goal.
Remove-rows-with-2-or-more-numbers-.xlsb
0
The idea is to create a script that allows a maximum of "X" numbers from same "deci". A deci is defined as numbers from 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79, 80-89, and 90-99,

There should be:

1) a prompt would ask for input of "Maximum numbers allowed from same deci" or something similar. If the input is one (1) then
2) ask to click on any cell that contains numbers to be processed then
3) sheet "Numbers_3" would be edited as follows:

 This process should be able to be repeated for any sheet similar to "Numbers_3"

max of "X" from same DeciThe picture shows items in RED that should be removed they all contain more than one number from the same deci. The sample numbers that should remain are 3, 7, 10, 20, 30 since only 3 and 7 are from same deci. 3, 7, 10, 20 and 29 are shown red since it contains 3 and 7 from same deci plus 20 and 29 from same deci. Thus it does not meet the criteria.
AllowMax_X_fromsameDeci.xlsm
0
Hello. Im requesting assistance with the following. I have a excel file that i want to filter according to the dropdown list selected. attached is the example file. In the "main" tab is where the table and the dropdown list will be featured. Once the table is filtered to the dropdown selection, the last column (column E on the "main" tab in the attachment) needs to be answered either with YES / NO. Once i have answered all the rows, I want excel to record the options selected in the "results" tab.

the "results" tab will have the same columns as 'main" tab.

Your assistance is greatly appreciated.
work_deck.xlsx
0
Hello Team


I have a userform which contains a tabcontrol with seven pages. On page 7 (6) there are nine checkboxes.

What I require is that, when the user selects to save the record, code is triggered that checks to identify if at least one checkbox as been selected. If one is identified then the save is allowed to proceed  If after all the checkboxes have been analysed and no checkboxes have been selected then the user will be alerted that no checkboxes have been selected and then exist it the save procedure.

Form Name:             frmRewnalAudit_Bus

TabControl Name:   TabCtlCats

Page Number           7 (6)  Page tab is named 'Doc Check'



Each check boxes follows the sequence, Check1, Check2, Check3 etc

Ideally, for example, what I am looking for is, code to loop through checkbox which contains the left string 'Check' and once a true value is identified, exit the loop procedure and execute the save code or after all checkboxes have been looped and no true value has been identified, exit the save routine.

Thank you in advance.

Dale
0
The attached file contains code that makes combinations from data in "sheet1" B10:AG19  and puts them in sheets "Numbers_X" where "X" is the first number in B10:B19.

When the code is executed again it deletes data in sheets "NumbersX" then writes new data.

The goal here is to edit the code to maximize the number of combinations made.. The current script makes combinations using 32 numbers from "sheet1" (grey / white alternate for easier view). The current code also uses the first number as the only common number as can be seen in sheets "Numbers_X".

The additional numbers have been highlighted light and dark yellow. If it is not possible to max at 69-70 then I'll accept the maximum possible under the circumstances. Please specify the maximum possible numbers used so I can adjust the data accordingly.
CreateMaxCombinationsForSpecificGro.xlsm
0
Hello,

I am looking to split out a a string from a bar code that looks like the following:
[)> 06 1JXY123456789123456789 P123f210 Q50 BBOX90 V0000001234 4LUS K 2S 1T 15K 2L 22L  
I want to separate each spaced item into its own field. Each datatype is separated by space and the end result I want to populate into a table:
Field1 = XY123456789123456789, Field2 = 123f210, Field3 = 50, and so on
Also note that I want to remove ether the first one or 2 characters of each so it is easier to read. Any Ideas on how to accomplish within Access 2013?
0
Hi. I was given the following code to email Access tables as Excel files. What can I change "F:\Documents\TableDump\Tables.zip" to so that it works on any computer with say a C drive? Thanks

Function ExportTables()
    Dim dbs As DAO.Database
    
    Dim tdf As DAO.TableDef
    
    Dim shell As Object
    Dim ZipFile As Variant
    
    InitializeZipFile ("F:\Documents\TableDump\Tables.zip")
    Set shell = CreateObject("Shell.Application")
    ZipFile = "F:\Documents\TableDump\Tables.zip"
    
    Set dbs = CurrentDb
    
    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSYS" Then
            '/ output the table to a CSV file:
            DoCmd.TransferText acExportDelim, , tdf.Name, "F:\Documents\TableDump\" & tdf.Name & ".csv", True
            shell.namespace(ZipFile).copyhere ("F:\Documents\TableDump\" & tdf.Name & ".csv")
            Do Until shell.namespace(ZipFile).items.Count >= 1
                'Call gotosleep(100)
            Loop
        End If
    Next tdf
    
    Set shell = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

End Function

Private Sub InitializeZipFile( _
  ZipFile As String _
)

    Dim intFile As Integer
    
      If Len(Dir(ZipFile)) > 0 Then
        Kill ZipFile
      End If
    
      intFile = FreeFile
      Open ZipFile For Output As #intFile
      Print #intFile, Chr$(80) & Chr$(75) & _
        Chr$(5) & Chr$(6) & String(18, 0)
      Close #intFile
End Sub


Function EmailTables() As Boolean
    Dim objOL

Open in new window

0
Hi Experts,
 I have one Excel file in one folder (C:/Source). I have also on Macro enable excel file where i need to copy source data to Macro enabled file with formatting. I need your help to write Macro to get file from Source folder and paste to Output tab.    I am attaching Input and Report file with sample data,

Can you please help me?

Source File

Input
Desired Output
OutputInput-FIle.xlsx
Report.xlsm
0
I have an Excel VBA module that sends an email that includes a hyperlink to a network drive location (that may have spaces). I have some code that sort of works but needs a bit of help...

  Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = managername & "," & vbNewLine & vbNewLine & _
              "Data has been dumped from the survey controller on project number " & newjob & ".  The name of the file is: " & vbNewLine & vbNewLine & _
              filefromname & vbNewLine & vbNewLine & _
              "and it is located in the following folder:" & vbNewLine & vbNewLine & _
            "<a href=""<file:///" & GetFolder & ">"">" & GetFolder & "</a>" & vbNewLine & vbNewLine & _
                "Comments:" & vbNewLine & surveynotes
    MsgBox strbody
    
    On Error Resume Next
    With OutMail
        .To = manageremail
        .Subject = "Some survey points have been downloaded on one of your projects"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

Open in new window


The email sends no problem, and the extra chevrons in the string help me pick folders that have spaces in them.  But when the user gets the email, they see the hyperlink source code in the Outlook email like this: (PS - the link works no problem)

HTML is showing
instead of seeing it neatly like this:

What I want it to look like
If I grab the "strbody" variable and paste it into dreamweaver or some other editor, it looks fine, but when the Outlook email is received, the HTML code for the link shows up.  What can I do so that the user doesn't see all the <a href> text that goes with the hyperlink?


Thanks very much!
0
Hi

What Access VBA code would I use to automatically email all Access tables as Excel spreadsheets
via Outlook without the user being able to alter the data. So I want the emails to fire off straight away.

Thanks
0
Hi

What Access VBA code would I use to generate a list of all the control names on all forms in Access and export this list to Excel

Thanks
0
The code makes combinations from all rows in "sheet1" and puts them in sheets "NumbersX" where "X" is 1-10.

The enclosed file dated 2-2020 has had changes to the numbers in B10:AG17. B18:AG18 remains the same as in the original code.

After changing the numbers in B10:AG17, Running the code presents error "Error #13 Type mismatch". But the code seems to run as intended.

What I would like to know is:

1) why the error? and
2) does it affect the results of the code?
3) if results are not affected, what can I do to eliminate the error?
CreateCominationsFromGoups_2-2020.xlsm
0
I have an excel file with Name email address and html file location.
I need to automate this process.
i.e. Send email based on the email address and html content in the email.
GTMarketing.xlsx
0
Hi
I have an Access form were the RecordSource is a table called "t_Licence". If the user edits any field I want to keep a record of what the field was before and after. What events and VBA code would I use to do this?
0
VBA code for Office 365

Refresh table Links  

Does anyone have a code example of how to refresh Links in Office  365.
Links that are going to sql server  "work_flow"  is my ODBC connection database ?

Thanks
fordraiders
0
I have an Excel 2019 spreadsheet macro that has some VBA in it.  Ideally what I would like is

  1. for a user to right-click a file in Windows Explorer (Windows 10) and select "Send to" and have the Spreadsheet available from the "Send to" menu.  Then, when the spreadsheet opens, it gets the name of the file and Autoruns using that file name in the VBA code
  2. Or, if the spreadsheet is opened without the "Send to" method, it will not autorun

Is this possible, and if so, please help...
0
Hello,

I found the following code that will scrub Outlook calendar and put appointments in Excel spreadsheet.  I'd like the code to apply to a specific calendar titled 'Projects'.  How can I modify the code so its only pulling from that specific calendar?

Option Explicit 
 
Sub ListAppointments() 
     
    Dim olApp As Object 
    Dim olNS As Object 
    Dim olFolder As Object 
    Dim olApt As Object 
    Dim NextRow As Long 
     
    Set olApp = CreateObject("Outlook.Application") 
     
    Set olNS = olApp.GetNamespace("MAPI") 
     
    Set olFolder = olNS.GetDefaultFolder(9) 'olFolderCalendar
     
    Range("A1:D1").Value = Array("Subject", "Start", "End", "Location") 
     
    NextRow = 2 
     
    For Each olApt In olFolder.Items 
        Cells(NextRow, "A").Value = olApt.Subject 
        Cells(NextRow, "B").Value = olApt.Start 
        Cells(NextRow, "C").Value = olApt.End 
        Cells(NextRow, "D").Value = olApt.Location 
        NextRow = NextRow + 1 
    Next olApt 
     
    Set olApt = Nothing 
    Set olFolder = Nothing 
    Set olNS = Nothing 
    Set olApp = Nothing 
     
    Columns.AutoFit 
End Sub

Open in new window

0
I have had this happen several times before and always ended up rebuilding the entire workbook.

The problem hit me today it is inside a pretty big workbook with vba code that was not working correctly.

I tried several dozen "fixes" but eventually had to bite the bullet and rebuild it. After rebuilding it the vba worked properly.

But in my efforts to fix things I came up two interesting workbooks.

Open NoBug.xlsm an run sub deletenow33. It does not abort.
Open Bug.xlsm and run sub deletenow33.  You will see an abort.

The Workbooks are practically identical except Bug.xlsm gives the privacy warning.

I have virtually everything including reverting from xlsm back to  xls, and the bug still occurs.

I am considering turning the .xlsm into a .zip file and doing further research, but I hope someone on experts exchange can tell me how to fix the problem WITHOUT rebuilding the workbooks.






a very simple example of why this problem is so frustrating.

I have attached to workbooks Bug.xlsm and NoBug.xlsm.  They are nearly identical. The only difference is that when I save the first
bug.xlsm
NoBug.xlsm
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.