Visual Basic Classic

163K

Solutions

43

Articles & Videos

58K

Contributors

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi Guys, within an Excel macro, I use the following code which filters Column 12 for a Range called "PrevDay" (which is a Cell which has the Previous Day's Date) but it does not work. What's wrong with the code?

Sheets("PL Star Accrual").Select


    Rows("26:26").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$26:$AZ$242").AutoFilter Field:=15, Criteria1:="0"

    ActiveSheet.Range("$A$26:$AZ$242").AutoFilter Field:=12, Criteria1:=Range("Prevday")
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hello,

This is another in a series of questions I have posted recently to come up with Excel VBA code that will selectively reformat a specified string (or strings) of text within a cell, but not the entire cell.

For example, suppose you wanted to reformat a sentence in cell B2 to that shown in D2:

2017-06-27a_EE.PNG
Note that the particular formats shown in this screenshot are arbitrary as the main objective is to have a code which can apply any desired formatting.

Thanks
0
I have a dropdownlist that a user can select their system trouble shooting problem with. Once they select the problem I want the solution to be shown in a label..please am using vb6.0
0
I am using WinHttpReq. to download a CSV file to import into one of my Access projects.
I use this code to retrieve,

Public Sub GetUCFiles(myUrl As String, OutputFile As String)
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myUrl, False, "username", "password"
WinHttpReq.Send

myUrl = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile OutputFile, 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If
End Sub

Open in new window


I use this to call it

filein = DLookup("Path_Local_Data_Report", "Admin", "ID=5") 
        FileOut = vardirectory & "\Occupation.csv"
        Call GetUCFiles(filein, FileOut)

Public Function vardirectory() As String
If TempVars("OSver") = 5 Then
        vardirectory = Environ("USERPROFILE") & "\My Documents\Downloads"
        End If
            If TempVars("OSver") = 6 Then
            vardirectory = Environ("USERPROFILE") & "\Downloads"
            End If
End Function

Open in new window


Everything works perfect for the first time. If something changes on the remote side, this code wont download a current updated CSV file
unless I restart my Access project.

Anyone had this happen before?

Mark
0
Hi Guys,
I have aspx page that have textbox controller and listbox controller in my page:
<asp:TextBox ID="txtObject" onfocus="this.select();" runat="server" AutoPostBack="True" CssClass="form-control"></asp:TextBox>
<asp:ListBox Style="border: none; min-height: 350px;" Width="100%" ID="lstTracked" runat="server"></asp:ListBox>

Open in new window


Now, when I type some text on the textbox and enter it is inserting the text to the lstbox and so on, but in some point after I enter 3 4 and sometimes 10 entries I'm getting error:

Here is the error:
Source:      System.Web
Description:      Invalid postback or callback argument. Event validation is enabled using in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.
Source:      Void ValidateEvent(System.String, System.String)
Data:      at System.Web.UI.ClientScriptManager.ValidateEvent(String uniqueId, String argument) at System.Web.UI.Control.ValidateEvent(String uniqueID, String eventArgument) at System.Web.UI.WebControls.ListBox.LoadPostData(String postDataKey, NameValueCollection postCollection) at System.Web.UI.WebControls.ListBox.System.Web.UI.IPostBackDataHandler.LoadPostData(String postDataKey, …
0
Hello!

I am writing a VB6 program to read an XML file.  The XML has encoding="UTF-8".

I have everything working with the exception of the embedded attachments.  There is a file name (jpg, doc, etc.) which I can pick up.  My problem is that the data element is just a bunch of characters.  Somehow I need to take the group of characters and convert it to a jpg or doc file.  

Does anybody know if this is possible,  and if so,  how is it done?

Thanks for your help!
0
Hi Experts,

I had this code working fine in a DSN based connection.
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Provider=MSDASQL;DSN=PlacementNP;Initial Catalog=PlacementNP;User Id=MyUser;Password=MyPWD"
objConn.Open

Open in new window


However now that we changed to a DSN-less connection I can't get it to work, tried the following
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "ODBC;DRIVER=sql server;Server=tcp:sqlserver1\exp08;Database=PlacementNP;UId=MyUser;PWD=MyPWD;Trusted_Connection=No"
objConn.Open

Open in new window

And I get the attached error, when all the credentials are still the same.

Any idea what's wrong here?
Untitled.png
0
I have this code which will find matching sets of three numbers (and colour them for ID) across a worksheet. (sample sheet attached 007-quad-ID-query-ee.xls )

Could it be adjusted to find those sets of three numbers which match 5 or more times ?
__________________________
Code :

Option Explicit

Private Type Sets
    strAddr As String
    strCells As String
    lngColor As Long
End Type

Sub IdentifyDuplicates()

    Dim lngLastRow As Long
    Dim lngLastColumn As Long
    Dim lngRow As Long
    Dim lngCol As Long
    Dim DupeSets() As Sets
    Dim strSet As String
    Dim lngFind As Long
    Dim lngFound As Long
    Dim lngColors()
    Dim lngNextColor As Long

    lngColors = Array(13494512, 11599871, 13626575, 15723724, 15258845, 12178907, 8518399, 11461045, 14667418, 14136257, 10074816, 5369343, 9491089, 14071663, 12683685, 13233150, 11596768, 14541491, 15259071, 15654653, 10668797, 7791807, 12504966, 13674644, 13743867, 8759804, 6146693, 10728776, 12552565, 11963641, vbYellow)
    lngNextColor = 0

    ReDim DupeSets(0)
    lngLastRow = Range("A65536").End(xlUp).Row
    lngLastColumn = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column

    For lngRow = 4 To lngLastRow
        If Cells(lngRow, 1) <> "" Then
            For lngCol = 8 To lngLastColumn Step 4
                strSet = Cells(lngRow, lngCol) & "," & Cells(lngRow, lngCol + 1) & "," & Cells(lngRow, lngCol + 2)
                …
0
I have this code which sorts groups of 6 numbers into all possible sets of 3 (of which there are 20)

Could someone help and alter it so that it sorts 6 numbers into all possible sets of five please ?

Public Sub Triples()
    intNumbers = 6
    Set wsData = ThisWorkbook.Worksheets("Sheet1")
    For rowCurrent = 4 To wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
        colOutput = 8
        For i1 = 1 To intNumbers - 2
            For i2 = i1 + 1 To intNumbers - 1
                For i3 = i2 + 1 To intNumbers
                    wsData.Cells(rowCurrent, colOutput).Value = wsData.Cells(rowCurrent, i1).Value
                    wsData.Cells(rowCurrent, colOutput + 1).Value = wsData.Cells(rowCurrent, i2).Value
                    wsData.Cells(rowCurrent, colOutput + 2).Value = wsData.Cells(rowCurrent, i3).Value
                    wsData.Cells(rowCurrent, colOutput + 3).Value = ""
                    colOutput = colOutput + 4
                Next
            Next
        Next
    Next
End Sub
0
Hi. I am using the Bloomberg COM v3 Data Control code in their RefDataExample.xls to retrieve BID and LAST_PRICE_TIME_TODAY for a list of bond ISINs. I need the data to be returned in the same row as the ISIN for which the request has been made. However, if a bad ISIN is in the list the results seem to be returned in a fairly random way. If I then do nothing else but rerun the macro the order of the returned data changes. I am not an expert. Anyone got any bright ideas? Thank you very much.
The problem is best seen by using simple numbers for the ISIN input column of 1 to, say, 100 and seeing how the output is returned to the right of the input column.
This has been answered by Bberg now. Thanks
0
Creating Instructional Tutorials  
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

So, I have to look through a worksheet and identify where groups of numbers match.

Could someone help me with code which will do this for me please ?

Also, would it be possible to identify each matching group of number with their own colour ?

I have attached a worksheet to illustrate


thanks
006-triplet-duplicate-id-EE.xls
0
Hello,

How do you modify Excel VBA macro code (containing a keyboard timestamp shortcut) so that, each time the macro is invoked, it reproduces the actual shortcut keystrokes (and therefore the current time) rather than the "absolute" time value the shortcut produced when the macro was originally recorded?

After clicking to Use Relative References (ie Developer > Use Relative References), the Excel macro recorder was used and created the following code:

Sub Macro12()
'
' Macro12 Macro
'

'
    Application.Goto Reference:="DateEnd"
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "11:59:00 AM"
    ActiveCell.Offset(0, 3).Range("A1").Select
End Sub

Open in new window

The relative actions (keystrokes) appear to have been recorded properly except for the following row:
       
ActiveCell.FormulaR1C1 = "11:59:00 AM"

Open in new window

At this particular part of the recording, the shortcut [Ctrl+Shift+;] was used to enter the current time. As is shown above, the time when the macro was being recorded was apparently 11:59 AM. However now, each time the macro is run, it does not enter the current time as it should but enters 11:59 AM.

So how must that row of code be modified so that each time the macro is run in the future, it will reproduce the keystrokes shortcut and post the current time then instead of always entering 11:59 AM?

By the way, I …
0
After this code runs and adds a record to the recordset, I am unable to add any more entries into the record. I believe the record is getting locked by the database since it detected changes, but I'm not sure how to remove that through access. I have verified this code works properly besides the unintended side effect of being unable to repeatedly do it. Does anyone know how to unlock the record so everyone can access it after this runs?

This is a copy of the entire code segment.
2017_06_20_11_16_43_Microsoft_Visual.png
This is the error message received after it SUCCESSFULLY updates the record the first time.
2017_06_19_12_44_48_Microsoft_Visual.png
This is the debug line that reports the database error.
2017_06_19_12_48_50_Microsoft_Visual.png
Things I've tried to get it to work.
-Adding a ClientRS.Edit before the arguments.
-Removing the ClientRS.Close

Things to note
-This is a seperate form that adds a phone number to a table of phone numbers with a Company ID, Company Name, etc.
-The data is managed by an SQL database that Access links to.
0
I have an application running in classic ASP coding with HTML pages and in one .asp page the client requested for a button. This button when clicked, it must export whatever the data present in Table of Access database (already in use) to an excel sheet. Kindly help as i have been searching for this sort of code everywhere.
0
After this code runs and adds a record to the recordset, I am unable to add any more entries into the record. I believe the record is getting locked by the database since it detected changes, but I'm not sure how to remove that through access. I have verified this code works properly besides the unintended side effect of being unable to repeatedly do it. Does anyone know how to unlock the record so everyone can access it after this runs?

    Set ClientRS = CurrentDb.OpenRecordset("Client Contacts", dbOpenDynaset, dbSeeChanges)
   
    With ClientRS
        .AddNew
        ![id] = ClientID
        ![Full Name] = FullName
        ![Phone Number] = PhoneNumber.Value
        ![Company] = "Other"
        .Update
        .Close
    End With

    Set ClientRS = Nothing
   
    DoCmd.Close acForm, "Add Phone Record", acSavePrompt
End Sub
0
Hi Experts,

I'm getting the attached error on the following line
    Dim mrstZipCodes As ADODB.Recordset
    Set mrstZipCodes = CurrentProject.Connection.Execute("SELECT * FROM tblZipCodes WHERE ZipCode = '" & strZipSearch & "'")

Open in new window

This is something that previously worked well, and in fact its still working in others pc's, however its stopped working at one pc, any idea how to make that work again?

I compared the references with the other working pc and both have the same, see attached(2).

PS. This pc has A2007 and we tried installing the runtime of a later version and then we un-installed it.

Thanks in advance
Untitled.png
Untitled.png
0
Hello,

I'm aware that various parts of an Excel window (eg gridlines, headings, Formula bar, etc) can be hidden using the View menu and other parts (sheet tabs, scrollbars, etc) can be hidden by going to: File > Options > Advanced.

Recently I came across a thread in Mr. Excel which includes a post (by none other than prolific EE expert Rory Archibald) showing VBA code for how to display the Status Bar in Excel:

application.DisplayStatusBar = True

Open in new window

I actually wanted to hide the Status Bar so I inserted:

application.DisplayStatusBar = False

Open in new window

which worked.

Now I'm wondering what other parts of an Excel (or other app) window can also be hidden using VBA. For example, is it possible to hide the Title bar, Ribbon and/or Quick Access Toolbar in a similar way? If so, can someone post the code?

Thanks
0
I have rows of data, from which I need to sort into every possible combination of three (as per worksheet)

I think there are 18 or 20 possible combinations altogether, I have done half a dozen for illustration.

Could someone help me with code that will sort thousands of rows into this format please ?


thanks

006-triplet-studies-EE-sample.xls
0
Hi All,

Have a problem and need help here. i am using vb6 and now face 2 problem .
1. Need to split the string into variable from CSV file but the data may contain comma in quote, which is -
 
            - string = abc,"3,4,5,6","3",Steve,got,"1,2,3",problem
             
             * i want to split into 6 variable to become
               
                aVar = "abc"
                bVar = "3,4,5,6"
                cVar = "3"
                dVar = "Steve"
                eVar = "got"
                fVar = "1,2,3"
                gVar = "problem"

2. i want to sort the string in 2 element, which is sort bVar then follow by fVar.

Anyone able to advice,

thanks in advance.

Regards
Steve
0
Online Training Solution
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

experts,

I inherited an Access database with the following

Its a split database with a Master Table with say 500 records. When we edit a record, theres a form of unbound fields that create filter criteria, and then runs a make table query to Table 1. Then opens Form 1 that is bound to Table 1 and displays just the records to be viewed.

I was given a requirement to lock and disable every control on Form 1, and add a command button to open a duplicate Form 2 that is editable, to make it more difficult for users to accidentally alter data.

The user then makes changes to a record and submits the changes via command button which updates the master table, an audit table, etc. and closes the editable version if the form.

At this point, i want to requery Form 1 to show the updated record, which means i have to run a Make Table query to update Table 1. Of course, at this step, the fails and notifies me that Access cannot lock Table 1 because it is being used.

But, even if i close Forms 1 and 2, essentially all open forms bound to Table 1, it continues to stay locked so i cant update it and i dont understand why.

Anyone know why?
0
Hi guys,

I'm trying copy a file from one folder to another but get a permission error, how come? If i do it manually it works just fine.

here is my code:
Function Recurse(sPath As String) As String

    Dim fso As New FileSystemObject
    Dim myFolder As folder
    Dim mySubFolder As folder
    Dim myFile As file

    Set myFolder = fso.GetFolder(sPath)

    For Each mySubFolder In myFolder.SubFolders
        For Each myFile In mySubFolder.Files
            'If myFile.Name = Range("B1").Value Then
            If myFile.Name = "SHB_Addins ver 1.7.dotm" Then
                Debug.Print myFile.Name & " in " & myFile.path 'Or do whatever you want with the file
                Debug.Print mySubFolder.path
                Call fso.CopyFile("S:\Udvikling\Addin\I prod\SHB_Addins ver 1.8.dotm", mySubFolder.path)
                Exit For
            End If
        Next
        Recurse = Recurse(mySubFolder.path)
    Next

End Function

Sub TestR()

    Call Recurse("\\bfd2\b00037\Templates_master\")

End Sub

Open in new window

0
Please note, i am getting an error after Call oUIDoc.FieldSetText("EnterSendTo", stTo).
 Run-time error 2147417851,Automation Error The server threw an exception

Please assist in stTo ie "Const stTo As String = "V/India/IBM"" What should i write instead of this
0
Respected All,

Hope you are all Fine i have recorded a macro with vlookup formula it comes in R1C1 format Please advice how to make Criteria Range Which is (Sheet1!R[-57]C[-5]:R[186]C[17] = B1:S244)  Dynamic so that if some data Added in last row which will be 245 it Will auto include in range.

Sub VlookupExtractRecoveries()

'Code to Apply Vloockup Formula to Extract Figures in Recovery Report
Dim LR As Long
LR = Range("G58").End(xlDown).Row

    Range("G58").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-5],Sheet1!R[-57]C[-5]:R[186]C[17],COLUMNS(Sheet1!R[152]C[-5]:R[152]C[10]),0),0)"
    Range("G58").Select
    Selection.AutoFill Destination:=Range("G58:G" & LR), Type:=xlFillDefault
  Range("G58").Select
 
  'Code to Convert Formula as Peste Special value
 
    Range(Selection, Selection.End(xlDown).Offset(-1)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'Code for Esc Copy Cut or Cancel Sellectoion
        Application.CutCopyMode = False
End Sub
0
Hi I am trying to extract the value from form objects such as Listboxes, comboboxes, textboxes and the like that are on a form.

I have no issue when each is individually named but when I attempt to use an array of say a textbox I run into difficulties.

Two textboxes with name txtfields index 0 and 1 created on form.

Dim I as integerError MessageDim ctl as string

 For i = 0 To 1
     
     If i = 0 Then
         ctl = "txtFields(0)"
       Else
         ctl = "txtFields(1)"
     End If
       
     Debug.Print "Test: ", Me.Controls(ctl).Text
   
    Next
   

I have tried various assignments such as ctl = "txtfields" & ibut still get the same error.

Any assistance would be appreciated.

Thank you.

Edward
0
I want to send sms from vb6 application. I have third party tool. He has given http://mobile.sendsms.com/api/smsapi.aspx?username=abcd&password=abcd&to=MOBILENO&from=fromhdr&message=this is test sms


following command i have written in vb6 code but it is not working.

TextString= "http://mobile.sendsms.com/api/smsapi.aspx?username=abcd&password=abcd&to=MOBILENO&from=fromhdr&message=this is test sms"
   
    Open "c:\SmsSend1.Bat" For Output As #1
    Print #1, TextString
    Close #1
    Shell "C:\SmsSend1.Bat", vbHide



in vb6 how to execute this command in vb6. I want to execute this command after saving data record.


Thanks
0

Visual Basic Classic

163K

Solutions

43

Articles & Videos

58K

Contributors

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.