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

How can I capture an image (jpg, png, gif - some common image type) of the entire content (not just what in currently in the view, but the entire scrollable webpage) of a webbrowser control in an Access database form?
0
Angular Fundamentals
LVL 13
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Hello Team

I have a worksheet named "Summary" which contains name ranges, 15 in total.

Each name range follows a name sequence of CCSum1, CCSum2, CCSum3 etc.  

Can you please advise, using VBA, how to best loop through each named range, e.g., get the code to identify CCSum1, run the code required to loop through each cell in the CCSum1 range  and then move onto CCSum2 etc.  The code for looping through each cell within the named range isn't required, just how to move from one name range to another.

Thanks in advance for any suggestions provided.

Sincerely

Dale
0
Hi,

I have below VBA code that massively converts csv to xls.

My problem that i have is that in my csv files, i have many values that as leading zero's.

Ex:0000000039,0000000039 WORLD ON EDGE

So to be able to keep the leading zero's, i must set the columns to NumberFormat = "@"

The problem i'm facing is that it does put the columns in TEXT format but the leadings zero's are gone.
Instead of seeing : 0000000039, i 'm getting 39.

I've tried: Columns("A:Z").NumberFormat = "@", Range("A:Z").NumberFormat = "@".


The file i have is only 1 macro. The macro ask to select a folder that contains csv file(s).

How can i make all the data in TEXT format so i can keep the leading zero's

Thanks for your help.


Sub CSVtoXLS()
    
    Dim xFd As FileDialog
    Dim xSPath As String
    Dim xCSVFile As String
    Dim xWsheet As String
    Application.DisplayAlerts = False
    Application.StatusBar = True
    xWsheet = ActiveWorkbook.Name
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    xFd.Title = "Select a folder:"
    If xFd.Show = -1 Then
        xSPath = xFd.SelectedItems(1)
    Else
        Exit Sub
    End If
    If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
    xCSVFile = Dir(xSPath & "*.csv")
    Do While xCSVFile <> ""
        
        Application.StatusBar = "Converting: " & xCSVFile
        
        Workbooks.Open Filename:=xSPath & xCSVFile
        
        Columns("A:Z").NumberFormat = "@"
        
        

Open in new window

0
Hi All,

Hope your well,

I have a VB script which is nearly there but just a small part missing.

The script is being configured to delete certain contacts with a particular attribute set. Within that subset of contacts with this particular attribute im also adding some additional logic to not delete all of them e.g.

as an example: I look for contacts with extensionattribute1 set with "SPECIALATTRIBUTE", I then say, if email address 2 is populated DO NOT DELETE, move on to the next. My aim is to delete all of these contacts without an email address 2 set. The bit where im stuck is that I'm also trying not to delete contacts that have a certain domain e.g outlook.com but im not sure on the contains context. My ultimate goal is to look for contacts with a particular attribute, keep the ones that have an Email2address set and if their email1address contains outlook.com and then delkete the rest that do not match these conditions.


Set objRecord = objItem.Find("[Extensionattribute1] = ""SPECIALATTRIBUTE""")

While Not objRecord Is Nothing
     
      If objRecord.Email2Address = "" And objRecord.Email1Address <> "*OUTLOOK.com" Then objRecord.Delete

      Set objRecord = objItem.FindNext

Many Thanks.
0
I am trying to write a function that runs Visual Cut, but I get compile errors. Please help me resolve the errors.

Error Detail:
(1) Line of Code:
Set RetVal = Shell(VisCutApp & "-e " & " " & ""Export_Format:Adobe Acrobat (pdf)"" & " " & ""CR_Path""", 1)
ERROR: "Expected: list separator or )" error caused by "Export_Format"

(2) Line of Code:
"C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe"
ERROR: "Type mismatch" error

Code with errors:

'Function RunVisCut(CR_Path As String)
Dim VisCutApp As Object
Dim RetVal As String
Set VisCutApp = "C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe"
Set RetVal = Shell(VisCutApp & " " & "-e " & " " & ""Export_Format:Adobe Acrobat (pdf)"" & " " & ""CR_Path""", 1)
End Function

This code runs without any errors:
Dim ls_temp As String
ls_temp = "C:\Program Files (x86)\Visual CUT 11\Visual CUT.exe " & _
"-e ""c:\temp\Sales.rpt"" ""Export_Format:Adobe Acrobat (pdf)"" ""Export_File:c:\temp\Sales.pdf"""
Shell (ls_temp)
0
Is it possible to add a custom button that point to my macro in Excel 2019 ?

ThxMacroButton.png
0
I have an Access 2016 database with a table and query that I want to export to fixed width text files monthly (I give a data example below). The problem is that I can't guarantee that the access database structure will remain the same. Due to this I can't use the built in saved exports feature in access. I have copied and modified a script that will create a schema.ini for me on the fly with the proper schema formatting, including field names, field widths, etc. The problem is I am uncertain how to use it to export the data. I was reading this rather informative web page about how to work with a schema.ini file but it references how to use it for import, not export:


I was originally trying to use the TransferText method but I just keep running into errors with it. Most sites I am reading that are newer seem to recommend using ADO commands now.
DoCmd.TransferText acExportFixed, "schema.ini", tableName, "C:\TestFolder\TestFile.txt", True

I am willing to use any method that works and am open to suggestions.

An example of my issue is that in one month it would be that the database table has 2 fields, both width 10:
fieldID Integer
fieldUserName Char Width 10

Open in new window


But the next month it could have 3 fields and different sizes:
fieldID Integer
fieldUserFName Char Width 25
fieldUserLName Char Width 25

Open in new window


I have a script that can create a schema file on the fly to account for the structure changes but I am not sure how to export the data using the updated schema file.

Open in new window

0
I have an Access database that has several users. The database uses mscomct2.ocx and comdlg32.ocx. Two weeks ago there were some enterprise-wide updates that were theoretically unrelated to the database (they were for Windows and SAP). However, something in the updates had the effect of deleting mscomct2.ocx from all the computers which caused problems for the Access database.

Once we discovered this, we reinstalled and re-registered mscomct2.ocx on all the machines (they're all Windows 10) and they are now all working fine except for one of them.

On the one machine, if I go in to VBA and Tools / References, I see that those controls are there and they aren't showing up as missing. The files are also where they should be (\windows\syswow64) and they're the same versions as on the other PC's.

But when Access tries to use either of those controls, we get the following error:

Run-time error '-2147319779 (8002801d)':
Automation error
Library not registered.


So we're unable to use the portions of the database that need those controls. But just on that one computer.

We've tried unregistering and re-registering the controls.

Any suggestions on how to resolve this problem?

James
0
This is a different question, my formula in vba is
=((((q_i^b_1)/((((1/b_1)*((1-d_i)^(-1*b_1)-1))/365)*(1-b_1)))*((q_i^(1-b_1))-((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))^(1-b_1))))+((((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))^b_2)/((((((1/b_1)*((1-d_i)^(-1*b_1)-1))/365)*((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))/q_i)^b_1))*(1-b_2)))*(((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))^(1-b_2))-(((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))*((-LN(1-d_min)/365)/(((((1/b_1)*((1-d_i)^(-1*b_1)-1))/365)*((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))/q_i)^b_1)))^(1/b_2))^(1-b_2))))+((((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))*((-LN(1-d_min)/365)/(((((1/b_1)*((1-d_i)^(-1*b_1)-1))/365)*((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))/q_i)^b_1)))^(1/b_2))-q_ab)/(-LN(1-d_min)/365)))+(t_flat*q_i*30.4)

Can someone help me in solving it for
qi, b1, b2, t_b2, di, dmin, qab.


Thank you.
0
I have developed a very profitable Excel based automated day trading system that I would like to make available to others on a subscription basis. The Workbook will need to be secured including all VBA code and certain worksheets, etc. This system works in conjunction with Interactive Brokers API and their TWSdde Excel spreadsheet.  I will very much appreciate any ideas and guidance you might have.

Thank you very much!
0
CompTIA Cloud+
LVL 13
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Hi
I have an Access database that has linked tables from another database.
I want to use VBA code to convert some of these linked tables into local tables
What VBA code would I use to do this?
Thanks
0
How do I change the following code :

Sub InsertMonth() 'insert MONTH corresponding to the date
Dim LastRow As Long

      LastRow = Range("A" & Rows.count).End(xlUp).Row
      
      Range("B1").EntireColumn.Insert Shift:=xlToRight

      Range("B1").Value = "Month"
 
      With Range("B2:B" & LastRow)
            .Formula = "=TEXT(A2, ""mmm"")"
            .Value = .Value       ' replace formulas with values - optional
      End With

End Sub

Open in new window



to get Excel to insert the column "YYYYMM" in the first column A regardless of the position of the "Date" Column ?

In the above script, one would need to place the "Date" Column in Column A , and then it will insert Month in Column B, but the month is in text format.

Before and After
Note:
-Date is in the format of mm/dd/yyyy
- there are data from row 2 onwards
- objective is re-arrange the data in the desired columns (position) and the have the column "Month" inserted.

Thanks,
0
I need to scrape the names listed in a table on a website.  I have code that can pull data by ID from my company's internal website, but I have no idea how to pull this data.  I'm using VBA to pull the data into a spreadsheet.

<tr class="TableOddRow" id="rltr_6" style=";"><td headers="col_1" class="TableCellResults ">john.doe1</td><td headers="col_2" class="TableCellResults "> john.doe1</td><td headers="col_3" class="TableCellResults ">&nbsp;</td></tr><tr class="TableEvenRow" id="rltr_7" style=";"><td headers="col_1" class="TableCellResults "> john.doe 2</td><td headers="col_2" class="TableCellResults "> john.doe2</td><td headers="col_3" class="TableCellResults ">&nbsp;</td></tr><tr class="TableOddRow" id="rltr_8" style=";"><td headers="col_1" class="TableCellResults "> john.doe3</td><td headers="col_2" class="TableCellResults "> john.doe3</td><td headers="col_3" class="TableCellResults ">&nbsp;</td></tr><tr class="TableEvenRow" id="rltr_9" style=";"><td headers="col_1" class="TableCellResults "> john.doe4</td><td headers="col_2" class="TableCellResults "> john.doe4</td><td headers="col_3" class="TableCellResults ">&nbsp;</td></tr><tr class="TableOddRow" id="rltr_10" style=";"><td headers="col_1" class="TableCellResults ">Johndoe5</td><td headers="col_2" class="TableCellResults "> john.doe5</td><td headers="col_3" class="TableCellResults ">&nbsp;</td></tr><tr class="TableEvenRow" id="rltr_11" style=";"><td headers="col_1" class="TableCellResults "> john.doe6</td><td 

Open in new window

0
Please tell me the easy way to do this, first column had date(daily) and second column volumes. I want to group(add) volumes in second column on monthly basis and put into another column.

Thank you,
0
I am trying to access data from myheritage.com on Internet Explorer using Excel VBA

So far I have been successful in getting the family data from an individual's profile page.

Now I am trying to be able to click on the family member to go to his/her page but cannot find the anchor to do so.

Can anyone help me out with this?

Using a browser other than Internet Explorer is also welcome.

Sub getImmediateFamilyFromMyheritage(IE As Object)
    Dim w As Object
    Dim html As String
    Dim htmlarray
    Dim pcel As Range
    Dim tcel As Range
    Dim i As Long
    Dim DesSzCol As Range
    Dim MembrCol As Range
    Set MembrCol = Cells.Find("Member Column", , , xlWhole).EntireColumn
    Set DesSzCol = Cells.Find("Descendent Size", , , xlWhole).EntireColumn
    Application.Calculation = xlCalculationManual
  For Each w In CreateObject("Shell.Application").Windows
    If w.Name = "Internet Explorer" Then
    
        Set HTMLDoc = w.document
        html = HTMLDoc.body.innerText
        htmlarray = Split(Mid(html, InStr(html, "Immediate family"), _
                            InStr(InStr(html, "Immediate family"), html, "showCAPTCHA") - _
                            InStr(html, "Immediate family")) & vbLf & vbCr, vbLf)
        Set pcel = ActiveCell
        Set tcel = pcel.Offset(1, 1)
        For i = 1 To UBound(htmlarray)
        htmlarray(i) = Replace(htmlarray(i), vbCr, "")
        Select Case htmlarray(i) 'InStr("His son His daughter 

Open in new window

0
Hi Experts

I am (still) trying to query an Access table using VBA (in Word). I initially did this using a stored query but found that it did not return the result. I then changed the code to use a straight SQL query and thought I had resolved the problem - but I have not.

If I pass the parameter to the stored query manually, then it returns a result. If I pass it via code, it does not.

Same thing with the SQL String method. If I run it manually, I get a result. If I run it via the code, nothing is returned.

Any help appreciated. I attach details of the queries etc.

Details of queries etc
0
Dear Experts!

Thank you so much for the previous and current help on this Ms Access VBA Topic , I still need your assistance on how to get or pull all the data from the sub form called [sfrmInvoicedetails Subform] for my string Json string. The little salvage over the internet it appears I'm required to use EOF & BOF, frankly I'm not sure how to use these functions in VBA.

Final working code and approved see below:


Private Sub CmdSales_Click()
  
Dim foo As New Dictionary
Set foo = New Dictionary
Dim Noor As Dictionary
Set Noor = New Dictionary
Dim hoo As New Collection
Dim goo As New Dictionary
Set goo = New Dictionary
Dim Zoo As New Dictionary
Set Zoo = New Dictionary
Dim Koo As New Collection
Dim Too As New Collection
    With foo
        .Add "PosSerialNumber", Me.INV
        .Add "IssueTime", Me.InvoiceDate
        .Add "Customer", Me.Customer.Column(1)
        .Add "TransactionTyp", 0
        .Add "PaymentMode", 0
        .Add "SaleType", 0
        .Add "Items", Koo
     Koo.Add Noor
     Noor.Add "ItemID", 1
     Noor.Add "Description", Forms!frmInvoice![sfrmInvoicedetails Subform]!Description.Column(1)
     Noor.Add "BarCode", "4589630036"
     Noor.Add "Quantity", Forms!frmInvoice![sfrmInvoicedetails Subform]!Qty
     Noor.Add "UnitPrice", Forms!frmInvoice![sfrmInvoicedetails Subform]!UnitPrice
     Noor.Add "Discount", Forms!frmInvoice![sfrmInvoicedetails Subform]!Discount
     Noor.Add "Taxable", hoo
     hoo.Add 

Open in new window

0
i need to get values from a subform.

Before that happens, i just want to  make sure the subform is currently selected ?

if not then i need to flag them to select a row in the subform ?

Me!SubformName.Form.CurrentRecord  ?



Thanks
fordraiders
0
Excel  Application.OnTime TimeValue("09:30:00"), "doWriteData", , schedule := False will not compile and execute.
Excel  Application.OnTime TimeValue("09:30:00"), "doWriteData",, schedule = false  will compile but not consistently execute

Is there any way to cancel this that works?  Thank you very much
0
Fundamentals of JavaScript
LVL 13
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

I need help with VBA sample code in word.
I have a userform with 2 comboboxes
I need to connect to an excel file with 2 worksheets.
first combobox is populated by first column of the first worksheet.
second combobox need to be populated by the first and third column of the second worksheet.

Any help would be appreciated
0
I am writing some PowerPoint VBA code in a userform that loads an programmatically exported image of the current slide.

It's working as expected, but only when the VBE is open!

If I close the VBE, the userform loads and as it reaches the followng line, VBA appears to crash (variables are reset) without raising any error and hence the userform disappears.

ActiveWindow.View.Slide.Export "C:\Temp\thumb.jpg", "JPG", 960, 540

Open in new window


The userform is loaded via a customUI ribbon onAction callback.

If I close the VBE and load the userform by calling a public macro via Alt+F8, it works as expected.

I have a diffferent userform (shown on an application event) which uses the same method above without issue so I rebuilt the userform exhibiting the behaviour but the issue persists.

I then built a really simple project with a single userform containing a single image control and this code:

Private Sub UserForm_Initialize()
  ActiveWindow.View.Slide.Export "C:\Temp\thumb.jpg", "JPG", 960, 540
  Me.Image1.Picture = LoadPicture("C:\Temp\thumb.jpg")
End Sub

Open in new window


It loads from this test macro, and the ribbon callback, with the VBE is closed/open:

Public Sub LoadTestForm()
  UserForm1.Show
End Sub

Public Sub OnAction(control As IRibbonControl)
  UserForm1.Show
End Sub

Open in new window


What is going on?
0
Is there an expert in vba who could help me some programming issues in excel? This requires and online screen share and brief discussion. I will pay if needed please.

Thank you,
0
I and using a drag and drop feature found on the web.The problem is that it opens to the last folder accessed.I am wondering if i can change the location last accessed in vba before opening the drag and drop.
0
Hi all, i did some enhancements on existing access file and has saved as accde file and send to the users.
Now the issue is some of the users are facing issues while accessing that file. can someone tell why this happens only in some systems and not all. at the moment i am not sure which access they have installed.

Errors they are getting are:
1 user is getting can't find language DLL msain.dll

other user is getting ----The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms and reports. ...
Other user is getting------The Visual Basic for Applications project in the database is corrupt.

other user is getting----cannot update database or object is read only.


each user is getting different message...some users didn't faced any issue at all.

can someone kindly help me to resolve these 4 issues please?
when i google it says for some error needs to repair access 2010 from control panel..but will this resolve the issue?
do they need to reinstall access 2010?
Note: these users didn't faced any issues on the same application file they are working. they only got issues with the file i sent with the new enhancements.

i am using access 2010 and sql serve 2014 as back end.
0
Hi guys, this should be easy, but I can't get it right. I have the below code in the UserForm_Initialize() event along with some other items.  This is used to populate my comboBox. I want to be able to refresh the combo box independent of the other routines in the userForm_Initialize event. I place the code in a private sub, and attempted to call it like this. "Call getCboListClasses", but I get compile error, sub or function not defined. What is the correct way to set this up and be able to call this routine.?

Thanks in advance.

Private Sub getCboListClasses()
    Dim src As String
    Dim i As Integer
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

     ' connect to the Access database
    Set cn = New ADODB.Connection
    myConn = TARGET_DB
    With cn
     .Provider = "Microsoft.ACE.OLEDB.12.0"
     .Open myConn
   End With

     Set rs = New ADODB.Recordset

     src = "SELECT [Classes],[AvailableSlots],[SlotsTaken],[Remaining] FROM qryClasses"

     rs.Open src, cn, adOpeDynamic, adLockOptimistic

     rs.MoveFirst
     i = 0
     With Me.cboClasses
         .Clear
         Do
             .AddItem
             .List(i, 0) = rs![Classes]
             .List(i, 3) = rs![Remaining]

             i = i + 1
             rs.MoveNext
         Loop Until rs.EOF
     End With
     rs.Close
     cnn.Close
     Set rs = Nothing
     Set cn = Nothing
     Exit Sub
     MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"

End Sub

Open in new window

1

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.