Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Visual Basic Classic

163K

Solutions

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

This may be an obvious question to many, but I've tried multiple iterations and much internet searching and can't get this to work.  I basically want to ask the user to input a value and then use that value in a table with filters to filter the data using the input word.  Would appreciate any help.  My current attempt below.

Thanks

Dim newVal

newVal = InputBox("Enter the search term")

If newVal = "" Then Exit Sub

Sheets("Question Database").Select
    ActiveSheet.ListObjects("RFPQs").Range.AutoFilter Field:=1, Criteria1:= _
        " & newVal & ", Operator:=xlAnd

Open in new window

0
How to Use the Help Bell
LVL 10
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

I need to return a single character from a text string following "<Gender>", how can I do that? Thank you
0
Access 2013 and ASP Classic
New to Access, I have the database and an excel file on my server in the same folder.  I created a query that links to the excel sheet.  I then have a web page that allows users to sort / filter / search for products on the query, which is linked to the excel sheet.  It appears if I open the excel, it locks others from using the webpage.  You can see the webpage with the error I included in the title.  It was my understanding, that while I work on the excel sheet, it created a locked file for me, so that the webpage could still function?  The folder that has the database, is read/write permissions for a group of users. (100).
Here is my connection strings:
dim rsFieldOneObject
dim connectionStringToAccess
Set rsFieldOneObject = Server.CreateObject("ADODB.Recordset") 
Set connectionStringToAccess = Server.CreateObject ("ADODB.Connection")
connectionStringToAccess.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & server.mappath("db/matrix-1.accdb")       	

Open in new window

This is where it first tries to pull data:
                 dim objRs
		Set objRs = Server.CreateObject("ADODB.Recordset")
		dim sicSQL
		sicSQL= "Select distinct(SIC), SIC_Description from MaxtixTable2"
		objRs.Open sicSQL, connectionStringToAccess
		Response.Write "<select name='sicInput' required>"
		response.write " <option value='' selected> SIC Choice... </option>"

Open in new window

Then it loops thru the data to create a drop down of choices.

I am using the linked excel so a team of four can keep it updated with no experience in ... anything computers.
I hope to create a form for them to use in the future, but deadline for project is too close to do that right now.

Do I have to tell them not to do any updates to excel during normal operation hours?  or should the excel being opened create a locked version so that the users can still use the webpage?

Thanks
0
I have a code sequence that scrolls through my outlook contacts and updates all that have been edited or added since the last update was run

It doe this by saving the last uodate and only editing contacts that have a creation date or a lost modification date after the saved date.

The process takes some time (6,000 + contacts)

Is there a way to "filter" the contacts folder and only scroll through those that have a creation date or a lost modification date after the saved date
0
Items:

Two Computer with each program with textbox

PC1:
I have project with Textbox and Button

PC2:
Also have project with textbox only

Problem:
How can I send the text in the textbox from PC1 to PC2?

Any help please, Thanks
0
How can I connect SQL to my Form (datagridview1)

I am new here in VB.net I don't have any idea but I am willing to learn, I do coding in VB but not using SQL and I only use text file for my database (txt, dll, bat)

this is my prev. project here and they helped me a lot, now I am trying to connect SQL, please help and TIA

Listview and Datagridview answered question here in Expert Exchange - this is my prev post

Details:

SQL Connection
:IP Address 192.168.6.252
SQLMy Table
Table
Datagridview1:
DGV
0
Hi Experts,

I have a code that should be changing the SQL of a pass-through query, and I realized suddenly that each time the code executes, new tables are being created in my app..

See attached.

Below is the code.

Private Sub ComboFindByRef_Change()
    Dim qd As QueryDef, s As String
    On Error Resume Next
    If Me.ActiveControl.Name = "ComboFindByRef" Then
        s = ComboFindByRef.Text
    Else
        s = ComboFindByRef.Column(1)
    End If
    Set qd = CurrentDb.QueryDefs("EmployeesSearchPTQry")
    'qd.sql = "select * from [dbo].[EmployeestblSelectBy" & "Ref" & "PH] ('" & Me.ComboFindByRef.Text & "','" & Nz(ComboFindByRefField, "ContactPerson") & "')"
    qd.sql = "exec [dbo].[PH_EmployeestblSelectByRef] '" & s & "','" & Nz(ComboFindByRefField, "ContactPerson") & "'"
   qd.Connect = "ODBC;driver={SQL Server};server=...;"

    Me.ComboFindByRef.RowSource = "EmployeesSearchPTQry"
    
    SendKeys "{F4}"
End Sub

Open in new window


Any idea whats going on??!

Thanks in advance
Untitled.png
Untitled1.png
0
Hi.

I need your help on a macro.

I have an MSHFlexgrid1 that i need to populate based on 3 different criteria.

No1: Start date
No2 How many Weeks
No3: Days requested

Let's take below picture as an example:
I have the start date of : 28-08-2017
I also have the number of week set to 10
And i also have the "Tuesday" day checked
grid_sample
What i would expect to see in the MSHFlexgrid1 is to add 10 rows where i would see the following 10 Tuesday dates starting on the Starting date of 28-08-2017 .

That would be:
29-08-2017
05-09-2017
12-09-2017
13-09-2017
19-09-2017
26-09-2017
03-10-2017
10-10-2017
17-10-2017
24-10-2017

I could check multiple different days also. Based on the days i check, it would add the the dates in the MSHFlexgrid1 in to LaneStartDateTime column.

How can i do that?

Thank you for your help.
date_conversion.zip
0
Hi experts, I'm having difficulties in connecting database with sql 8 to a network or other pc. Codes below is working locally but when i try to run it via network an error occur,  says; " Server does not exist" . Im using OS Widows 10. Thanks!

Sub eMiles(edate, db As String, rmrk As String)
        Dim e As Save_Files
        Set e = New Save_Files
        e.OPEN_CON db, "SkyJob" & "\SQLEXPRESS"
            e.RecordHistory edate, rmrk
        e.CLOSE_CON
end sub

Open in new window


Public Sub OPEN_CON(db As String, ServerName As String)
Set cn = New ADODB.Connection
cn.Provider = "sqloledb"
cn.ConnectionTimeout = 12
cn.Properties("Data source").Value = Trim(ServerName)
cn.Properties("Initial Catalog").Value = Trim(db)
cn.Properties("User ID").Value = "sa"
cn.Properties("Password").Value = "100"
cn.CursorLocation = adUseClient
cn.Open , "sa", ""

If Err.Number <> 0 Then
    MsgBox (Err.Number & "|" & Err.Description)
End If

End Sub

Open in new window

0
Hi,

I would like to be able to divide a cost per any amount so that the total once divided equals the cost not divided.

Ex:
If the Cost = 357.25$
If the divided value = 3
If we do 357.25 / 3 = 119.083333333333

So i cannot say that the value would be 119.08 per value cause 119.08*3 = 357.24.
So it would have to put 119.09 on the first and 119.08 on the 2 last value for a total of 357.25.

How can i do that?

Before the division:
Before
After
After
Thanks for your help
Cost-devided.zip
0
What does it mean to be "Always On"?
LVL 4
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Hello guys,

I need help with the creation of a Macro that is doing my head...

Let me try to resume so it is super clear for everyone :

SHEET 1 = A simple food plan with Name / client Code / Menu Type and Allergies (then food below). This sheet is a individual and set by client.

SHEET 2 = An order form with the list of clients (order form contains names / client code / Menu type and allergies). This sheet is a whole list of client.

WHAT I WANT = A Macro that would generate the following report

- The macro would open Sheet 3 - Copy the whole format from sheet 1 - paste it in sheet 3 - goes to sheet 2 and copy the data (into the form pasted in sheet 3 from sheet 1) with the following information -> SHEET 2 -> Copy C6 paste it in SHEET 3 cell D4. / SHEET 2 -> Copy F6 and paste it in SHEET cell A1 / SHEET 2 Copy B6 and paste in SHEET 3 cell D5 / SHEET 2 copy K6 and paste in SHEET 3 cell B1.

- When this is done it would rename SHEET 3 with the code present in SHEET 3 Cell B1.

NEXT :

- THe macro would finally repeat as many new sheet with the above process as there are clients listed in SHEET 2 (if there is 5 clients - 5 new sheets would be created, if 20 clients then 20 new sheets). Finally each new sheet would be renamed as per cell B1 in that new sheet.


Thanks a lot guys that would help A LOT.
0
I have a script that will create folders for students with individual data folders contained therein.  For example, when I run the script for folder 2025 it will create sub folders labeled for each user (IE: “Lskywalker”, “Hsolo”, etc.)  Users have access to their folder to save data.

The problem I am facing is when I run my script to create all the new users folders it gives the student user “Full” rights to the folder, which we don’t want them to have; just “Read / Write” (to prevent them from changing permissions, etc).  I can make the change on each folder manually but that is extremely time consuming. I’ve found that I can use the “cacls” command to change the permissions, but not sure how to combine this into my current script.

Any help would be greatly appreciated.
0
Been using GPO preferences for shortcuts and they are working well...except for one I need to open a UNC path

Windows 2008r2 domain
Windows 10 laptops

Here's the preference setup
UNCPRINTERS.jpg
0
Folks I keep getting an error on this code at:
.lstCategory.ListIndex = 0

Open in new window

in the below subroutine when the workbook opens and I haven't figured out what is causing it?
Private Sub Workbook_Open()
Dim intIndex As Integer

With Worksheets("Main Menu")
    For intIndex = 10 To .UsedRange.Rows.Count
        .Cells(intIndex, 1).Value = ""
        Next
        .Activate
        .lstCategory.ListIndex = 0
End With

Open in new window

Error opening workbook
0
Hi experts, I have a problem obtaining data in sqlserver in which in my sql command I need to advance 1 day of my time end before able to obtain the data.  My data field in sql is look like this, "2017-08-17 10:00:00.000"  this means that it has a time and this is August 17, 2017. In my sql command I cannot obtain this record if i set the time end to "08/17/2017" but  "08/18/2017" meaning i need to advance 1 day. Is there anyone who can explain me why. What shall i do to make my sql command set "08/17/2017"?

Set rs = cn.Execute("Select Proname, TheDate,  from sTable where TheDate Between '" & "08/17/2017" & "' And '" & "08/17/2017") & ") 

Open in new window

0
Hi All,

Greetings....

According to my work, pdf files will be get merged in a specific path. I need to give dynamic file names to the mergedOutPut File.
1. Is there any way to pass parameters to the file name?
2.Or I can give fixed file name to the mergedOutput file through my code.After that Can I  rename it into dynamic naming conversion?

I saw RENAME command in Foxpro. But I am not sure whether I can pass parameters to it

Any help regarding this would be highly appreciated.

Thanks in Advance.
0
I'm working on a macro that inserts a style separator before the first period in every heading level 2 paragraph. It is also set up to skip any paragraph that is not heading 2. Currently it only works as many times as you specifically trigger it, but I would like it to continue looping until the last style separator is inserted in the last heading 2 paragraph. I can't figure out how to loop this without creating an infinite loop. I'm new to the macro writing game so please excuse my code:

      
 If Selection.Style = "Heading 2" Then
            Selection.MoveUntil Cset:=".", Count:=100
            Application.Run MacroName:="LWmacros.basHNum.InsertStyleSeparator"
            ActiveWindow.Selection.GoTo wdGoToHeading, wdGoToNext

ElseIf Selection.Style <> "Heading 2" Then
             ActiveWindow.Selection.GoTo wdGoToHeading, wdGoToNext

End If 

Open in new window



 I'm not sure what sort of condition I would need to set for the Do Loop for it to end after the last heading level 2 paragraph is found.
0
Hi,  When I enter something new or change something in Cell B1, I would like Cell C1 to be shaded yellow.   I tried this with Conditional Formatting, but I believe that you need to have a specific value in order for conditional formatting to trigger the change.  I want this shading in C1 to be triggered if anything is added or changed in B1.   In other words, B1 could be blank or already populated, so if there is any change entered to that cell, I need C1 to alert me by shading it yellow.  I then need to copy it to subsequent rows.  

I hope my request makes sense and is clear.

Thank you!
0
MS-Access 2016 automatically colors rows in their reports.  These colors alternate by ROW.  I want to alternate the colors by GROUP!  I grouped my report by Invoice (Invoice1, Invoice2,....Invoice100) and want to alternate the colors by Invoice , to include their "Subs"

Example:
Invoice1
    Sub1
    Sub2
Invoice2
    Sub1
Invoice3
    Sub1
    Sub2
    Sub3
Invoice4
    Sub1
    Sub2

Invoice1 AND Sub1, Sub2 would be one color (lets say grey)
Invoice2 AND Sub1, would be alternate color (lets say white)
Invoice3 AND Sub1, Sub2, Sub3 would be Same color as Invoice1 (grey)
Invoice4 AND Sub1, Sub2, would be Same color as Invoice2 (white)

Can any one help?
0
Free Tool: Path Explorer
LVL 10
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Good Morning,

Yesterday I posted a question about saving file locations within Word VBA, I was very kindly pointed in the right direction SaveSetting \ GetSetting however when I tried the suggested code it didn't work and my knowledge is limited - so I am not even sure if it possible hence posting again

I want to use GetSetting to capture the ActiveDocument.name, ActiveDocument.Path and then pull that info back into a variable so I can use it at a later date but I get "Run Time Error 5 - Invalid Procedure Call or Argument" at the line that read StrPath = GetSetting("CMS", "File Locations", strFileName, "Not found")

The code with Comments at bottom of post is what I am trying to achieve, the code is totally wrong but might help

Sub TestSave2()
'
' TestSave2 Macro
'
'
Dim StrPath As Variant
SaveSetting "CMS", "File Locations", ActiveDocument.name, ActiveDocument.Path
StrPath = GetSetting("CMS", "File Locations", strFileName, "Not found")
ChangeFileOpenDirectory _
"File Locations"
            ActiveDocument.SaveAs2 FileName:= _
        "CMS", FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
End Sub

This code with Comments is what I am trying to achieve, the code is totally wrong but might help

Dim DocName As Variant
Dim File_Locations As Variant
SaveSetting "CMS", ActiveDocument.name ' I want to Save the Document Name
SaveSetting "File_Locations", ActiveDocument.Path ' I want to Save the FileLocation
0
I have two access database with same table and same columns. I wanted to copy records from one database to another one. I was trying the below code in VB6 but it provides "Syntax error in INSERT INTO statement"

I was using ADODB

On Error GoTo er1
Dim dbs As ADODB.Connection
Set dbs = New ADODB.Connection
dbs.Open "Provider=microsoft.jet.oledb.4.0;data source=" & dbServer

dbs.Execute My_query


My_query is  
INSERT INTO empTable(JobID, Date, EmpOut, EmpIn)  SELECT 515 AS JobID, Date,EmpOut, EmpIn FROM empTable IN ('D:\Application\data\Employees2017.mdb')

Could anyone suggest me a solution!
0
Hi experts, I wish to know why still got a message of "Yes" base on the codes below even the row is not exist?  I erased all unpaid from the table, SuppliersLedger  but still got a message of "yes" after removing it. Is there anyone who can give idea what's wrong on the codes? Thank you!

Public Function ObtainTotCreBal(code As String) 
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs = cn.Execute("Select Credit as TotCredit,Debit as TotDebit from SuppliersLedger where Status='" & "Unpaid" & "'")
    With rs
        If rs.BOF = True And _
            rs.EOF = True Then
            MsgBox "No"
        Else
            MsgBox "Yes"
        End If
    End With
Set ObtainTotCreBal = rs
Set rs = Nothing
End Function

Open in new window

0
According to my work,I have a  folder which contains some pdf files.

1.I need to merge files into 2 separate files.(File names ends with ABCRDF & ABCOSD into single merged file and all other pdf files into another merged file.
2.So after merging we will have only 2 pdf files.One which contains file names ends with ABCRDF & ABCOSD.And other merged file with all  files except above 2 files.
3.Merging should be done by last modified time stamp.

I have code which merge all the files present in a folder into a single pdf based last modified time stamp.It wont differentiate files based on file names.It will take all the files present and merge to a single pdf file.I will attach the same here.


Any help regarding this would be greatly appreciated..!!

Sub Main()

    Const DestFile As String = "MergedFile.pdf"
    
    Dim MyPath As String, MyFiles As String
    Dim a As Variant, i As Long, f As String
     
     ' Choose the folder or just replace that part by: MyPath = Range("E3")
    With Application.FileDialog(msoFileDialogFolderPicker)
         '.InitialFileName = "C:\Temp\"
        .AllowMultiSelect = False
        If .Show = False Then Exit Sub
        MyPath = .SelectedItems(1)
        DoEvents
    End With
     
     ' Populate the array a() by PDF file names
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    Set sh = Workbooks.Add.Sheets(1)
    Set Rng = sh.Range("A1") ' or wherever; doesn't matter

    Set fso = 

Open in new window

0
in an old vb6 application,

When you set a breakpoint in the code it looks like this with a maroon colored marker.

q1.PNG
What does the sky blue marker mean? how do you remove it?

q2.PNG
0
Hello,

This is a follow-up to an earlier question posted here:

Combine multiple sorted Excel worksheets to a single summary worksheet which is also sorted

The main question in that thread was:
Is there an Excel formula (or formulas) which will merge data from multiple worksheets into a single summary worksheet and keep the combined rows sorted?

Please refer to the earlier thread for detailed examples and screenshots.

The questions below refer to the solution in the previous thread which is also included here for convenience:

Sub CombineSheets()
    ' loop all sheets and combine
    For Each ws In Sheets
        ws.Select
        ' if 1st sheet, clear first
        If (ws.Name = "Combined") Then
            Range("B4:G4").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
        Else
            ' for other sheets, copy and paste into first one
            Range("B4:G4").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            Sheets("Combined").Select
            
            Range("B4").Select

Open in new window

0

Visual Basic Classic

163K

Solutions

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.