Posts by Mavyak
-
-
-
I'd like to programatically hide a table I create. I've tried the following:
CodeCurrentdb.tabledefs("BTN_Update_Log").Attributes = Currentdb.tabledefs("BTN_Update_Log").Attributes + dbhiddenobject
And it works. The problem is, when I go to Tools --- Options --- show hidden objects, I still can't see the table. I noticed that when I right click on a table and select hidden that the "Flags" value in the MSysObject table changes to an "8". I tried:
CodeDoCMD.RunSQL "UPDATE MSysObjects SET MSysObjects.Flags = 8 WHERE (((MSysObjects.Name)='BTN_UPDATE_LOG'))"
to update the flags field to an "8" for my newly created table but I got an error message telling me I needed to use an updateable query.
How can I programatically do the exact equivalent of right clicking on a table and checking the "Hidden" checkbox in the lower left hand corner?
-
Re: Repeatedly Adding Properties Slows Performance
Not sure why it worked but it did.
Many thanks GeorgS!
:music:
-
I have a process that automatically imports a text file into my database. When the import happens, the table it is replacing gets renamed so that the current data always has the same table name. For housekeeping purposes I am writing some code that checks a custom property that I add to the backed up table when the new table is created. My problem is that the first time I run the code, everything is snappy and fast. Thereafter, the for loop on my TableDefs collection takes approximately 5 seconds per tablename to check. Does anyone know why that happens or better yet, how to speed it up? Here is the code:
Code
Display MoreSub Back_Up_Existing_Location_Roll_Up_File() MappingsValidationCode.procedure_name = "Back_Up_Existing_Location_Roll_Up_File" On Error GoTo error Dim x As Integer Dim y as Integer Dim db As DAO.Database Set db = CurrentDb Dim t As TableDefs Set t = db.TableDefs Dim mytablename As String mytablename = "Location Roll-Up File" y = t.Count - 1 For x = 0 To y If t(x).Name = mytablename Then Call addInternalNameProperty(mytablename, mytablename) t(x).Name = mytablename & " Replaced " & Replace(Replace(Now, "/", "-"), ":", " ") Application.RefreshDatabaseWindow Exit For End If Next x Set db = Nothing Set t = Nothing Exit Sub error: Call Error_Handling.Log_Error(Err.Number, Err.Description, MappingsValidationCode.procedure_name) MsgBox "An error has occured. The error has been logged. Contact <My Name> at <My Phone Number>.", vbOKOnly, "Error Logged" End End Sub Sub addInternalNameProperty(tablename As String, PropValue As String) Dim p As Property Set p = CurrentDb.TableDefs(tablename).CreateProperty("InternalName", dbText, PropValue) CurrentDb.TableDefs(tablename).Properties.Append p CurrentDb.TableDefs.Refresh Application.RefreshDatabaseWindow Set p = Nothing End Sub
-
Re: Forced Exit Macro Vis-a-vis Autoexec?
I'll give it a shot. Thanks for the reply!
-
Re: Change mouse pointer to hand
From below there is a limited set of options available.
QuoteMousePointer Property
See Also Applies To Example Specifics
You can use the MousePointer property together with the Screen object to specify or determine the type of mouse pointer currently displayed. Read/write Integer.expression.MousePointer
expression Required. An expression that returns one of the objects in the Applies To list.
Remarks
The setting for the MousePointer property is an Integer value representing one of the following pointers.Setting Description
0 (Default) The shape is determined by Microsoft Access
1 Normal Select (Arrow)
3 Text Select (I-Beam)
7 Vertical Resize (Size N, S)
9 Horizontal Resize (Size E, W)
11 Busy (Hourglass)Note Setting the MousePointer property to an integer other than one that appears in the preceding table will cause the property to be set to 0.
You can set the MousePointer property only by using Visual Basic.The MousePointer property affects the appearance of the mouse pointer over the entire screen. Some custom controls have a MousePointer property that, if set, will specify how the mouse pointer is displayed when it's positioned over the control.
You could use the MousePointer property to indicate that your application is busy by setting the property to 11 to display an hourglass icon. You can also read the MousePointer property to determine what's being displayed. This could be useful if you wanted to prevent a user from clicking a command button while the mouse pointer is displaying an hourglass icon.
Setting the MousePointer property to 11 is the same as passing the True (–1) argument to the Hourglass method of the DoCmd object. Conversely, passing the True argument to the Hourglass method also sets the MousePointer property to 11.
Example
The following example changes the mouse pointer to an hourglass.Screen.MousePointer = 11
Perhaps an API call would work?
-
Is there a macro naming convention that I could use to force a macro to run after a user clicks the "X" to close the database and before the database actually closes?
-
Re: Right-click Menus Compiler Add-in
I didn't really see the value in this at first but thought, "Whiskey Tango Hotel, I'll give it a shot."
I don't know if I could function without it now. Very nice add-in. Muchos Appreciado! :music: -
Re: Folder Picker Dialog Box
Cheers Norie!
You've helped another troubled soul! :music:
-
Re: Hover Highlighter
Two words...
[SIZE="5"]Suh Weet![/SIZE] :music:
-
The following code will take selected cells (if more than one cell is selected) or all cells from A2 and down until a blank cell is reached and place the contents of each cell in a notepad text file enclosed in quotation marks and separated by commas. The list is particularly useful when running an Access query with an In or Not In clause(i.e. ...Where Last_Name In(list of names from Excell goes here)).
There may be some tweaking involved in the Shell statements depending on which version of Windows you are running.
Code
Display MoreSub geninlist() Dim inlist As String Dim x As Integer Dim a As Object Dim ReturnValue As Double If Selection.Count > 1 Then For Each c In Selection inlist = inlist & c.Value & "," Next c Open "C:\inlist.txt" For Output As #1 Write #1, Left(inlist, Len(inlist) - 1) Close #1 ReturnValue = Shell("C:\WINNT\System32\Notepad.exe C:\inlist.txt", vbMaximizedFocus) AppActivate ReturnValue SendKeys "^a", True SendKeys "^h", True SendKeys ",", True SendKeys "{TAB}" SendKeys Chr(34) & "," & Chr(34), True SendKeys "%a", True SendKeys "%{F4}" Else x = 2 Set a = Cells(x, 1) Do While Len(a.Value) <> 0 inlist = inlist & a.Value & "," x = x + 1 Set a = Cells(x, 1) Loop Open "C:\inlist.txt" For Output As #1 Write #1, Left(inlist, Len(inlist) - 1) Close #1 ReturnValue = Shell("C:\WINNT\System32\Notepad.exe C:\inlist.txt", vbMaximizedFocus) AppActivate ReturnValue SendKeys "^a", True SendKeys "^h", True SendKeys ",", True SendKeys "{TAB}" SendKeys Chr(34) & "," & Chr(34), True SendKeys "%a", True SendKeys "%{F4}" End If End Sub
-
Here's some code I wrote today that I believe will be very reusable by me. With some very slight tweaking I suspect a lot of people can use it. As it is pasted below, the code takes data that is sorted by the first two columns (and the first two columns must be column A and column B) and scrolls through the rows. When it finds an instance where data is different in one of the two columns between rows, it copies all the like rows and pastes them in a new worksheet. Hopefully it comes in as handy for you as it does for me.
Code
Display MoreSub BreakApartLikeData() Dim oldprov As String Dim oldban As String Dim x As Integer Dim y As Integer Dim startrow As Integer Dim endrow As Integer oldprov = Sheet1.Cells(2, 1) oldban = Sheet1.Cells(2, 2) startrow = 2 For x = 3 To 17793 If Sheet1.Cells(x, 1) <> oldprov Or Sheet1.Cells(x, 2) <> oldban Then endrow = x - 1 Sheet1.Range("A" & startrow & ":A" & endrow).EntireRow.Copy Workbooks(1).Sheets.Add , After:=Worksheets(Worksheets.Count) ActiveSheet.Paste Destination:=Worksheets(Worksheets.Count).Range("A2") startrow = x End If oldprov = Sheet1.Cells(x, 1) oldban = Sheet1.Cells(x, 2) Next x End Sub
-
Re: Splitting a column of text into 2 Different Columns
This code will take the selected cells and split the names up placing the last name in the very next column followed by the first name in the column after that. It is contingent on there being only one comma in the selected cell.
-
Re: List files with Hyperlinks
I frequently receive inventory files from clients that have pieces of data within each file. I wrote some code that would search those files and return the file name to the comments of the cell containing the data but I would often have to manually go into the cell and copy/paste the file location to go find it. Slightly edited, this code will get put to use by me, probably on a daily basis.
Much appreciated. :music:
Here's my code if you're interested:
Code
Display MorePublic Sub FindFiles() searchfolder = InputBox("Enter the path to the folder to search", "Enter Path", "C:\Documents and Settings\<mylogin>\Desktop\") If searchfolder <> "" Then For Each c In Selection foundfilenames = "" With Application.FileSearch .NewSearch .LookIn = searchfolder .SearchSubFolders = True .MatchTextExactly = False .FileType = msoFileTypeAllFiles .TextOrProperty = c.Value If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then For i = 1 To .FoundFiles.Count If i < .FoundFiles.Count Then foundfilenames = foundfilenames & .FoundFiles(i) & vbLf Else foundfilenames = foundfilenames & .FoundFiles(i) End If Next i c.ClearComments c.AddComment foundfilenames c.Comment.Shape.TextFrame.AutoSize = True End If End With Next c End If End Sub
-
Re: AutoSizing Comments via VBA
I swear I tried that but I must've had something off because when I copied your code and pasted it, it worked like a champ.
Much appreciated Andy! :music:
-
I have written a macro that individually takes every cell in a selection and searches a folder on my harddrive for any file containing the value of the cell. The resulting list of files with matching criteria is then programmatically inserted into the comments of the particular cell whose value was found. The list is sometimes extensive. I need to find out how to programmatically set a cell's comment shape to autosize so that I don't have to individually show each cell's comment and then drag the corner to see all the files that contain the value of that cell. Sometimes I search in excess of 30 cells worth of values. The info I find in Excel's help is remarkably lacking. Any help on this would be great!
-
Re: Automated Module Importing
Kudos to Dave Hawley for posting a link to your proposed solution. I'll reiterate the link for those that find this thread. It is:
http://%22http//www.ozgrid.co…n-create.htm%22
I opened a new workbook which already had my user form and module (I use the user form as a menu for the module subs) attached and then saved it as an add-in (*.xla). I then closed Excel and reopened it to get a fresh spreadsheet. i went to Tools - Addins and checked the box by my new addin. Then I went to my VBA code and saw my new add-in was there. i removed the form and module from my Book1.xls project since it is now in my add-in. I then saved the workbook as Book.xlt in my Excel\XLSTART folder so that whenever I open it the code is available. More importantly, however, whenever I open any Excel file, my code is now available. -
I've spent some time creating a module that speeds my every day tasks. I've set up Excel so that it is attached whenever I open a new spreadsheet (Book.xlt). However, I am frequently emailed spreadsheets from others and would like the functionality of my module without having ot import the module everytime. How do I force Excel to attach/import my module from my hard-drive to any Excel file I open? I understand naming conventions could come into play in the event someone emails me a spreadsheet that already has "Module1" attached. I'm sure I can come up with a unique name for my module should the automated import process actually be possible. How 'bout it folks? Who's got a procedure to help?