PDA

View Full Version : My first VBA



sirl212
06-27-2012, 08:26 PM
Hello,

I'm completely new and trying to learn VBA to help further my career, at the moment I'm reading the dummies book for VBA programming. I wanted to start with something simple, I was hoping to have the program look into a folder and if that folder has the word week in it, have the vba change the number following the word week into the next corresponding number. Such as week9.xls into week10.xls

Any help and or direction/ advice is incredibly appreciated.

lynnnow
06-27-2012, 10:38 PM
Which version of Excel are you running?

Bob Phillips
06-28-2012, 12:24 AM
If we do it for you, you are learning nothing. Seeing as you have a book, presumably you have tried some stuff yourself. What have you tried?

sirl212
06-28-2012, 05:51 PM
I'm working with 2007 at home but at work they use 2003 and 2010, not sure the difference in coding. I haven't done anything because I can't figure out how to start. My problem is that I'm trying to call up the folder and make the excel file with Week in its name as the active file. But I'm only up to chapter 5 on the dummies book and they don't seem to get that advance. Maybe just a little hint on this would help.

Kenneth Hobs
06-28-2012, 06:57 PM
Welcome to the forum!

You picked a project that has several issues to consider. e.g. Could the file already be open by someone or yourself?

Many times, people want to save with an incremental number. http://www.vbaexpress.com/kb/getarticle.php?kb_id=1008

You said look for a folder with the word week and then you used week in the filename. Paste this code into a Module that you insert in the Visual Basic Editor (VBE).

Sub Increment1()
Dim fn As String, fn2 As String, fn3 As String
Dim i As Integer

fn = "x:\dir\week*.xlsm"
fn2 = Dir(fn) 'Finds first file matching wildcard.
If Dir(fn2) = "" Then Exit Sub 'Exit if no file was found.
fn3 = BasenamePart(fn2) 'Get the basename of the file without path and file extension.
i = StripOutCharType(fn3, False) 'Get the numbers in the basename.
Name fn2 As FolderPart(fn2) & StripOutCharType(fn3, True) & i + 1 & ".xlsm"
End Sub

Function FolderPart(sPath As String) As String
FolderPart = Left(sPath, InStrRev(sPath, "\"))
End Function

Function FilenamePart(sFullname As String) As String
FilenamePart = Mid(sFullname, InStrRev(sFullname, "\") + 1)
End Function

Function BasenamePart(sFullname As String) As String
Dim s As String
s = FilenamePart(sFullname)
BasenamePart = Left(s, InStrRev(s, ".") - 1)
End Function

' http://www.vbaexpress.com/kb/getarticle.php?kb_id=816
Public Function StripOutCharType(CheckStr As String, Optional KillNumbers As Boolean = True, _
Optional AllowedChar As String, Optional NeverAllow As String)

' For the given string, the function removes all numeric characters (KillNumbers=True) or
' all non-numeric characters (KillNumbers=False). Use AllowedChar to build a string of override
' characters that are always allowed. For example, "$,." would indicate that the dollar sign,
' comma, and period should all be allowed, even if KillNumbers=False; likewise, "9" would indicate
' that nines should be kept even if KillNumbers=True. NeverAllow is a string of override
' characters that are never allowed. The "never allowed" characters are processed before the
' "always allowed" characters, and so if any characters are in both strings Never allow takes
' precedence

' The AllowedChar and NeverAllow arguments are *not* case-sensitive

Dim Counter As Long
Dim TestChar As String
Dim TestAsc As Long

' Loop through characters
For Counter = 1 To Len(CheckStr)

' Get current character and its ANSI number
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)

' Test first to see if current character is never allowed
If InStr(1, NeverAllow, TestChar, vbTextCompare) > 0 Then
' do nothing

' If current character is in AllowedChar, keep it
ElseIf InStr(1, AllowedChar, TestChar, vbTextCompare) > 0 Then
StripOutCharType = StripOutCharType & TestChar

' If KillNumbers=True, test for not being in numeric range for ANSI
ElseIf KillNumbers Then 'only allow non-numbers
If TestAsc < 48 Or TestAsc > 57 Then
StripOutCharType = StripOutCharType & TestChar
End If

' If KillNumbers=False, test for being in numeric ANSI range
Else 'only allow numbers
If TestAsc >= 48 And TestAsc <= 57 Then
StripOutCharType = StripOutCharType & TestChar
End If
End If
Next

End Function

sirl212
06-28-2012, 09:05 PM
Wow, I meant to say for the program to look in a folder and search for an Excel file that has the word Week 21 in it and renames it to the next consecutive name which should be Week 22.xlsx. But this helps a lot. Ok, I'll try this at work tomorrow. Thank you so much, this gives me a much better understanding of how much I still have to learn.

Kenneth Hobs
06-29-2012, 06:58 AM
It looks like lots of code but not really. Even Increment1 is not all that big. The bigger part comes in the error checking. The main part of it is very simple in that it breaks each filename part down and then recombines it and uses Name to rename the file.

When your code is modular, you can reuse code as shown by my fso Functions and by kb 816 in the last post. I did not include here but it is used. Of course it is a tad slower since it set the fso object and then removes it. You could combine all that into the main Sub if more efficiency is needed. Today's computers are so fast, it seldom makes that much of a difference. I prefer modular programming generally for methods that I use many times.

I added a Format() to keep the "00" format in the number part. I provided a link so that you can explore fso in more detail. Be sure to add the library reference from the VBE Tools > References menus as detailed in the comment.

Sub Increment1()
Dim fn As String, fn2 As String, fn3 As String
Dim i As Integer

fn = "x:\t\Book??.xlsx"
fn2 = Dir(fn) 'Finds first file matching wildcard.
If fn2 = "" Then
MsgBox "No match found for: " & vbLf & fn, vbCritical, "Macro Ending - File Does Not Exist"
Exit Sub 'Exit if no file was found.
End If

fn3 = GetBaseName(fn2) 'Get the basename of the file without path and file extension.
i = StripOutCharType(fn3, False) + 1 'Get the numbers in the basename and add 1.
Name fn2 As GetFolderName(fn2) & StripOutCharType(fn3, True) & Format(i, "00") & "." & GetFileExt(fn2)
End Sub

Rem Needs Tools > References > MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
Rem Help File: script56.chm, http://tinyurl.com/5ts6r8
Function GetFolderName(filespec As String) 'Returns path with trailing "\"
Dim fso As FileSystemObject, s As String
Set fso = New FileSystemObject
s = fso.GetParentFolderName(filespec)
Set fso = Nothing
GetFolderName = s
End Function

Function GetFileName(filespec As String)
Dim fso As FileSystemObject, s As String
Set fso = New FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
s = fso.GetFileName(filespec)
Set fso = Nothing
GetFileName = s
End Function

Function GetBaseName(filespec As String)
Dim fso As FileSystemObject, s As String
Set fso = New FileSystemObject
s = fso.GetBaseName(filespec)
Set fso = Nothing
GetBaseName = s
End Function

Function GetFileExt(filespec As String)
Dim fso As FileSystemObject, s As String
Set fso = New FileSystemObject
s = fso.GetExtensionName(filespec)
Set fso = Nothing
GetFileExt = s
End Function

' http://www.vbaexpress.com/kb/getarticle.php?kb_id=816
'Insert kb 816 code below:

sirl212
06-29-2012, 08:05 AM
I'm getting an error message when I'm running it: sub or function not defined - (StripOutCharType). Do I have to have this function loaded somewhere in the background? I was running it on 2003 so I'm not sure how to locate the library. :dunno

CodeNinja
06-29-2012, 08:18 AM
sirl212,
This is a fantastic opportunity to learn how the code works. Obviously, it is not finding the sub or function you are calling (typically these things are a misspelling error or something like that). When the code breaks, go into the code, and look for where it is highlighted. This will show you the line it broke at... you can then look for that sub and see if you have it in your program. Perhaps you misspelled something... for example:

Sub test()
msgbx hi
End Sub
This sub will give me that exact error because I misspelled msgbox (missing the o) and it doesn't recognize the command, so it assumes it is a subroutine or function.

Good luck with your debugging... I learn more from debugging than anything else I do.

sirl212
06-29-2012, 08:35 AM
Sub Increment1()
Dim fn As String, fn2 As String, fn3 As String
Dim i As Integer

fn = "x:\t\Book??.xlsx"
fn2 = Dir(fn) 'Finds first file matching wildcard.
If fn2 = "" Then
MsgBox "No match found for: " & vbLf & fn, vbCritical, "Macro Ending - File Does Not Exist"
Exit Sub 'Exit if no file was found.
End If

fn3 = GetBaseName(fn2) 'Get the basename of the file without path and file extension.
i = StripOutCharType(fn3, False) + 1 'Get the numbers in the basename and add 1.
Name fn2 As GetFolderName(fn2) & StripOutCharType(fn3, True) & Format(i, "00") & "." & GetFileExt(fn2)
End Sub[/quote]

Ok so I'm not sure if it's correct but I added in:

Sub Increment1()
Dim fn As String, fn2 As String, fn3 As String
Dim i As Integer
Dim StripOutCharType As Characters

But now I get the message of file not found. I altered the path to point to the folder I want the macro to look in but I guess I'm specifying the file name wrong.

CodeNinja
06-29-2012, 08:45 AM
In this line:
fn = "x:\t\Book??.xlsx"
Are you looking for week?? or book??

sirl212
06-29-2012, 08:54 AM
In this line:
fn = "x:\t\Book??.xlsx"
Are you looking for week?? or book??

I am looking for Week ## personname.xlsx and rename ## +1 and save.

Kenneth Hobs
06-29-2012, 09:04 AM
When testing, I have to make sure that I have a valid string. In "x:\t\Book??.xlsx", I doubt that you have an X: drive or the path x:\t\ but it is possible that you would have a book??.xlsx. The ?? are wilcard characters. Obviously, you would replace book with week along with the drive and path.

If I don't know all the details, I can't help much. Now you say that your naming convention is different. That type of naming convention requires other tweaks. When naming files, try to use the incremental number in the prefix or a suffix for the base file name. So, if your base filenames are composed of three words, it might be even easier. You would replace ?? with * for one thing. You can use Split() to create an array and then get the 2nd element to increment and then add the 1st and 3rd elements to build the new base string. Before I show you how that is done, are three words making up the base name?

sirl212
06-29-2012, 09:39 AM
Yes it is, the base name would be Week 22 Smith.xlsx
So I'm assuming the the code would be

Sub Split()
Dim SptTxt As String
Dim CsU As Byte
Dim CsL As Byte
Dim Cs As Variant
SptTxt = ""
Cs = Split(SptTxt, ",")
End Sub

Thank you, I'm learning a lot.