VBASponsored by jamf

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

Nothing to do with sheet1
delete the last cell data of each row of Sheet2
delete the last cell data of each row of Sheet3
delete the last cell data of each row of Sheet4

see the Result , I want the result in same sheet not in separate sheet



I have to do all this by Vba
Kindly see the sample file
Book1.xlsm
0
Cloud Class® Course: Amazon Web Services - Basic
LVL 12
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Copy sheet1  last cell data of each row  to sheet5 A column
Copy sheet2  last cell data of each row  to sheet5  B column
Copy sheet3  last cell data of each row  to sheet5 C column
Copy sheet4  last cell data of each row  to sheet5 D column

i have to do all this by vba
kindly see this sample file
Book1.xlsm
0
Conditionally copy the data  and paste the data
COPY THE DATA                  PASTE TO
FROM COLUMN A             TO      SHEET2
FROM COLUMN B            TO      SHEET3
FROM COLUMN C            TO      SHEET4
FROM COLUMN D            TO      SHEET5
                  
Copy the data and paste the data as per given condition
see the sample file for details
highlighted colour in this file is only for understanding purpose, it is not there in actual file
Book10.xlsx
0
Hi,

I have an Access FE and SQL 2014 BE and i encounter the following issue:
1. I have a bound form
2.  if the record is a NEW record, never saved, and I set some textbox to NULL explicitly (me.someTextbox = Null) and then I save the record  using Dirty=False, it gives a #deleted for the entire record
3. I've checked if it could be some trigger etc... but it doesn't sound so (i've disabled them all for testing)

Any help would be appreciated

Joseph
0
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!
0
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
0
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
0
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.
0
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.
0
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)
0
Cloud Class® Course: SQL Server Core 2016
LVL 12
Cloud Class® Course: SQL Server Core 2016

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.

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!
0
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
0
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
0
how to convert data table in the xls file into xml format with VBA macro ?
0
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
0
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
0
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.
0
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
0
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 …
0
Free Tool: Subnet Calculator
LVL 12
Free Tool: Subnet Calculator

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>
0
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 …
0
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

0
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!
0
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.
0
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

0

VBASponsored by jamf

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.