Recently, I exported an Access Report to Word (.RTF). Nope, did not use Albert’s Super Easy Word Merge because the nature of the report with its many Subreports and Sub-Subreports did not lend itself to a mail merge. Only after I exported it I needed to first copy it in its entirety to another document and then remove a line from the original document.
Function strDeleteLine(strPath As String)
'http://www.access-diva.com/tips.html
Dim objWord As Object
Dim objDoc As Object
Dim objSelection As Object
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open(strPath)
objDoc.Range.Select
objWord.Selection.Find.Text = "Beginning text of the line you want to delete"
Do While objWord.Selection.Find.Execute(FindText:="Beginning text of the line you want to delete", Forward:=True, Format:=True) = True
With objWord.Selection
'wdLine = 5
.Expand Unit:=5
.Delete
End With
Loop
End Function
Generally, I store the path to my file and the name of the File in two separate fields. I do this because if there is a likelihood that the database will be upsized to SQL Server the Hyperlink data type in Access is not supported and I’d have to do it anyway. Not a big deal as in Access as I can use…
However, it is a problem if I then want to export then to Adobe, Word or Excel and keep the links clickable. To do that requires a little trickery (been wanting to use that word!).
For Adobe (.PDF) and Word (.DOC or .DOCX)…
In your Recordsource (should be a Query) for your Report create a field:
In the Property Sheet for the Control in the Format tab…
Is Hyperlink to Yes Display As Hyperlink to If Hyperlink
Now while in Preview of the Report you can select PDF or XPS or Word from the Data tab and your links will be clickable and display whatever text you put in [FieldThatHoldsNameOfDocument].
For Excel (.XLS or .XLSX)…
I tend to use Excel Automation because I can control a lot more. I also use Templates to I don’t have to worry about Formatting. In this Template I have two hidden fields that hold the Path (column B) and the Name to Display (column C), column F will display the Hyperlink.
The first thing to is tell it how many rows are in your Recordset. Then I need to add which Row I am starting on. In my case I am starting at Row 5 so I need to add 4 to my Recordset.
Add to your declarations
Dim lngLastRow As Long
Then under the CopyFromRecordset line add, this is so we can get the row count to apply our formula to
‘Add the number of rows from the top the Recordset will be copied from
lngLastRow = rst.RecordCount + 4
Now, for the Formula which you want to be sure to add after your data has pasted, so don’t add before the CopyFromRecordset…
'Column F to display the Hyperlink, column B the Path and column C the Text I want to Display
With xlWSh
For i = 5 To lngLastRow
xlWSh.Cells(.Rows.Count, 6).End(xlUp).Offset(1, 0).Formula = "=HYPERLINK(B" & CStr(i) &", C" & CStr(i) & ")"
Next I
End With
When you open the Excel spreadsheet your links will be clickable.
Yep, you read that right… Microsoft has opened up a UserVoice site where YOU can suggest features or improvements you’d like to see in Microsoft Access Desktop and Web Apps, as well as, vote on suggestions made by others. This is a great opportunity for YOU to pass on your ideas directly to the Product Team. Have a look… Welcome to Access Suggestion Box!
Where I live driving is a neccessity, however, it does not mean you own the road! So, here’s my *gripe* about those folks that make me believe you really can get your license in the mail!
Your Turn Signal was not an option, every vehicle has one, use it! You may know where you are going but I don’t and..
…for those of you who do use your Turn Signal it does not give you permission to just move over. I’m on the road also and I like my car!
Driving around with one (or more) head/rear lights out is a hazard. Not just for you but for everyone else, we don’t know if that’s a car or a motorcycle we are passing. Replace the bulb!
The Speed Limit is not a suggestion, it’s the law, so, let’s stop running those who obey the posted limit off the road.
A Muffler making that much noise on any vehicle is not cool at 1:00 AM or any other time.
Your Car Radio/CD/DVD Player is for your enjoyment, turn it down.
Throwing trash out the window is unacceptable. Put a trash bag in your car or wait till you get home.
Bluetooth, folks, bluetooth! (In some states it’s a $500.00 fine to be using anything but Bluetooth. That should be the fine in every state as far as I’m concerned!)
Apparently, the *Don’t Text and Drive* hasn’t gotten thru to everyone! Pull over to the side of the road if you must but, honestly, it can wait till you get someplace where you are not driving. (Oh, and this also goes for you folks who think using their tablet/iPad/laptop isn’t as dangerous!)
And finally, need I say more?
Well, those are my pet peeves, I’ll be stepping off my soap box now.
Ever need to create a few instructions for your database? Or maybe you just want to include some assistance to the End User for easier data entry. This database allows you to do just that. While not designed for extensive *Help* it does give you the ability to provide information to your End User.
To use, import all the Objects into your database and open frmInputHelp pictured below…
Once you have filled in all the information you can take a look at it to see what your User is going to see by selecting the *Preview* button, the one to the utmost left. You can also add your own images to the file if you don’t want the question mark. (The image is per record.)
At the bottom you will see the *Showing on* Combo Box. I use this along with the ID to pull up the individual Help menus, see below. While it is not required (or needed), I find it helpful in larger databases when looking for where I put one specific menu. If I am just looking for a number I would have to pass thru every *9* I entered anywhere, while if I look for the Form Name I have a much shorter find.
You can also put the frmInputHelp under the File Maintenance section in your databases. I do not allow the addition of new ones; however, this will give your Users the chance to edit/update existing Help files.
Access MVP (2010-2015)
Recent Comments
No comments.