I have a spread sheet which on a per line basis contains columns whose cells contain the path to a file. It looks as if many of the files are tif, pdf, word and excel etc, so multiple file types.
Writing a routine which would take each row and then for each line of file paths turn them into one multi-page document - pdf or something?
I have a sales report that I generate each week based on a query, which relies on the input from a combo box on a form. This works great - but I have select each salesperson, one at a time and run their reports separately.
I'd like to have some code that will run through each person on the list, generate their report, create the PDF, and email (or save) the file.From my initial research, it appears I will need to utilize a List Box (vs a combo box), so I can select multiple salespersons for the report.
I've found a few examples of code to use...but a little lost on how to implement. I create a new form with a list box that queries the property table for the list. I then added a button to execute the code, and attempted to add this (with my control and reports names substituted). Getting all sorts of errors -- so not sure I'm on the correct path.
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003 with a Combo Box
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![ YourControl ]
'If Your Data Type is Numeric use this section
[code]...
I'm a newbie and using Access 2007.
I have added functionality that creates a report, saves it as a PDF, and then sends it to a customer via email as an attachment. The PDF file is saved to a folder and is named to include the Customer's unique customer number and the date and time the PDF was created.
For example, if the customer number is 09 and the date was 10th October 2013 and the time 09.00am, the file name is 0009101020130900.pdf.
What I want to be able to do from within the database is to have a button, that when clicked, will display all of the Pdf's for only customer number 09. The customer number will be based upon a field in the customer record.
I know it's a long shot, but, in A2010 I'm trying to convert a series of Word doc files into pdf, BUT then assemble them into one large PDF. It's for a sort of archiving system.
way I can do this, preferably just using the PDF capabilities within Access and without purchasing any Adobe Pro software.
Am using below code for generating set of pdf files in a desired folder..similarly I have another code, which generates another set of pdf file in another folder.. Count and Filename is identical in both the folders and I need to merge the file names with identical names together....separate binder file would also work..
Private Sub Command14_Click()
Dim strDefaultPrinter As String
strDefaultPrinter = Application.Printer.DeviceName
Set Application.Printer = Application.Printers("Adobe PDF")
[code]....
Is it possible to create a record set from a list box?
I have two list boxes list1 (customers) the can transfer records to list2. I want to take all records from list2 and use it to open a report, using customer id as where clause in my docmd.openreport statement.
I would like to read a TIFfle to ACCESS, and store its content as hexadecimal like below to further other process..., how can I achieve this change and storage??
0000: 49 49 2A 00 4E 00 00 00 80 3F E0 50 38 24 16 0D
0010: 07 84 42 61 50 B8 64 36 1D 0F 88 44 62 51 38 A4
0020: 56 2D 17 8C 46 63 51 B8 E4 76 3D 1F 90 48 64 52
0030: 39 24 96 4D 18 80 80 00 60 00 00 00 01 00 00 00
0040: 60 00 00 00 01 00 00 00 08 00 08 00 08 00 0F 00
0050: FE 00 04 00 01 00 00 00 00 00 00 00 00 01 03 00
0060: 01 00 00 00 11 00 00 00 01 01 03 00 01 00 00 00
0070: 0F 00 00 00 02 01 03 00 03 00 00 00 48 00 00 00
0080: 03 01 03 00 01 00 00 00 05 00 00 00 06 01 03 00
I'm trying to create a button that will open a save window so that user can select where and the name of a new backend file.
and create a new backend file from the one in use and link to the new open.
I've created an accounts package for my business using access 2003 and I'm just thinking of some time saving features.
We need to email our invoices to our clients.
Normally we print out an invoice, scan it back into the computer, save as a PDF and attach to an email manually one by one.
My package can now print to PDF (saving one step), but what I would like is to be able to print to PDF and attach to a new email (with the email address coming from the customer table) and possibly attach more than one PDF to a single email.
I know you can use
Code:
DoCmd.SendObject acReport, stDocName
to send a report but I would like it as a PDF as my formatiing is lost using that code and we normally send PDFs to our clients.
I have a User Defined Type which stores an array..I have 2 instances of this UDT, one storing matched data, the other storing exception data..I was to parse these arrays to an Excel output routine. However, the parameter variable contains no data when accessing it..
Here is my code:
Code:
Private Type TOutputRow
RACFID As String
FullName As String
Access As String
LastLoggedIn As Date
End Type
[code]...
What I'd like to do is have an "export button", where the OnClick event, exports a single table into a new accde file. This would allow the end users to zip this file up and send it to me.
Because of all sorts of stupidity, I have no network capability and must send data back and forth via email.
I have digressed to a replication table that needs to be uploaded, once data is entered by the users...
Because the files can get relatively large (for emailing purposes), I am trying to figure out how I can get just one table from them vs. sending the whole application file back to me... It's pretty vital that they not be able to edit the table because that could really mess up the process.
So any way to create a new lock file from within the current Db?
I have a VBA routine that runs some queries and then creates a report as a PDF document using my virtual PDF printer. At this point it brings up the Save As dialog and I give the file a name and select a folder to store it in.
But I run this report 30 times for 30 different parameters. The parameters are obtained from a table which contains values such as Cancer, Diabetes, Stroke
-I always name the PDF file according to this value.
-I always choose the same folder.
-I always overwrite the reports I produced last month (copies have been sent elsewhere by then).
how can I get VBA to cycle through this code 30 times, each time selecting the next value from my table and creating/overwriting those files without the need for me to tell it the filename and path?
Code:
Private Sub lstSpecialties_DblClick(Cancel As Integer)
At the moment I am running this code by double-clicking an entry in the Specialty listbox, 'but I would prefer the whole thing to run multiple times for as many Specialties as exist in the table '(currently 30 but could be more in time)
'Warnings off
DoCmd.SetWarnings False
'Make the tables needed for the cumulative queries
DoCmd.OpenQuery "Spec 002 Monthly recruits - part 2 - make table" Each of these queries
DoCmd.OpenQuery "Spec 005 Monthly recruits - part 2 - make table" uses the Specialty selected above
DoCmd.OpenQuery "Spec 022 ABF previous year - part 2 - make table" as a parameter to get
DoCmd.OpenQuery "Spec 025 ABF current year - part 2 - make table" the right data for the report
[code]....
I'm looking to use the shell function to dynamically call different pdfs that are in a directory. However I'm getting run time error 5 "Invalid procedure or call argument".
Here is the code (very basic I know)..
Private Sub Liste_Documentation_DblClick(Cancel As Integer)
Dim PathName As String
PathName = Me.Liste_Documentation.Column(2)
' Debug.Print PathName
Shell PathName
End Sub
A typical filepath name is as follows..
S:VenteVendeursMarcCRMDOCSDiligences KYC - LABFT - V 2013 04 23.pdf
If I have an XML file, I can load it into an XML document and parse it.
For an HTML file, I have been unable to find similar parsing methods - that is, using DOM and not involving a browser (other than simply treating the file as text). I'd prefer to avoid the browser control because it is different for a number of different Access versions, so the only option remaining that I see is to run Internet Explorer with visibility off.
Can one parse HTML using the DOM without a browser?
I have a folder which holds 1000s of pictures organised in subfolders, for example:
D:~AI Database Print Scans2009family and
D:~AI Database Print Scans2009holiday
And so forth. In total at the moment 17 main subfolders, each of which hold another 2-3 subfolders.
I am putting together a database to bring pictures together with all sorts of details. I import the picture via hyperlink and complete the various fields. All that works fine but there are a lot of pictures! And it gets confusing to see which ones have already been entered into the database and which ones haven’t.
One solution for this is to copy the pictures that have been “completed” to another folder. I have found a way to do that:
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = Forms!frmPrintDetails.txtPath1
newPath = "D:~AI Database Print ScansCompleted_Entries"
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath, newPath
Set fs = Nothing
Works fine, but the problem is that I loose the subfolder structure, it copies all pictures to the same main folder. I’d like to maintain the subfolder structure and add code to create the correct subfolders and next copy the picture. With the different paths I’m lost. I don’t know how to extract the correct path in code. I’d also like to either rename or remove the picture once I have completed entry of the details. The problem I have again is that I don’t know how to code for a changing path.
I want to create a Macro saves the report in a specific location, and uses two fields within the report to generate the name of the file. For example, "[Sales Rep Name] + [PayPeriod].pdf" and it should be saved in a predetermined folder.
View 1 Replies View RelatedAccess 2003
I have a process which imports a txt file into a memo field of my TBLORIGINAL table Each file may have one record or it may have multiple records Every record begins (exactly) with
Quote:
FRE Order Order RAD ShipDate Customer City State Postal Code
For each record in the memo field, I need to create a new record into my TBLPROCESSED table
This can probably be done while it is importing but I need to keep the original file intact
I suppose I could copy and paste but there must a vba way of doing this
I'm working on a database which is designed to connect to an app called Canvas (www dot gocanvas dot com). It's for a client which runs a team of heating engineers. All the information regarding the engineer's job is uploaded to Canvas through their API as an XML file, and the engineers use an app on tablet to view the job details. When the engineer goes to their customer and does some work, the completed info (which parts were serviced etc) is then downloaded as an XML file via the API into the database. Also, the customer signs on the engineer's tablet to confirm that the work has been done.
Although the XML files for the job data upload and download fine, there's a different API for getting a download of the JPEG which contains the customer signature, and I'm having real problems finding out how to use this.
The API guide says this:
----
The Submissions API is restricted to authenticated users and requires a username and apassword to access. In addition, the ID field is required. If these fields aren't specified, an error is returned.
The result of this webservice invocation will be either a standard JPEG image or an error code.
Example: [URL] ....
All my code (which I've pasted at the foot of this message seems to work fine. There are no errors, and the ResponseBody object appears to contain a byte stream which would be the jpeg... if only I could get it into a jpeg file!
How I go about taking this "stuff" that's come back in the responsebody and actually create a jpeg image file from it? I feel that I'm so close that I can smell it, but can't get the last step!
Here's the existing code :
Code:
Sub DownloadImageFile()
Dim xhr As Object
Dim webServiceURL As String
Dim actionType As String
Dim PostData As String
Dim strResult As String
[Code] ....
In appendix is .mdb file with this thema.
Inside you can find one form with listbox (with multi selecting ability).
I use this code :
Dim strSQL As String
Dim i As Variant
With Me.se1
For Each i In .ItemsSelected
SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;"
CurrentDb.Execute (SQL)
Next
End With
Me.se1.Requery
Result is this one :
1) If I delete only one item in list, it is ok. I can do it again and again and it is working fine.
BUT
2) If I delete more items in list at once, it is ok - but if I will try to do it again then there is an error because .ItemData(i) value is Null.
I've built an HTML web form that sends an email to me upon submission with all the field inputs. I have previously found success using VBA in Outlook to parse out these emails and feed the substrings into an Excel Sheet (hooray!), but now am looking to feed the substrings into Access, specifically into a table in an existing .mdb file. I figure the way Outlook will "talk to" Access will likely be a bit different from the way it does for Excel.
The way my previous script engaged with Excel was through a rule; every time a new email was received, the following script would run if the message had the subject line generated by my web form. The script would check whether Excel and the target workbook/sheet was open, act accordingly, and then input the substrings (I've excluded that part below), then return Excel and the wb/ws to their initial state. I imagine though, that with Access I might be able to feed the substrings into the .mdb without having to open it proper, the way a front-end talks to a back-end. But perhaps I'm mistaken on that front.
Code:
Option Explicit
Option Compare Text
Public Const xlUp As Integer = -4162 'I genuinely do not know what this is _
about. It came with the initial _
borrowed code.
Public Const wbPath As String = "C:...Workbook.xlsx" 'Workbook path
[Code] .....
I am trying to create some code for a button in a report that will follow a hyperlink to a specific file. The problem I'm having is that the files that are at the end of the hyperlink can have various extensions (*.doc, *.docx, *.pdf, etc.) I'd like to be able to put a wildcard in the code to allow the opening of the file regardless of the extension.
Code so far:
Private Sub Command6_Click()
Application.FollowHyperlink ("C:UsersjbeggDocumentsAccessTestFolder" & [FileName] & ".*")
End Sub
I'm using Access 2007.
So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.
I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.
Here is some of my code:
Code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean
[Code] ....
Here's my current query in SQL:
Then finally how to I get the query to execute and create a report based on all of this?
Hi All a newbie here so any help will be appreciated,
sorry for the long post but trying to give you all the information you might need.
I wrote a basic access database for my Church to aid in a paperwork audit for a charity food drop which we do monthly to give free food to the needy.
But each month it gets harder to find out who was in line first so I thought with all your help we may be able to randomize the names each month in a different order as to avoid confusion and also avoid people waiting in line as they turn up at 5am and we don't start until 9am.
So if this will work in access they can all come for 9am
I don't mind creating a new database and adding the additional information, if that's what it would take.
My Background I have created basic databases from scratch not using wizards, But I don't know much about code or how to implement it so any help in where code goes it would be very much appreciated.
Database details (Microsoft Access 2002 version)
Table Name = details
Field name = ID (auto-generated)
Field name = FirstName (text)
Field name = Surname (text)
If possible it would be nice to keep a record of the randomized lists (in the database somewhere ?) each month in case anyone wants to see it or disputes the lists, where I can just create a report to show the details.
There will be approximately 90 to 125 names.
Thank you in advance for all your help in this matter
Britgent
I use office 2003, first I had to convert my database, then I did the database splitting then, remove all menus from running at startup, I tried to create an mde file but it didnt work.
This is what I got:
This error is usually associated with compiling a large database into an MDE file. Due to the method used to compile the database, a considerable number of TableID references are created for each table. The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Jet database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.
So installed office XP and tried the same steps but still not working.
I am trying to get an access report to save a pdf file, then with a control button create and e-mail and attach the email and send it. Has anyone coded anything like this?
View 1 Replies View RelatedHi all,
Before I start, please know that I am what you call a newbie to a certain extent even though I created in the last 3 years a very complex and efficient database for my business.
This is going to be hard to explain, but I'll try.
The time came to stop creating the Price List in Excel and copy and paste in Access.(mainly to make it easy for other person to maintain and understand)
I know more or less the tables that will need to be created.
My problem is how to store calculated fields in a table (I know I really shouldn't, but how else can I accomplish this), since all the costs and selling prices are the result of complex formulas.
If this information is not enough to understand my question please let me know.
Thank you for any help.
Emilio