Re: Kickbutt VBA Find Function
Hi Aaron, just a couple of things;
1) FirstAddress wasn't defined.
2) I like to use functions where you are given choices rather then guess @ the expected values i.e LookIn = xlValues 'xlFormulas the use wil be expected to places these constant in.
To this end (For Excel2000+) I use Enum like so ......
With Enum writing the Code gives the user intellisence options for the Consts.
Don't take this the wrong way, it is a nice function... one that I will use
Any way for xl2000+ this is how I will use it >Code
Enum eLookin xl_Formulas = -4123 xl_Comments = -4144 xl_Values = -4163 End Enum Enum eLookat xl_Part = 2 xl_Whole = 1 End Enum Function Find_Range(Find_Item As Variant, _ Search_Range As Range, _ Optional LookIn As eLookin, _ Optional LookAt As eLookat, _ Optional MatchCase As Boolean) As Range Dim c As Range, FirstAddress As String '<< If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole If IsMissing(MatchCase) Then MatchCase = False With Search_Range Set c = .Find( _ What:=Find_Item, _ LookIn:=LookIn, _ LookAt:=LookAt, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=MatchCase, _ SearchFormat:=False) If Not c Is Nothing Then Set Find_Range = c FirstAddress = c.Address Do Set Find_Range = Union(Find_Range, c) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> FirstAddress End If End With End Function
Re: Event Triggers are not working
May sound silly but
1) have you enabled macros.
2) Are you sure you have the Event codes in the correct place.
3) Do you have any other code that may hinder the event codes from running
Re: TOC of Shared File Directory using VBA
This File maybe of use to you
File properties extracted from the Namespace Folder are;
Re: extract compressed file attachement...
if you want to unzip a file then something like this may helpCode
Function WinZipIt(Source As String, Target As String, blnZipIt As Boolean) Dim ZipIt As String Dim UnZipIt As String ZipIt = "C:\Program files\Winzip\" & "winzip32 -a " UnZipIt = "C:\Program Files\Winzip\" & "winzip32 -e " If blnZipIt Then Shell (ZipIt & Target & Source) Else Shell (UnZipIt & Target & Source) End If End Function Sub TestZip() WinZipIt " C:\UnzippedFilesHere\", " H:\Fidelio\FO_data\History\Spool\20010817.zip", False End Sub
As far as the rest .... that's doable as well ie... extracting the attachments and unziping
Re: Progress Bar
If I understand you correctly it is the Random numbers you don't need ??
Just remove the code that does this eg
'Put a random number in a cell
Cells(r, c) = Int(Rnd * 1000)
remove the above ??
Re: macro to search and unzip and collect to total amount
I have a Translated UnRar file I did a while ago that may be of use.
See what you can make of this ....
Re: FileSearch ExcXP
Worked for me IF C:\MyDocuments exists.
Are you sure you have this Dir ?
What do you get if you run
MsgBox Dir("C:\MyDocuments", vbDirectory)
Re: Cannot Run Recorded CodeQuote from mhabib
Thanks Ivan. Your code works.
My first question is: What's the role of the Const declaration in this case?
Sencondly, is there a way to modify the declaration for a source range that's variable?
For example, if instead of
I were to use
How should I change the Const declaration to accommodate this kind of variable range reference?
The use of the Const is due to a bug for this particular property of the Paste class. (ie the pasteColumnWidth) Defining it just circumvents the bug.
Can't find the MS link for the bug ... sorry.....
Re: Cannot Run Recorded Code
Re: VBA Coding practicesQuote from Brandtrock
With this type of arrangement do you have any opinion one way or the other as to whether Subs or Functions would go first?
My thought is to list the Functions first as they are used in the Subs therefore, when they are used, the purpose of the function should be obvious to a subsequent coder. Probably nit picking, but I tend to read code top to bottom and not hop around using the drop downs until I am familiar with the code. Especially if it is written by someone else and I'm trying to amend it.
I generally prefix my functions with a "f" to denote it is a function for easier reading plus macros / functions are listed aphabetically so drop down lists all functions grouped.
also, what naming convention are you using ... using one is a g8 way to decipher your code latter as it should tell you just by looking @ it;
what scope the function/sub/variable has , what type is is and a discriptive text of what it does etc.
Re: Using UNC address to reference another file
if lreturn = 0 then
I'm not sure why I put that like it is ????
Re: Using UNC address to reference another file
You can't Change Dir or Drives using the UNC convention.
You will need to resort to APIs for this..... adapt this routine...Code
Option Explicit '// Function Discription: '// :The SetCurrentDirectory function changes the current '// :directory for the current process. Private Declare Function SetCurrentDirectoryA _ Lib "kernel32" ( _ ByVal lpPathName As String) _ As Long 'Use fChDirUNC Like ChDir Public Function fChDirUNC(ByVal szPathToSet As String) As Long fChDirUNC = SetCurrentDirectoryA(szPathToSet) End Function Sub Tester() Dim lReturn As Long '// Change this to your UNC Path Const strDir As String = "//share/path" lReturn = fChDirUNC(strDir) If Not lReturn Then '// If the function succeeds, the return value is nonzero. '// If the function fails, the return value is zero. '// Show the message MsgBox "Could not Change Directory to " & strDir Exit Sub End If '// Success so ......the rest of your code here End Sub
Re: open program from macro
You need to put in the path to the Exercutable program that opens it along with the doc to open as you have it.
I think what you are talking about is vbs or java script
eg if you write this in notepad and then save with extention .vbs you can run it to open an excel app.
xl_file_full_path="C:\ExcelFiles\Useful\Sample.xls" 'Change here to your XL full path
set appXL = CreateObject("Excel.Application")
="Douglass Mayberry "& TEXT(TODAY(),"mm/d/yyyy")