VBA

10K

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

Hi EE,

How do you make an audit trail remote connections to an MS Access db 2007 to 2013, is this possible?

The more variables that can be captured the better, the queries being entered will primarily be select queries.

Any assistance is welcome.

Thank you.
0
OWASP: Forgery and Phishing
LVL 12
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Hi Experts,

I need to have the following in a sub form within a main form.

From the table Patients_Daily_Medications

with the fields below.
PatientID
Medication
Day
TimeGiven
Sequence

I need a crosstab query that would show me Medications as column heading and sequence as row heading and TimeGiven as the values.

See attached what the data looks like, and what I am trying to accomplish (not completed as you can see...)

At the end I would need this query as the record source of a form that has a patientID and a date as fields, so this should only return values of a sibgle patient at a particular day.

PS- Note TimeGiven has a date+time, we need to extract the Time only.

PS -, a VBA function that will open a recordset of that table, Accept two params (PatientID,Day) and return the following would also serve the purpose.
ALBUTEROL: 9:00 AM, 11:00 AM      BUDESONIDE: 11:AM, 1:00PM, 5:00 PM      MIRALAX: 3:PM

Thanks in advance.
Untitled.png
Untitled1.png
0
So I have my import tool working great. It allows the user to browse to the excel file and fills in the file name txt file (txtFileName)
Importing the date to the table works great.  I can even have it do all the sheets of the workbook.

I was wondering if there a way to browse within the workbook to display just the worksheets and the user can select what worksheet they want to import and it stores that worksheet name in the txtWorkSheet.

Thanks
john
0
Running Access 2003 and creating an Excel spreadsheet using vba coding
My Access 2003 vba code works correctly in deleting columns in an Excel 2013 spreadsheet.
But when run on user pc running Office 365( I think it is Excel 2016) the code runs without errors, but fails to delete the proper columns.
her is my vba coding:
     Dim xls     As Excel.Application
     Dim wkb     As Excel.Workbook
     Dim wks, wks2    As Excel.Worksheet

     Set xls = New Excel.Application
     Set wkb = xls.Workbooks.Open(excelfilename)
     Set wks = wkb.Worksheets(1)
     Set wks2 = wkb.Worksheets(2)
        xls.Worksheets(1).Range("A:A,B:B,D:D,H:H,Q:Q,R:R,S:S").EntireColumn.Delete
    xls.Worksheets(1).Columns("A:L").EntireColumn.AutoFit

  xls.Worksheets(1).Name = "Excluded Holdings"
      xls.Worksheets(2).Range("A:A,B:B,H:H,P:P").EntireColumn.Delete
          xls.Worksheets(2).Columns("A:L").EntireColumn.AutoFit


  xls.Worksheets(2).Name = "Information Table"

     wkb.Close True

     Set wks = Nothing
     Set wkb = Nothing

     xls.Quit
          Set xls = New Excel.Application
     xls.Visible = True
     Set wkb = xls.Workbooks.Open(excelfilename)
varreturn = SysCmd(acSysCmdClearStatus)
0
Hi,

Found a bit of VBA code to place text in the subject line of Outlook:

Sub New_Subject_Email()
Dim MItem As MailItem
Set MItem = Application.CreateItem(olMailItem)
MItem.subject = "36355 XXXXXXXXXX - "
MItem.Display
End Sub

It Works, but I would love to have the cursor ready (blinking) to type after the "36355 XXXXXXXXXX - " when I use the TAB key to activate the Subject line.

How would I change the VBA code accordingly?
0
Hello Team

I currently have a issue with linking two mailboxes to an MS Access  form.

There is nothing complicated about the form, very basic, just counts emails in mailboxes.

I have linked up to 20 mailboxes but when I am attempting to link two the last remaining two mailboxes I get the classic , 'Can't find the wizard.  The wizard has not been installed...etc'

All other boxes are linking with no issues so it can't actually be the wizard issue as MS  presents it to be.

Anyone got any ideas what can been allowing the vast majority to link but not the last remaining ones? Alternatively, is it possible to link to a mailbox without having to use the wizard but by using VBA for example?

Thanks in advance.

Dale
0
I am trying to create a simple outlook addin that reads and writes to the ribbon bar. I have bought the addin express tool, but somehow the addin has some tweaks, since it does not react like the normal VBA with events etc.

I am looking for a sample that would achieve the following, gladly I would be willing to pay for this if someone would take some time to help me.

in this sample i need to see how i can read and write values from a ribbon bar element, in one seperate class (in the addin express it only lets me call events in the main Addin class, i would like to have the call run in a seperate class)

in the sample, i would like to react to an event from an outlook ribbon item in one sperate class.

write to an open Form from a seperate class

the element to write to the ribbon should be a drop down list,
In the ribbon add a button, when clicked it will read the value from the drop down box and call a sub in a seperate class file
have a form open, and with a timer in a seperate class update a labe with the time
0
https://docs.microsoft.com/en-us/office/vba/api/outlook.mailitem.copy

Please refer to the above link first.

Set myCopiedItem = myItem.Copy
myCopiedItem.Move myNewFolder   ' this also moves myItem in myFolder into the Junk folder!

Open in new window


These two lines of VBA code works as follows:
1.The copy command duplicates myItem in myFolder, say myCopiedItem, and
2.the move command moves myCopiedItem to myNewFolder, and then
3.automatically deletes myCopiedItem from myFolder, and then
4.the deleted myCopiedItem is automatically trashed(moved) into the Junk folder.

FYI, 3 and 4 are not explicitly mentioned in the above link.

My questions are:
Q1. How do I copy myItem from myFolder to myNewFolder without making a duplicate copy? I do not want to make a duplicate copy, say myCopiedItem, in myFolder.
Q2. How do I copy myItem from myFolder to myNewFolder without using the move command? Using the move command seems to send myCopiedItem from myFolder into the Junk folder, due to the inherent delete & trash operations of 3 & 4, respectively.

I am using outlook 2010.
0
I had this question after viewing Visio 2013 - Undo has stopped working!.

I have followed the example shown in this link:

http://www.visguy.com/2015/07/07/deleting-visio-shapes-programmatically/

I have the same code structure as in the example, and when the code runs successfully, I was expecting to see afterwards the "Undo" button enabled in Visio, showing me the Undo Scope name that I set, but button appears dissabled.

What I am missing?
0
hey i am looking to have the cash on hand reconciliations do the same thing that the bank account reconciliations do. Once the difference is 0, then you can lock the reconciliation by clicking the button.  The reconciliations are the tabs from Oct  to Jun. Also, can we add an if coding so that the bank reconciliation can not be locked until they put a date in? Currently it says Please enter statement date in red but I want my staff to have to put in the date before it will actually lock.  Also, if the date has not been put in can a message box pop up saying that the bank statement date has not been entered?

Thanks!
Bank-rec-template.xlsm
0
Bootstrap 4: Exploring New Features
LVL 12
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.

I want to read from the attached excel the sheets "Wells" and "DATABASE" and produce the sheet "Result". I just put in the "Result" some samples and not including all the data.

Regards,

Dallag
Production-Repor_Final_Test.xlsm
0
I want to write a vba macro that does the following

link a table from sql server to access database, embed/program and execute access query in vba and remove the linked table

My code so far is this.


Sub Update()
Dim Con       As ADODB.Connection
Dim RS        As ADODB.Recordset
Dim myCommand As ADODB.Command



Dim AccessFile As String


Set Con = CreateObject("ADODB.Connection")

Sheets(1).Select
ActiveSheet.Select

'AccessFile = Range("b3").Text & "\" & Range("b4").Text & ".accdb"
AccessFile = Range("b3").Text & "\" & Range("b4").Text & ".mdb"

With Con
  .Open "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" & AccessFile
End With

'cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & Conn

Set myCommand = CreateObject("adodb.command")

''************ link table dbo_production_values from a sql database somewhere on the network                 <-----------------------

With myCommand
    .ActiveConnection = Con
    .CommandType = adCmdStoredProc
    '.CommandText = "qry_Daily_Production"
    .CommandText = "SELECT AC_PROPERTY.propnum, Prod.client_day, Prod.allocated_oil_production, Prod.allocated_water_production, Prod.allocated_gas_production, _
    Prod.api FROM AC_property INNER JOIN dbo_production_values AS Prod ON AC_property.api=prod.api WHERE prod.client_day >= DateAdd ("d", -300, Date());""
   '.Parameters.Append .CreateParameter("paramDateInput", adDate, adParamInput, , Sheets("DataEntry").Range("D5").Value)
    Set RS…
0
example image
Please refer to the example image above.
The two kinds outlook items shown in the image are of the same class type(olReport) but are apparently shown by different icons.
How do I detect each of these ReportItems using VBA code?

myItem.Class OR TypeName(myItem) does not seem to recognize this difference.
0
Attached is an example Excel spreadsheet with syntax similar to a table in a 60 page word doc. It represents a data structure that has many substructures in it that are controlled by loops and if statements. It is hard to see the structure, so I have learned a couple of techniques to try to indent sets of rows to the next column. For example, I now have a macro that will indent a manually selected set of rows one column to the right.
Nested-labeled_Conditions.xlsm

To get an idea of the desired result, this link gives a similar example of pictures of the a sheet before indenting and after indenting.
https://www.experts-exchange.com/questions/29130882/Excel-Spreadsheet-Error-when-moving-rows-one-column-to-the-right.html#a42766586

If you have a solution, I'll add an extra nested structure to see if it works in general.

<<EDIT 2019-01-16 12:40am EST>>
I changed the title in an important way. No longer should the Headers and Footers be considered as expressions. I now can see that expressions are much too difficult to maintain and vary immensely from table to table as there are potentially many different authors with their descriptive writing style.

The Headers and Footers should be considered as a single known word(s) - and it will be the first word in Column A. Currently, it is safe to say that the Footer will be always:
End - but test should be case insensitive, so end also is a footer.

Some headers I have seen in general are as …
0
example image
Please refer to the example image provided.
How do I fold/unfold a certain group of mail items set as a certain category, say category labeled "category 1", of outlook 2010 using shortcut keys or macros?
Please kindly provide VBA code when possible.
0
Hello experts,
I have the following code in order to apply format for for specific range.
I would like to create an Apply_Format procedure to add to my personal.xlsb with the following requirement:
1.      Inputbox: “Select initial range (header range) in which you want to apply format”. Error handling: exit sub if the data reported is not correct or exit sub if cancel button is activated.
Here is what I have for the moment:

Sub Apply_Format()
Dim wb As Workbook
Dim ws As Worksheet
Dim iX As Integer

Set ws = ActiveSheet
With ws
'Remove gridlines
ActiveWindow.DisplayGridlines = False
'Apply Borders
With .Borders
.LineStyle = xlContinuous
.ThemeColor = 7
.TintAndShade = 0
.Weight = xlThin
End With
'Apply Font
With .Font
.Name = "Segoe UI"
.Size = 11
End With
'Set columns width
For iX = 1 To tbl.ListColumns.Count
tbl.ListColumns(iX).Range.ColumnWidth = 20
Next iX

'Apply Alignement
With .HorizontalAlignment = xlLeft
End With
End With
'Apply fill color for header row
'With .HeaderRowRange
With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 26112
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Apply font white
With .Font
.Color = vbWhite
.Name = "Segoe UI"
End With
End With
End Sub

Open in new window

0
Hi,
1. Trying to update few cell details using control button but the data will come from any other workbook. i am very bad in VBA knowledge, is it possible using VBA code. Please suggest me, i am attaching example file. See few instruction in excel file.
2. My second query is about to take screenshot of table which we are updating and copy paste in new cell (Screenshot), second tab screenshot should go on same Tab just below the first screenshot.

Please help me on this. I tried finding solution on Google but not able to find any clue and coding.
Daily-report.xlsx
Utilisation.xlsx
0
Hello experts,

I have attached a table template that I want to reuse for my future files and I would like to apply for the existing one.
I was thinking about two procedures
1-Create a Sheet template with the current format attached
2-For existing tables apply this format at least fill colors and borders.

The idea is to add them to my personal.xlsb. Thank you very much for your help.

Could you please help me with this?
Thank you in advance for your help.
Template-Table.xlsx
0
Microsoft Access 2016 application:  OS Windows 10 Pro

I have a form which I will call frmForm1 (Single form NOT Tabular form) in which I need to display an image (picture) originating from a file called ABC.jpg.

Of course I could embed the image in a field of the underlying Table (tblTable1), but that would make the table occupy an unmanageable amount of space if the number of record grows.  Typically the underlying table contains about 10 thousand records with about 50 fields.

What is the recommended method to accomplish this goal.
0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

I have a function in Access to retrieve some data from a web page. I need to get the correct syntax to access the next page of the process.

This is the code that I'm currently using that is not working.
   Set lnk = QPR.Document.Links(1)    ' 3=TMMK-VEH,4=TMMK-PWT,5=TMMC,6=TMMTX,7=TABC,8=NUMMI,9=TMMI,10=TMMBC,11=TMMAL,12=TMMNK
   lnk.Click

Open in new window


I believe the target page has been changed.

This is the source code of the page. I need to to click the link that is labeled "TMMK-VEH".
<html>
	<head>
	   <title>ToyotaSupplier.com: Supplier Key Performance Indicators</title>
	   <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
	   <link href="theme/Master.css" rel="stylesheet" type="text/css">
	   <script>
			function hidestatus() {
			    document.status = '';
			    return true;
			}
			if ( document.layers ) {
			    document.captureEvents( Event.MOUSEOVER | Event.MOUSEOUT );
			}
			document.onmouseover=hidestatus; document.onmouseout=hidestatus;
		</script>
		<script LANGUAGE="javascript" SRC="include/actionSubmitter.js"></script>
		
		
		
		
  
  		<script charset='UTF-8'>

			var environment = "prod";
			if(environment=='stage'){
					window['adrum-start-time'] = new Date().getTime();
					(function(config){
					    config.appKey = 'AD-AAB-AAH-WRX';
					    config.adrumExtUrlHttp = 'http://cdn.appdynamics.com';
					    config.adrumExtUrlHttps = 'https://cdn.appdynamics.com';
					    config.beaconUrlHttp = 

Open in new window

0
My problem involves rogue page breaks. I'm using MS Access in Office 365. I have an application that creates legal documents for 15 different states. The application has seven base reports from which to build these documents. Because of the various states boiler plate,  page breaks occur at different places. I need to force page breaks in order to keep paragraph headings with at least one line of the corresponding paragraph.

All seems to work well accept when developing about the 10th state, the last page break creates multiple page breaks instead of just one as expected.  The total page breaks in a report is between 12 to 18, although each state uses no more than four or five.

Once the "seal" on this issue is broken, then all of the reports have the same issue, even prior reports that had been working.  I'm wondering if I should re-install MS Access? Perhaps one of the MS Access system files has beeen corrupted?

Any thoughts?
0
Hello, I'd like to open an excel file located on a shared point server. While running vba code, I got an error message 1004 : 'Open' method of object 'Workbooks' failed. Don't see any issue in vba code or Loadfilepath variable value. Please help. Thanks.
0
On the AFL tab, for the notify column, Is it possible to have it automatically send an email via MS Outlook? The email will be on the staff tab and each staff will have their own email. I was going to make a mock email for each staff but the column seems to be locked somehow. So once all the information in the row is filled out (besides the confirmation column). a button will appear in the Notify column for that row. When you click it, it will send a generic email That will say:

(Column G cell),

Can you work a(n) (Column D cell) on (Column B cell) at (Column C cell)? This will bring your hours up to (Column I cell) before adjustments.

Please let me know at your earliest convenience.
Dec-31-Schedule.xlsm
0
How to set an outlook folder to a name defined in a variable?

This works if the destination folder is explicitly defined:

Dim topic As String
topic = "testXYZ"

Dim TestFolderXYZ As Folder ' this is the destination folder

Call EMAIL_Move_from_Inbox(topic, TestFolderXYZ)

This throws an error:

Dim topic As String
topic = "testXYZ"

Dim destFolder As Folder
Set destFolder = TestFolderXYZ

Call EMAIL_Move_from_Inbox(topic, destFolder)

The sub i'm calling is defined as:
Sub EMAIL_Move_from_Inbox(ByVal topic As String, ByVal destFolder As Folder)


I've tried a few different syntax variations including placing the foldername in quotes  "TestFolderXYX" as well as trying to use an object.Name syntax such as:   Set destFolder.Name = "TestFolderXYZ".
0
Using VBA - I need to write a REST/POST command with JSON parameters.  However, I can't seem to find a simple example that I can follow.  Does anyone have anything?
0

VBA

10K

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.