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

x

VB Script

36K

Solutions

20K

Contributors

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.

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

Sign up to Post

Hi Experts
i have an application no working with all features except i run it as administrator however the user is a domain user
i need batch file to run this app as administrator without give the domain user admin password
0
Technology Partners: We Want Your Opinion!
Technology Partners: 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!

Count the number each item appears in a selected column and produce those values in a new tab.

Column A of New Tab = Original Value (text or number)
Column B of New Tab = Number of Times Value Appeared

Assistance is greatly appreciated.  A sheet might contain as many as 15,000 rows.
0
How to append the mail notification inside the VB script
0
I am trying to get the function of Application.SumIf() and Application.CountIf() for an internal VBA array defined as Variant.

It is fairly easy to achieve if the criteria Crit in the (non-working) Application.CountIf(tmpArr, Crit) is just supposed to be Equal to the values in the array, but I need Crit to be able to contain criterias like >10, <5 and ideally even wildcards.

Below is a code snippet showing what I have tried for CountIf() , the error it produces and also how I hoped it would work.

Sub CountIfTest()
Dim tmpArr As Variant, Crit As String, tmp As Variant, i As Long
    Crit = InputBox("Criteria", , "<6")
    tmpArr = Array(5, 3, 12, 4, 0, 4, 3, 2, 1)
        
'    MsgBox Application.CountIf(tmpArr, Crit) 'Gives error "Object required" since tmpArr is not a Worksheet.Range()
    For Each tmp In tmpArr
        If tmp Like Crit Then i = i + 1         '"Like" doesn't work, but "If tmp = Crit" works
    Next tmp
    MsgBox "Like test " & i
    MsgBox "Filter test " & UBound(Filter(tmpArr, Crit, True, 0)) + 1                   'Works for Equal, but not for < or > or wildcards
    MsgBox "Match test " & Application.Count(Application.Match(tmpArr, Array(Crit), 0)) 'Works for Equal, but not for < or > or wildcards
End Sub

Open in new window


Any creative ideas?

Thanks,
Jörgen
1
Hi!

I have created 7 templates that I would like to write a Macro for that opens them all in succession for me to manually insert attachments and send.  After trawling a few forums I have cobbled this together however I am now struggling to see what I am missing in order to make this work once assigned to a toolbar button:

Dim template As String

Sub OpenTemplate1()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template1.oft"
MakeItem
End Sub

Sub OpenTemplate2()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template2.oft"
MakeItem
End Sub

Sub OpenTemplate3()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template3.oft"
MakeItem
End Sub

Sub OpenTemplate4()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template4.oft"
MakeItem
End Sub

Sub OpenTemplate5()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template5.oft"
MakeItem
End Sub

Sub OpenTemplate6()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template6.oft"
MakeItem
End Sub

Sub OpenTemplate7()
template = "C:\Users\COsbourn\AppData\Roaming\Microsoft\Templates\Template7.oft"
MakeItem
End Sub

Private Sub MakeItem()
Set newItem = Application.CreateItemFromTemplate(template)
newItem.Display
Set newItem = Nothing
End Sub
0
Hi, I have a couple of macros which needs to be run automatically every day. However after opening the sheet I need to move the tab and press CNTRL+D to download the file. HOw can do that automations. Please advise

Thanks
Venkatesh.
0
Is it possible to insert image (location map ) in the sheet using VBA if I save all the respective images in a folder with Project code as name.

Say for example if I have DS101.jpg ,DS102.jpg (Project Code column in datasheet) saved in a folder. When I run the macro it fetches the respective image for each project and insert at a specified range (Location) in the template.

Please find the attached excel
CreateMultipleReport_v2a.xlsm
DS101.JPG
0
Hi

I have 600 user accounts (within  OU14, OU15 ,OU16, OU17) that i have extracted in a notepad. I need to delete all these user accounts from our active directory which are not required any more. Is there any script or batch file or command that i can use to delete all these user accounts.
If so please post me the syntax to safetly delete them from the AD.

My Ad structure is like this :
Domain.local
       -Students
              -OU14
              -OU15
              -OU16
              -OU17

Thanks and any help would be great.
0
Hi,
I have a datasheet and a template. I need to create around 30 worksheets(in the same workbook) using the data . When I run the macro with 7 sets of data it is working fine (execution time: 3 minutes). But it is hanging if I enter more number of data.

Please find the code I am using:

Option Explicit


Sub PTOTemplateFill()


Dim LastRw As Long, Rw As Long, Cnt As Long
Dim dSht As Worksheet, tSht As Worksheet
Dim MakeBooks As Boolean, SavePath As String


Application.ScreenUpdating = False  'speed up macro execution
Application.DisplayAlerts = False   'no alerts, default answers used



Set dSht = Sheets("Datasheet")           'sheet with data on it starting in row2
Set tSht = Sheets("Project Page Template")       'sheet to copy and fill out

'Option to create separate workbooks
    MakeBooks = MsgBox("Create separate workbooks?" & vbLf & vbLf & _
        "YES = template will be copied to separate workbooks." & vbLf & _
        "NO = template will be copied to sheets within this same workbook", _
            vbYesNo + vbQuestion) = vbYes

If MakeBooks Then   'select a folder for the new workbooks
    MsgBox "Please select a destination for the new workbooks"
    Do
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count > 0 Then    'a folder was chosen
                SavePath = .SelectedItems(1) & "\"
                Exit Do
         …
0
Trying to extract data from website into excel.
Site is https://www.btcmarkets.net

I am after the exchange markets data.

I have tried the DATA - from web approach by doesnt allow me to get table.

Can anyone help
0
On Demand Webinar: Networking for the Cloud Era
LVL 10
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Hi,

See Attached:

I'm trying to customize this Excel Checklist Template that I obtained from the following link:

http://www.clearlyandsimply.com/clearly_and_simply/2015/01/microsoft-excel-check-list-compilation.html

File Name: 08_check_list_more_columns.xlsm

I'm trying to add more columns between columns D & E without messing up the Percentages.  When inserting extra columns, then checking off the boxes to mark it as complete in the row, the Percentages on the top don't match up with the column check boxes that were just changed, it pushes them to the right one.

When the document is open you can hit "ALT + F11" to open up the VBA Editor.  I've tried to diagnose where I would change the correct entry so that it doesn't mess everything up when I add the extra columns but I can't find it.  I"m not a coder or understand how they created this so please explain in detail.

Please HELP!!!!

Thanks
08_check_list_more_columns.xlsm
0
Well I need a similar Macro to make a Pivot as before but not as complex. I need a Pivot to give me YTD totals.

Report Filter = Task Type (which incident is selected)
Columns = Month
Row Labels = Assigned To
Values = numbers

sample
0
I need a MACRO to create a pivot Table and export that to a PDF or show as image in another tab then export it.

Worksheet name = 8 Week INC Trend
Report Filter I need , "8 Week Trend", "Task Type"
Column Labels I need "Week Ending"
Row Labels I need "Assigned to"
Values I need "number"

NOTE I do have a name range for my data TAB called "RANGE"
0
I need a MACRO to export the active sheet to a PDF file.
0
At C35 I have this drop down with names and when selecting the name it will email out the form...that works great and what I wanted to do is duplicate that at B35 but I'm unable to do so.  The Code is there at module 2 for email so I need it to work for the "Email Supervisor" highlighted in yellow.

See attachment.
C--Users-lfreund-Desktop-TIMESHEET-.xlsm
0
Hi experts, I have a database with the following datas;

code     description     Qty     Date
1175     Para                  1        9\12\2017
1188     Amox                3        9\12\2017
1175     Para                  2        9\12\2017
1188    Amox                 2        9\12\2017
1199    Cefa                    1       9\12\2017

We can see here, two Para and two amox. I want them to merge or add its quantity that would result with the follwing view;

code     description     Qty     Date
1175     Para                  3        9\12\2017
1188     Amox                5        9\12\2017
1199    Cefa                    1       9\12\2017

How to set a condition to achieve the goal? I have a solution but this is not working for this purpose.

Set rs = cn.Execute("Select code, description, quantity  from SoldItems")
0
I want to convert file from .xlsx to xlsb. Which  xlFileFormat I need to give in below code ?    $ws.SaveAs($file + '_' + $ws.Name, [Microsoft.Office.Interop.Excel.xlFileFormat]::xlCSVWindows)
0
HI,
We need a solution to retrieve filenames from sub folders in macro for a comparison analysis.
Dir\SubDir\Filename
option to select the folders for comparison from D:\ and E:\
We need to break the path in the rows
Match folder/subfolder/filename in D: with folder/subfolder/filename in E:
reflect matched and not matched filenames

Thanks!
CPH
0
Hi People,

I’d like to request some PowerShell scripting help for testing document download and access that have been published by my users to the cloud service.

Steps to do:
1.      Loop through the month directory and then get all PDF published in that directory, then save it to C:\Test\Result\
2.      Go to the next month and then perform the same thing as above

URL:
January 2017
https://CompanyName.s3.amazonaws.com/Uploads/Finance/201701/20170101-USR-Report.pdf
https://CompanyName.s3.amazonaws.com/Uploads/Finance/201701/20170102-USR-Report.pdf 

https://CompanyName.s3.amazonaws.com/Uploads/Finance/201701/20170131-USR-Report.pdf 

Open in new window


 …

August 2017
https://CompanyName.s3.amazonaws.com/Uploads/Finance/201708/20170801-USR-Report.pdf 
https://CompanyName.s3.amazonaws.com/Uploads/Finance/201708/20170802-USR-Report.pdf  

https://CompanyName.s3.amazonaws.com/Uploads/Finance/201708/20170831-USR-Report.pdf 

Open in new window


I wanted to test if the user published the document as per the standard naming convention or not.

Any help would be greatly appreciated.

Thanks,
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.

I need a powershell script that will ping a dns name and if it replies, set a registry value in HKEY_CURRENT_USER to 1 and if its not pingable set the registry value to 0.

I appreciate the help with this?
0
Hi Guys, any idea why this While Wend Loop is only populating Cells (i,44) and not Cells (i,88) in Excel. The If Statements are exactly the same.

Sub Newmacro()

Worksheets("CTMRFCDXTR_1_2_LN").Select

Dim i As Long

i = 1

While Cells(i, 1).Value <> ""

If Cells(i, 4).Value = "MM_BNK_LN" And Cells(i, 21).Value = "EUR EURIBOR 3M" And Cells(i, 18).Value = "GB-RELATIONSHIP LOAN" Then
Cells(i, 44).Value = "-0.332"
i = i + 1
ElseIf Cells(i, 4).Value = "MM_BNK_LN" And Cells(i, 21).Value = "EUR EURIBOR 3M" And Cells(i, 18).Value = "GB-RELATIONSHIP LOAN" Then
Cells(i, 81).Value = "-0.332"

i = i + 1


Else

i = i + 1


End If
0
Hello,

I have a script that generates a PDF.  Everything works, except I don't want the PDF to open.  There's a constant called openAfterExport but I keep getting errors when using it.  Here's my code.  Thanks.


Sub CommandButtonIntro_Click()

    'On Error Resume Next

JobYear = cstr(Year(Item.UserProperties("JobInitiatedDate")))

    Set objDoc = GetWordDocAll("\\TGPS13VM1\drawing$\Jobs\Task_Templates\Letter-Project Introduction - 2"+CDNTemplate+".dot")
    Call FillFieldsIntro(objDoc)
    objDoc.Application.ActiveDocument.AttachedTemplate = "Normal"'objDoc.Application.Options.PrintBackground = True
objDoc.application.Windows(1).WindowState = wdWindowStateMaximize

If MsgBox("Make your changes to the Word doc first, then click Yes to save.", _
   vbQuestion + vbYesNo) = vbYes Then
      objDoc.Application.ActiveDocument.SaveAs "\\TGPS13VM1\drawing$\Jobs-"+JobYear+"\"+Item.UserProperties("JobNumber")+" "+Item.UserProperties("JobName")+"\"+Item.UserProperties("JobNumber")+" Intro"
End If

objDoc.ExportAsFixedFormat "\\TGPS13VM1\drawing$\Jobs-"+JobYear+"\"+Item.UserProperties("JobNumber")+" "+Item.UserProperties("JobName")+"\"+Item.UserProperties("JobNumber")+" Intro.pdf",wdExportFormatPDF,true,wdExportOptimizeForPrint,wdExportAllDocument,OpenAfterExport

    'Call RestoreWordIntro
objDoc.Close wdDoNotSaveChanges
'objWord.Quit
          Set objDoc = Nothing
      Set objWord = Nothing

Call CommandButtonIntroCover


End Sub
0
Please provide me with the correct syntax for granting a user full access to another user's Office 365 calendar by using the Office 365 Power Shell.

For example this command grants a single user full access to everyone's calendar within Office 365.

How can this power shell command be modified to grant a single user access to another user's Office 365 calendar?

Get-Mailbox | ForEach-Object {Add-MailboxFolderPermission $_":\Calendar" -User msmith@company.com -AccessRights Owner}

Open in new window

0
I am trying to write to a word document and I can seems to find the right reference in the COM tab to define the Interop Namespace. It is giving me an error on the "Word.Application" and the "Word.Document" calls. I tried to locate the referenced Please add the Microsoft.Office.Interop.Word assembly to your project. Go to Project -> Add Reference. But I can;t find it????

Imports Microsoft.Office.Interop
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim MSWord As New Word.Application
        Dim Doc As Word.Document = MSWord.Documents.Add

        Doc.Bookmarks("\endofdoc").Range.InsertParagraphAfter()


        Doc.Range.Text = "This is my text.  "

        ' store the end of the current range 
        ' Note that the value of Range.End will be 1 character past the end of the text string
        Dim startbold As Int32 = Doc.Range.End - 1

        Doc.Range.InsertAfter(" This is my bold text.  ")
        ' Store the end position of the string
        Dim endbold As Int32 = Doc.Range.End - 1

        ' add some for text just to demonstrate that setting the bold property later
        ' is dependent on character position.  As long as something is not added prior to our
        ' text (thereby changing the position of the text in the document), the  bold property
        ' can be assigned at any time.

        

Open in new window

0
An online reviewer downloads tens of thousands of video and music files and it is a real mess with no organization. Additionally they have some on their laptops, others on their PCs and then some on several servers, with a few on MACs. Fortunately the MACs are few in number so I can manually move all those files but for the rest I would like to be able to take all the files, use their file names and create folders with the name and then move all the files with matching names into the matching folder. Looking at the video files (most of which are TV shows) they have a fairly consistent naming convention which I hope will make automating this process possible. Generally speaking the name of the video files are "name of the TV show then season number then episode number", example "Americas Got Talent S02E07". So would it be possible to take all of the name but not the S02E07 and create a folder "Americas Got Talent" and then move all the files that match that name into the newly created folder. The MP3's (mostly music but some are podcasts) do not have this "consistent" naming convention so not sure what to do here -- but if I can get the videos organized I think switching them to Media Monkey for future podcasts and music should provide the organization needed. Thanks!
0

VB Script

36K

Solutions

20K

Contributors

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.