VBASponsored by jamf

Solutions

Content

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.

Splitting up a column using a word

I need to split up a column by certain words and move them to another column. How would I go about doing that?

Currently looks like
Column A
Company CSA UL DATE
Company CSA TUV DATE
Company CSA DATE

Would like the outcome to look like this
Column A B C
Company DATE CSA UL
Company DATE CSA TUV
Company DATE CSA

Any help or pointing me in the right direction would be very much appreciated!

5 Comments

Question by

Cedric D

6h ago

Cannot Run Macro from script

Hello,
I am trying to run the script (see attached file), however after I press "ALT+F11" in Outlook then copy/paste and run my script I'm getting an error "Compile error: invalid outside procedure".
Please tell me what am I doing wrong, sorry I'm very new to run macros via Visual Basic Editor.

Export-Folder-Structure-VBS.txt

3 Comments

An excel sheet is given with some data about students. Another sheet is given in which we have to transfer data from some columns of the sheet-1 based on some conditions.
In sheet-1,marks of 4 students along with subject code, subject/topic name and bonus obtained is given. This data is to be transferred to sheet-2 .Only those subjects from sheet-1 are to be read and transferred to sheet-2 alongwith the marks, bonus and subject code which are having some value(i.e. those subjects which are left blank against a name in sheet-1 need not be mentioned into sheet-2).

The same procedure is to be done for almost 100 students.

sample file is attached below.
TASK-1.xlsx

1 Comment

LVL 1

Question by

Dustin Stanley

20h agoSolved

MS Access How To Get Sum Of Column In a ListBox

I am working on a monthly sales form and it has a Litsbox named "Listbox1" on the form called "frmMonthlySales". I have a text box at the bottom called "txtGrandTotal". In this text box I want the sum of the 8th column of "ListBox1" I have tried several ways but no luck.

[Me].[Listbox1].[Column](8)

Open in new window

[Me]![Listbox1].[Column](8)

Open in new window

I know this code won't give me the sum but I can't get it to respond at all. It just says #Name. I placed the code in the source text box. =[Me].[Listbox1].[Column](8)
I have tried requery it and other methods. I have used the Column method MANY times in the past for combo boxes. I just don't get it.
Thanks for the help.

8 Comments

I am trying to copy a paragraph from an external, unopened document to the activedocument.

I am using the below code however am receiving an error stating that no text is selected.

I'm very new to VB so have just trying to figure this out using similar coding I'm finding on the internet.

Sub ThisDocuemnt()

Dim docA As Document
Dim docB As Document

Set docA = ActiveDocument
Set docB = Documents.Open("Z:\xxxxx.doc")

docB.Bookmarks("Demo").Range.Select
docB.Bookmarks("EndDemo").Range.Select
docB.Bookmarks("EndDemo").Range.Copy
docA.Bookmarks("PasteDemo").Range.Paste

End Sub

Open in new window

Any help on how to identify the selection of text would be great. I've tried using .start and .end as identifiers of where to start the selection and where to end the selection but that isn't working either.

1 Comment

hello brother
i have A . B . C columns
i want the formule Vlookup being copied/excuted in columns B and C only if A contain text ( criteria )
i have 10000 row with 3 colmuns . coping the formul in this big range make the file slowly.
so i want to redure it by VBA code

formula in D columns :Vlookup(a1;database;2;0)
formula in C columns :Vlookup(a1;database;3;0)

7 Comments

Cloud Class® Course: SQL Server Core 2016

LVL 12

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

LVL 24

Question by

Tracy

1d ago

Save Excel File as Password Protected PDF

Hi All,

I'm looking to save an Excel file as a password protected pdf using VBA. I have Adobe Acrobat DC - Standard, we can't use other third party downloads here. I've seen lots of examples with third party tools doing this, but none with Adobe itself.

Does anyone have a snippet of code for this?

Thanks for your help!

1 Comment

I have a folder with multiple workbooks.
Each workbook has multiple, but variable number of worksheets
I would like to loop through each workbook and for each workseheet within delete some superfluous rows UNTIL the first Cell in Column A = "X"

Then, because each workbook's first worksheet is generically named Sheet1 I would like to Rename the workbook whatever is in cell D5
Then save it and move onto the next workbook in the folder.

Thanks

16 Comments

running WORD vba from access. Receiving 4605 error when trying to access the document. The Document opens up OK and I can view the document from Word. Cannot do any comments for inserting text or moving to a bookmark. The document and access script work on other computers. It only fails on the one computer that normally sends out the documents. The Access program used a front and back-end linked files. This script suddenly stopped working 4 days ago. It has been used for 7 years with no problems.

Several other Word file that use the same technique have the same problem on 1 computer.access.txt

Copy of the affected text is attached.access.txt

2 Comments

how to convert data table in the xls file into xml format with VBA macro ?

9 Comments

Hi
I want to enter amount received from certain party against a Bill No. So in the Excel Userform, he should just enter the bill no against which amount is received and the amount. The Userform should apply a vlookup(or some other formula) using Bill no as the key and input the amount received in the data base. Given below is the header of my database.
The database before the "Amount Received" column is already filled in using another Userform.

Bill Number Bill Date Customer Name Area Brand Name Sales Person Bill Amount Amount Received
Bill - 1

11 Comments

MS Access VBA

I am trying to set a variable called strPath to a value from my table. Clearly i am doing something wrong.

Dim rst As Recordset
Dim db As Database
Set db = CurrentDb

Set rst = db.OpenRecordset("Select Path from Table1 where ID = " & "1" & "")

Dim strTable As String
Dim strBaseTable As String
Dim strPath As String

strTable = "Customer_Jobs1"
strBaseTable = "Customer_Jobs"
strPath = rst

3 Comments

Hi team,

I am trying to create a macro where if i insert Country name is cells A1 so the images should generate in B1 (e.g if i type A1=Germany so B1= Germany flag) and i want to do same for many country. I need if i put 20 names at a time in A1:A20 so flag should auto generate to next column. also i need if i delete the country names so flag also should get clear. Can we make 2 button one with "Display" another is "Clear" once i click on click on clear button so flag and names both should clear and once i click on display flag so flag should generate based on value in cell.

I have used formula method an also got successfully (Indirect formula) but the problem in that my .jpg images are getting change to .wmf and images is taking unwanted cell size. Is there any way that my images should not get changed to emf format hence the file size is getting increased as well. also i need that all my flag should be in same size and should not look stretched. I would be great if you help me on all this.

I am totally new to VBA. Could you please help me on that ASAP hence need to show project in my school work to get good marks honestally saying.

5 Comments

Question by

Ajay Krishnan

4d agoSolved

VBA UserForm and array Help needed

Hi All,

Thank you in advance for supporting me on this.

I have created a User Form to get the needed with the help of some other codes retrieved from internet. Please have a look at the codes and user form and advise.

The whole idea is like Double clicking the Headers in the Listbox 1 will select and copy the Header to ListBox 2 where you can adjust the position of the Columns with Up and Down command Buttons. Then Combobox1 will allow us to select the reference Column for Spliting, and Combobox2 will select the starting row and Combobox3 will select the file type.

There is a function in the Code for selecting the Destination Folder ( GetFolder() ) which will be shooting while we press the Split button and will split the sheet into files to the selected folder.

Please see the attached Excel File in the following link for reference and advise how can we meet the needful from the code

www.ajayscv.ml

Thanks in Advance
12-10-15-40-FT.xlsm
frmSplitToCSVs.frm
frmSplitToCSVs.frx

33 Comments

I have been doing VBA for a couple months now at my work updating and making worksheets better but what is needed on this one workbook is way to advanced for me and I really need some help. I have attached an example sheet.

New items print everyday and a worker keys them into the sheet below the yellow line. There are hundreds listed. When an item is ready to go out for service the worker will put a date in column E above the yellow line, as seen in the example as 10-May-2018. Worker then has to cut and paste the items from below the yellow line to above the yellow line under the date but only the ones that are ready to go, not all will bit. What I would like to happen is- When the worker lists a date in column E the sheet locates any rows with that same date but in column K (pick up date) and then cuts and paste the row under the date the worker has listed in column E but it must insert and not paste because the rest of the sheet needs to shift down. Some of the issues are the sheet is ever changing and growing. There are thousands of records above the yellow line and it grows weekly so being specific to row numbers is impossible. Also, in a perfect world, When the items cut and paste below the date listed in E we need them alphabetized by column G but only for that dated section and again row numbers cant be referenced because it is ever changing. I am not sure that part is even possible so if it isn't then just help with the first part would be amazing.
When items …

8 Comments

Free Tool: Subnet Calculator

LVL 12

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

I am attempting to recover a Token from an HTTPS server.
There is a correctly installed Certificaton my PC that when used via a web page returns a Token

Code is in VBA WinhttpRequest

When the code is run it returns an error on the send line

Error 800700b7
"Cannot create file when that file already exists."
The same error is returned regardless of the contents of the strSource xml file.

I cannot understand what file it is trying to create.

<Code>
Dim HttpReq1 As WinHttpRequest

Set HttpReq1 = New WinHttpRequest

HttpReq1.Open "GET", "https://secure.authentxxxxx.com/Authenticator/Token.asmx", False
HttpReq1.setRequestHeader "Content-Type", "application/soap+xml; charset=UTF-8" '

HttpReq1.SetClientCertificate "CURRENT_USER\My\AurtxxxUAT01"
HttpReq1.setRequestHeader "Content-Type", "text/xml; charset=UTF-8"
HttpReq1.setRequestHeader "SOAPAction", "http://www.uk.experian.com/WASP/STS"
HttpReq1.send strSource 'strSource is an XML string with SOAP headers etc.

<Code end>

2 Comments

For years I have been using the Word startup folder to load a macro that provides various functions for my staff such as a print button on a toolbar for different printers. This was originally created in Word v2000 in a .dot template. It is still in a .dot template format, not dotx.

I have since tweaked this to be a custom print button on the Home ribbon and have had it working fine in Word 2010, 2013 and, until now, 2016 and 365.
Once the template is in start up I can install the print macro on the Ribbon for each user using the customise ribbon bar tools in Word.
The button then appears for any document, new or old, that they edit and the various macro functions are available and run fine.
I have the .dot Template in the user's start-up folder and adjusted the security and trust center settings to allow it to run.

In the document part of the template I have recorded my notes and revisions of the macros over the years as this does not (or at least did not) display to the user.

I have just installed two new PCS in the office both with identical software (Windows 10, MS Office 2016 Pro Plus).

On one PC it works as it always has done, i.e. the extra button appears on the Toolbar and the document page is blank and the macros work.

On the other, the button is there but all my development notes appear in the document (Document 1) . I have since had the same issue on a third PC. The macros still work it seems but the users are confused having this …

5 Comments

Asked a similar question recently and realized I need to provide more detail.

I have an Excel Macro Workbook (binary extension) that suddenly has developed 2 issues occasionally (sometimes it runs fine).

#1 Issue - Occasionally there is an error on Line 5 It appears the connection does not open. We get the error: Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x7f8 Thread 0x1174 DBC

#2 Issue - Occasionally there is an error on Line 7 It appears the SQL can not run. It indicates the Worksheet tab can not be found even though it does exist. Note for this error we have no issue with Line 5 (Open Connection). The error that displays is: [Microsoft][ODBC Excel Driver] The microsoft jet database engine could not find the object 'Worksheet Name'. Make sure the object exists... For Issue #2 once we close the error dialog box and THE MACRO GETS DELETED !!!

Notes

The code module has not been changed in years Some days we get no errors/issues

This issue occurs for multiple Excel workbooks run by multiple people that run different VBA code blocks. However 1 of the code blocks is the same across all these macros which is the code snippet below.

The source data has not changed in years in terms of formatting or volume.

All these workbooks are on a Network server.

We are on Windows7/Excel 2010/ODBC Excel Driver (xls xlsx xlsm xlsb) 14.00.7180.5000

1-Set

Open in new window

…

3 Comments

Hello Experts,
I am trying to open a DTS package in Visual Studio 2015. The goal is to convert it to SSIS package. I was able to open the DTS package with error in Integration Services Script Task, where I have VB codes. When tried to edit it, I received "This task does not have a custom editor. Use the properties window to edit properties of this task"
message. Any idea how to edit it? Please try to help. Thank you in advance.

Thank you!

5 Comments

LVL 5

Question by

bfuchs

4d ago

looping thru all table fields and constructing a SQL update string.

Hi Experts,
I need to compare 2 tables with exactly the same structure and update TableA according to what it is on TableB for all records that they are different.
This is the SQL needed.

UPDATE Patient_Progress_Notes INNER JOIN Patient_Progress_Notes1 ON Patient_Progress_Notes.PN_ID = Patient_Progress_Notes1.PN_ID SET Patient_Progress_Notes.Patient_Last_Name = nz([Patient_Progress_Notes1].[Patient_Last_Name]), Patient_Progress_Notes.Patient_DOB = nz([Patient_Progress_Notes1].[Patient_DOB]), Patient_Progress_Notes.[Date] = nz([Patient_Progress_Notes1].[Date]), Patient_Progress_Notes.Nurse_Name_Stamp_PN = nz([Patient_Progress_Notes1].[Nurse_Name_Stamp_PN])
WHERE (((nz([Patient_Progress_Notes].[Patient_Last_Name]))<>nz([Patient_Progress_Notes1].[Patient_Last_Name]))) OR (((nz([Patient_Progress_Notes].[Patient_DOB]))<>nz([Patient_Progress_Notes1].[Patient_DOB]))) OR (((nz([Patient_Progress_Notes].[Date]))<>nz([Patient_Progress_Notes1].[Date]))) OR (((nz([Patient_Progress_Notes].[Nurse_Name_Stamp_PN]))<>nz([Patient_Progress_Notes1].[Nurse_Name_Stamp_PN])));

Open in new window

However since these tables have over 85 fields..(each).

The above contains the SQL needed for the first four fields.

Can someone help me build this SQL for the rest of the fields, via code by looping thru all fields on the table and adding it to the string?

Thanks in advance.

21 Comments

I have been trying to follow the videos and extract data from an HTML file and I was able to do so on my system. BUT when I try to use the same code/module to extract data from the html file from my Friend's system it shows me an "error 91: object variable or with block not set" at line 2 and sometime even at line 1. Basically it is not reading the html document on a different system. The HTMLButton1 when tried in debug.print show nothing

Dim file As String
Dim IE As New SHDocVw.InternetExplorerMedium
Dim HTMLDoc As MSHTML.HTMLDocument
Dim HTMLButton1


file = "C:\temp\HTML\page1.html"

IE.Visible = False
IE.navigate file 'line 1
Do While IE.Busy = True
Loop

Set HTMLDoc = IE.Document
Set HTMLButton1 = HTMLDoc.elementFromPoint(338, 462)


array_objects(index).date = HTMLButton1.innerText 'line2


IE.Quit
Set IE = Nothing

Open in new window

1 Comment

First
Prev
1
2
3
4
5
6
7
Next
Last

VBASponsored by jamf

Solutions

Content

Contributors

VBASponsored by jamf

Related Topics

VBASponsored by jamf

Related Topics