Contents. A Quick Guide to this Post The following table provides a quick reference guide to what is covered in this post. String Type Task How to 1234ABC334 Fixed size get left 4 chars 1234ABC334 Fixed size get right 3 chars 1234ABC334 Fixed size get chars 5,6,7 'John Henry Smith' Variable size get first name 'John Henry Smith' Variable size get second name 'John Henry Smith' Variable size get third name 'John Henry Smith' Variable size Get last name s Quick Reference Notes To find out more about the items referenced in the post check out the following links If you would like to know more about the InStr or InStrRev functions then please read. If you would like to know more about Mid, Left or Right functions then check out.
Oct 07, 2016 Hi AMPS12, The link tutorials on creating PDF file (this code is in C# but with every converter it can be converted to VB), please check if it helps. Microsoft.NET doesn't include a PDF component, you could use a third-party component, here are a few popular components for the task (ABCpdf.NET, iTextsharp, PDFSharp).
For more about the Split function check out. The Like operator is covered in I use Debug.Print in my examples. It prints values to the Immediate Window which you can view by pressing Ctrl and G (or select View-Immediate Window) Introduction In this post, I’m going to show you a better way to extract values from a string than using then VBA InStr function with Left, Right or Mid. This post is broken down as follows. Section 1: How to extract from fixed sized strings. Section 2: How to extract from variable sized strings. Section 3: How to extract from variable sized string using the Split function.
Sections 4 to 6: Some real world examples. When VBA InStr, Left, Right and Mid are useful If you want to check if a string contains a value then InStr is fine for the job. If you want to do a simple extraction then Left, Right and Mid also fine to use.
Using InStr to check if string contains text In the following example, we check if the name contains “Henry”. If the return value of InStr is greater than zero then the string contains the value we are checking for. ' Check if string contains Henry If InStr( 'John Henry Smith', 'Henry') 0 Then Debug.Print 'Found' End If Extracting with Left, Right and Mid The Left function is used to get characters from the left of a string. The Right function is used to get characters from the right of a string. The Mid function is used for the middle of the string.
It is the same as Left except that you give it a starting position. Sub ExtractString Dim s As String: s = 'ABCD-7789.WXYZ' Debug.Print Left(s, 2) ' Prints AB Debug.Print Left(s, 4) ' Prints ABCD Debug.Print Right(s, 2) ' Prints YZ Debug.Print Right(s, 4) ' Prints WXYZ Debug.Print Mid(s, 1, 2) ' Prints AB Debug.Print Mid(s, 6, 4) ' Prints 7789 End Sub These three functions work fine if the text you require is always the same size and in the same place. For other scenarios, they require the use of InStr to find a particular position in the string. This makes using them complicated. Use Left, Right or Mid when the characters will always be in the same position. Dealing with Strings of Varying Lengths Many of the strings you will deal with will be of different lengths.
A simple example is when you are dealing with a list of names. The string length and part you require(e.g. The first name) may be of different each time.
For example Brooke Hilt Pamela Jurado Zack Kinzel Eddy Wormley Kaitlyn Rainer Jacque Trickett Kandra Stanbery Margo Hoppes Berenice Meier Garrett Hyre (If you need random list of test names then try this ) Using the VBA InStr Function with Left In the following example, we are going to get the first name from a string. In this string the first name is the name before the first space. We use the VBA InStr function to get the position of the first space. We want to get all the characters before the space. We subtract one from the position as this gives us the position of the last letter of the name. Sub GetFirstname Dim s As String, lPosition As Long s = 'John Henry Smith' ' Prints John lPosition = InStr(s, ' ') - 1 Debug.Print Left(s, lPosition) s = 'Lorraine Huggard' ' Prints Lorraine lPosition = InStr(s, ' ') - 1 Debug.Print Left(s, lPosition) End Sub Let’s look at the first example in the above code. The first space is at position 5.
We substract 1 so which gives us position 4. This is the position of the last letter of John i.e. We then give 4 to the Left function and it returns the first four characters e.g. “John” We can perform the same task in one line by passing the return value from InStr to the Left function. Dim s As String s = 'John Henry Smith' ' Prints John Debug.Print Left(s, InStr(s, ' ') - 1) Using the VBA InStr Function with Right In this example, we will get the last word in the string i.e. We can use the function to help us. This is the same as InStr except it searches from the end of the string.
It’s important to note that InStrRev gives us the position from the start of the string. Therefore, we need to use it slightly differently than we used InStr and Left. Sub GetLastName Dim s As String: s = 'John,Henry,Smith' Dim Position As Long, Length As Long Position = InStrRev(s, ',') Length = Len(s) ' Prints Smith Debug.Print Right(s, Length - Position) ' Alternative method. Prints Smith - do in one line Debug.Print Right(s, Len(s) - InStrRev(s, ',')) End Sub How this the above example works. We get the position of the last space using InStrRev: 11. We get the length of the string: 16. We subtract the position from the length: 16-11=5.
We give 5 to the Right function and get back Smith Using the VBA InStr Function with Mid In the next example, we will get “Henry” from the string. The word we are looking for is between the first and second space. We will use the function here. Sub GetSecondName Dim s As String: s = 'John Henry Smith' Dim firstChar As Long, secondChar As Long Dim count As Long ' Find space position plus 1. Result is 6 firstChar = InStr(s, ' ') + 1 ' find 2nd space position. Result is 11 secondChar = InStr(firstChar, s, ' ') ' Get numbers of characters. Result is 5 count = secondChar - firstChar ' Prints Henry Debug.Print Mid(s, firstChar, count) End Sub You can see this is tricky to do and requires a bit of effort to figure out.
We need to find the first space. Then we need to find the second space. Then we have to substract one from the other to give us the number of characters to take. If have a string with a lot of words then this can get very tricky indeed.
Luckily for us there is a much easier was to extract characters from a string. It’s called the Split function.
The Split Function We can use the to perform the above examples. The Split function splits a string into an array. Then we can easily access each individual item.
Let’s try the same three examples again and this time we will use Split. Dim s As String: s = 'John Henry Smith' Debug.Print Split(s, ' ')(0) ' John Debug.Print Split(s, ' ')(1) ' Henry Debug.Print Split(s, ' ')(2) ' Smith Boom! What a difference using Split makes. The way it works is as follows. The Split function splits the string wherever there is a space.
Each item goes into an array location starting at location zero. Using the number of a location we can access an array item.
The following table shows what the array might look like after Split has been used. Note: the first position in the array is zero.
Having zero based arrays is standard in programming languages. 0 1 2 John Henry Smith In the above code we split the string each time we used it. We could also split the string once and store it in an array variable. Then we can access it when we want.
Sub SplitName Dim s As String: s = 'John Henry Smith' Dim arr As String arr = Split(s, ' ') Debug.Print arr(0) ' John Debug.Print arr(1) ' Henry Debug.Print arr(2) ' Smith End Sub If you would like to know more about arrays then I wrote an entire post about them called. In the next sections, we will look at some real world examples. You will see the benefit of using Split instead of the InStr function.
Please feel free to try these yourself first. It is a great way to learn and you may have fun trying to figure them out(or maybe that’s just me!) Example 1: Getting part of a file name Imagine we want to extract the numbers from the following filenames “VB23476Val.xls” “VV987Val.txt” “VZZA12223Val.doc” This is similar to the example about where we get the second item. To get the values here we use the underscore(i.e. “”) to split the string. See the code example below Sub GetNumber ' Prints 23476 Debug.Print Split( 'VB23476Val.xls', ')(1) ' Prints 987 Debug.Print Split( 'VV987Val.txt', ')(1) ' Prints 12223 Debug.Print Split( 'ABBZA12223Val.doc', ')(1) End Sub In the real world you would normally read strings like these from a range of cells. So let’s say these filenames are stored in cells A1 to A3. We will adjust the code above slightly to give us: Sub ReadNumber Dim c As Range For Each c In Range( 'A1:A3') ' Split each item as you read it Debug.Print Split(c, ')(1) Next c End Sub Example 2: IP Address Range The example here is taken from a question on the website.
The user has a string with an IP address in the format “BE-ABCDDD-DDS 172.16.23.3”. He wants an IP of the range 172.16 to 172.31 to be valid. So for example “BE-ABCDDD-DDS 172. 16.23.3″ is valid “BE-ABCDDD-DDS 172.
25.23.3″ is valid “BE-ABCDDED-DDS 172. 14.23.3″ is not valid “BE-ABCDDDZZ-DDS 172. 32.23.3″ is not valid This is how I would do this. First I split the string by the periods. The number we are looking for is between the first and second period.
Therefore, it is the second item. When we split the string it is placed at position one in the array (remember that the array starts at position zero).
The resulting array will look like this 0 1 2 3 BE-ABCDDD-DDS 172 31 23 3 The code below shows how to do this Sub IPAdd ' Check the number to test different ip addresses Dim s1 As String: s1 = 'BE-ABCDDD-DDS 172.31.23.3' ' Split the string using the period symbol Dim num As Long num = Split(s1, '.' )(1) ' Check the number is valid Debug.Print num = 16 And num 0 And Right(f, 4) = '.pdf' End Sub This code is very messy. Luckily for us, VBA has.
We can check the pattern of a string without having to search for items and positions etc. We use the Like operator in VBA for pattern matching. The example below shows how to do it. Sub UsePattern Dim f As String: f = 'AA1234(5).pdf' ' Define the pattern Dim pattern As String: pattern = '.AA.1234.pdf' ' Check each item against the pattern Debug.Print f Like pattern ' True End Sub In the above example, the asterisk in the pattern refers to any number of characters. Hi Bruce, That’s a good question. If you are using a large number of items then it is a good idea to do this The following code is from the This code uses Split once and stores the result to an array.
sourcecode language=”vb” Sub SplitName Dim s As String: s = 'John Henry Smith' Dim arr As String arr = Split(s, ' ') Debug.Print arr(0) ‘ John Debug.Print arr(1) ‘ Henry Debug.Print arr(2) ‘ Smith End Sub /sourcecode Imagine we were reading a large number of items e.g. The range A1 to A5000. Then we should use erase each time we finish with the array. The following code shows an example sourcecode language=”vb” Sub SplitNameWithErase Dim s As String Dim arr As String Dim c As Range For Each c In Range('A1:A5000') arr = Split(c, ' ') Debug.Print arr(0) ‘ first name Debug.Print arr(1) ‘ second name Debug.Print arr(2) ‘ surname Erase arr Next c End Sub /sourcecode. Hi Paul I Have an Excel Database of nearly 4,500 songs of the 60’s, 70’s, 80’s and 90’s, chronologically and alphabetically sorted as are the mp3 files, eg c: Music Collection 2 – SEVENTIES 1974 74 – Billy Preston – Nothing From Nothing.mp3 I can currently parse the above through to the Windows Object library, but only one mp3 at a time. I’ve tried to use ‘Collections’ to parse multiple mp3’s to no avail.
An multiple mp3’s be parsed through to Media Player? Will Arrays work.
Below is my code. (Please forgive me, but I am only a beginner, but quickly turning into a VBA addict).
If you coild help me overcome this hurdle, I’d be most appreciative. Cheers Geoff Sub PlaySong Dim DriveName, FullPathSongTitle, Decade, Year, HalfYear, Artist, Song, Answer, CurrentCell, MP3Title As String On Error Resume Next Application.ScreenUpdating = False CurrentCell = ActiveCell.Address Range(“A” & ActiveCell.Row).Offset(0, 5).Select DriveName = Range(“L3”).Text Select Case (Mid(ActiveCell.Offset(0, 1), 3, 1)) Case Is = 5, 6: Decade = “1 – SIXTIES” Case Is = 7: Decade = “2 – SEVENTIES” Case Is = 8: Decade = “3 – EIGHTIES” Case Is = 9: Decade = “4 – NINETIES” End Select Year = ActiveCell.Offset(0, 1).Value: If Year.
Sometimes what is not said is more important that what is said. Trying to describe a process to a programmer without knowing the common 'programmer acronyms' can be a challenge.
I usually try to read between the line and try to find what the user really wants, not what it looks like he's asking for. And often it can be done without playing '20 questions'. Yes, the code in both links is VB6 code. Neither codepiece is complete.
You'll need to customize it to make it do what you need. The code in my link = was pulled from a production program that is able to open any file or document in its' native application. The 'native application' must be installed on the workstation so the file association can be determined.
(to open pdf files, you need at least adobe reader). Hi,i cannot get the code work. You receive the error because you are sending a value; i.e. Your file name. If the function requires a variable, then you need to send a variable that you declared in your function.
When your function uses ByRef, then you need to send a variable. To get around the error and to use the function as is, just declare a variable and give it a value before sending it to the function. The function can then change the value of that variable, but it does not sound like it will.
So, that function probably could have been more efficiently written by using ByVal. Unless the programmer's intent was to optionally change the value of the variable. When you use the ShellExecute API you can just use 'open', 'print', or 'explore' for the second parameter. In your case, you just want to use 'open' for the operation. The operating system will use the program that is designated in the windows registry to open a file with that file's extension. If you use the VB Shell function, you then must use the actual name of the program plus any parameters that may accompany the file. You need to use a space between the program name and the parameter; hence, chr$(32).
You have more options, if you use the ShellExecute API. But it's more complicated. The following example uses both the ShellExecute API and the VB Shell function. The VB Shell function just performs a limited set of the options of the ShellExecute API. Option Explicit Private Const SWSHOWNORMAL = 1 Private Declare Function ShellExecute Lib 'shell32.dll' Alias 'ShellExecuteA' (ByVal hwnd As Long, ByVal lpOperation As StringByVal lpFile As String, ByVal lpParameters As StringByVal lpDirectory As String, ByVal nShowCmd As Long) As Long Private Sub FormLoad Dim lngReturn As Long Dim dblReturn As Double lngReturn = LoadUserFile(Me.hwnd, 'C: Documents My.mdb') If lngReturn. Thanks for reply i am still getting error with your code error is Compiler error: Sub or function not defined. I am new to vb and just want a vb comiled EXE to run MSACCESS mdb or accdb database.
It would help if you would list what sub or function was not defined. Usually the compiler stops on the function that is not defined and has it highlighted. Don't make it too complicated. If you want to use the Shell command, do something similar to the following: Shell 'c: Program Files Microsoft Office MSAccess.exe ' & 'c: Documents and Settings MyUserName MyAccess.mdb', vbNormalFocus The Shell Function expects the program name and it expects any parameters, such as the filename, to be separated from the program name by a space. The ShellExecute API is simpler; but you can just use the the word 'open' with the second parameter, and the filename with the second parameter. But just try the Shell first: it should work. I was going to stay out of this, but I see too many ways being described to do the same thing.
The code I posted earlier goes into a module. Elsewhere in a program I fill a listbox with the path information of the files I might want to open in their native application. The path information is stored in an MS Access table, or an sql server table, or a MySQL table, or whatever other place you might want to keep them. I named my listbox 'ListView1' (vb default name) To populate the Listbox I used this. The path information is stored in an MS Access database: Function popList ' populates the listview control Dim sql As String dim x as integer ' the next line builds the sql statement and adds the user id to the where clause so each user can only see their own documents.
Sql = 'select tblDocs.docName, tblDocs.docPath, tblDocs.id from tblDocs where clientid = ' & getClientID(ActiveClientName) ' Call opnDB(Me) ' this line just opens a connection to the database. The error is generated by access app. MSAccess Windows open and tells that file c: Documents and Settings MyUserName MyAccess.mdb is not present.
To generate the error code in VB first, you must test the file in VB first. You can use the Microsoft Scripting Runtime Library and use the File System Object. You might try using the ShellExecute API. It returns error codes for File not found, file association errors, etc. I haven't tried it.
But if you start using these extras such as file system object, Checking Error Codes on running the ShellExecute API, I would say your request for making a simple executable to run an access file is not really that simple. If you're a novice, as you say you are, then the best you can hope for is copying and pasting somebody else's code to accomplish your purpose.
Or you're going to have to do some digging and show some personal effort to figure out what's going on and why.