Open Args and export question

OasisUnknown

New Member
Joined
Oct 15, 2015
Messages
46
Hello Everyone,

I am trying to set up an export menu so that I can either export my report to PDF or export it to Excel.

I have the functionality for the PDF portion working but I wanted to expand to excel and I might be thinking about the whole process the wrong way.

Here is what I am currently doing.

1. When I have the report open I have a button on the report that says export. on click it will launch my export form as such
Code:
Private Sub ExportCmd_Click()
    Call ExportHandler(Me.Name)
End Sub

Public Sub ExportHandler(exportTarget As String)
    DoCmd.OpenForm "frmExportMenu", acNormal, , , , acWindowNormal, exportTarget
End Sub

2. On the form I have two additional buttons One says PDF the other says excel when you click the pdf button it launches this code
Code:
Option Compare Database
Option Explicit
Dim targetRpt As String
Private Sub Form_Load()
    targetRpt = Me.OpenArgs

End Sub
Private Sub ExportPDFCmd_Click()
    DoCmd.OutputTo acOutputReport, targetRpt, acFormatPDF
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub


This all works but the issue that I run into when trying to incorporate excel. Since the report itself and all its formatting wont export to excel and look very nice I was thinking I have to gain access to the reports record set and just export that query/table that the data is driven from.

but in order to export the record set I need to gab its name in a string variable and pass it to my export form.

This is where my open args question comes into play. Currently I just pass the report name through the open args but can you pass more than one variable through the openargs. from what I have been reading the answer would be no.

I might be able to pass a string array through the open args but I am not really sure how to access that content on the other side in the form load call.


Am I going about this all wrong or does any one have any suggestions about what I could be doing better?
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
If the record source for your report is a named query (which you can rewrite as the criteria for the report are selected), you can export the query to Excel.

Denis
 
Upvote 0
This is not a comment on the suggested answer, rather it applies to the specific question on the open args.
The only way to pass multiple values (that I know of) would be as character separated values or a concatenation thereof. For this purpose, I'd use the Pipe | (some say redirect pipe) as a delimiter rather than a comma, if there is the possibility that any of the values were input by a user. They could enter a comma that ends up in the string as well, which could mess things up. Your form code would have to either address each possible combination of values as an entire string (if there were only 2 or 3 possibilities) or call a procedure that parses the string using the delimiting character and decides what to do with it. In the simpler case, it might look like:
Code:
If Me.OpenArgs = "apple|bannana" Then
  do stuff
ElseIf Me.OpenArgs = "apple|orange" Then
  do stuff
ElseIf Me.OpenArgs = "orange|bannana"
 etc.
Obviously not very elegant (even if written as a Select Case statement), doesn't deal with Nulls or empty strings and would exponentially increase as you add elements. A function or sub might be more practical.
Function ParseIt (strArgs as String)
do stuff
ParseIt = 'the results'
End Function
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top