Full Version: opening an excel report
UtterAccess Forums > Microsoft® Access > Access Forms
access_new
Hi, i need help i ha ve a form with comtrols and i need to add another button for opening a report in excel. But for some reason i coundt make it right..
Can anybody help me please.. the code in RED are my ocdes for the excel button...
There my code in the form:
CODE
Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.Hourglass True
Dim Criteria_ListBox As Control
Dim ProcessOptionGroup As Control
Dim QueryOptionGroup As Control
Dim y As String
Dim x As Integer
Set QueryOptionGroup = Forms![RUN CRITERIA]![QUERY GROUP]
'Set Criteria_ListBox = Forms![Run Criteria]![Criteria List Box]
Set ProcessOptionGroup = Forms![RUN CRITERIA]![PROCESS OPTION GROUP]
If UserSecurity = "A" Then
    Forms![RUN CRITERIA]![PROCESS OPTION GROUP].Visible = True
    Forms![RUN CRITERIA]![Text42].Visible = True
Else
    Forms![RUN CRITERIA]![PROCESS OPTION GROUP].Visible = True
    Forms![RUN CRITERIA]![Text42].Visible = True
End If
FirstTime = "YES"
PROCESSGROUP = ProcessOptionGroup
OptionGroup = QueryOptionGroup
Call QueryGroup
'y = MultiSelectFillArray()
'Call FillCriteria_Grid
'Criteria_ListBox.Requery
Call FillCriteria_Grid
Call Get_View_Query
'If PROCESSGROUP = 5 Then
'    x = UnGray_Menu_Item(3, 0)
'Else
'    x = Gray_Menu_Item(3, 0)
'End If
Forms![RUN CRITERIA].TimerInterval = 1
DoCmd.Hourglass False
DoCmd.SetWarnings True
End Sub
Private Sub Form_Timer()
Dim x As Integer
Dim ProcessOptionGroup As Control
Set ProcessOptionGroup = Forms![RUN CRITERIA]![PROCESS OPTION GROUP]
PROCESSGROUP = ProcessOptionGroup
'If PROCESSGROUP = 5 Then
'    x = UnGray_Menu_Item(3, 0)
'Else
'    x = Gray_Menu_Item(3, 0)
'End If
Forms![RUN CRITERIA].TimerInterval = 0
End Sub
Private Sub PROCESS_OPTION_GROUP_AfterUpdate()
Dim Process As Control
Dim Criteria_List As Control
Dim SpreadSheetOption As Control
Dim x As Integer
Set Criteria_List = Forms![RUN CRITERIA]![QUERY GROUP]
Set Process = Forms![RUN CRITERIA]![PROCESS OPTION GROUP]
Set SpreadSheetOption = Forms![RUN CRITERIA]![SpreadSheetOption]
PROCESSGROUP = Process
FirstTime = "YES"
If PROCESSGROUP = 1 Then
    Criteria_List = 1
    OptionGroup = 1
    SpreadSheetOption = 1
    SpreadSheetOption.Enabled = True
    Call QueryGroup
End If
If PROCESSGROUP = 2 Then
    Criteria_List = 1
    OptionGroup = 1
    SpreadSheetOption = 1
    SpreadSheetOption.Enabled = False
    Call QueryGroup
End If
If PROCESSGROUP = 3 Then
    Criteria_List = 1
    OptionGroup = 1
    SpreadSheetOption = 1
    SpreadSheetOption.Enabled = False
    Call QueryGroup
End If
If PROCESSGROUP = 4 Then
    Criteria_List = 1
    OptionGroup = 1
    SpreadSheetOption = 1
    SpreadSheetOption.Enabled = False
    Call QueryGroup
    DoCmd.OpenForm ("VIEW REASONABLENESS REPORTS")
End If
If PROCESSGROUP = 5 Then
    Criteria_List = 1
    OptionGroup = 1
    SpreadSheetOption = 2
    SpreadSheetOption.Enabled = False
    Call QueryGroup
End If
If PROCESSGROUP = 6 Then
    Criteria_List = 1
    OptionGroup = 1
    SpreadSheetOption = 1
    SpreadSheetOption.Enabled = False
    Call QueryGroup
End If
[color="red"]  
If PROCESSGROUP = 7 Then
    Criteria_List = 1
    OptionGroup = 1
    SpreadSheetOption = 2
    SpreadSheetOption.Enabled = True
    'Call tmpFQuery2
    Call QueryGroup
    'DoCmd.OpenForm ("VIEW EXTRACT 1 - SELECT YEARS AND COMPANIES")
    'DoCmd.OpenForm ("VIEW FORM1")
End If
[/color]
Call FillCriteria_Grid
'If PROCESSGROUP = 5 Then
'    x = UnGray_Menu_Item(3, 0)
'Else
'    x = Gray_Menu_Item(3, 0)
'End If
'Call Requery_Listbox
End Sub
Private Sub PROCESS_OPTION_GROUP_BeforeUpdate(Cancel As Integer)
End Sub
Private Sub QUERY_GROUP_AfterUpdate()
DoCmd.Hourglass True
DoCmd.SetWarnings False
Dim x As String
Dim Criteria_ListBox As Control
Dim Process As Control
Dim OptionG As Control
Set Process = Forms![RUN CRITERIA]![PROCESS OPTION GROUP]
'Set Criteria_ListBox = Forms![Run Criteria]![Criteria List Box]
Set OptionG = Forms![RUN CRITERIA]![QUERY GROUP]
FirstTime = "YES"
OptionGroup = OptionG
PROCESSGROUP = Process
Call QueryGroup
'x = MultiSelectFillArray()
Call FillCriteria_Grid
'Criteria_ListBox.Requery
'Forms![Run Criteria].Recalc
DoCmd.Hourglass False
DoCmd.SetWarnings True
End Sub
Private Sub RUN_GROUP_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.Hourglass True
Dim y As Control
Set y = Forms![RUN CRITERIA]![PROCESS OPTION GROUP]
ProcessOption = y
If ProcessOption = 2 Or ProcessOption = 3 Then
    Edits_Audits = "YES"
Else
    Edits_Audits = "NO"
End If
Calcul = ""
Call RunGroup
Forms![RUN CRITERIA]![RUN GROUP] = 0
DoCmd.Hourglass False
DoCmd.SetWarnings True
End Sub
NoahP
Welcome to Utter Access!
You can open a spreadsheet using the FollowHyperlink method very easily. Access Help should show you more detail, but it's pretty much:
FollowHyperlink "Pathtofilehere"
I do suggest you get rid of all the If-Then statements checking the variable 'Process Group' though. I'd use a Select Case (Acces Help will have more details again). Instead of what you currently have, I'd use:
'since all use the same Criteria_List and OptionGroup, leave them outside the Select-Case
'and only set it one time
Criteria_List = 1
OptionGroup = 1
Select Case Processgroup
case 1
SpreadSheetOption = 1
SpreadSheetOption.Enabled = True
case 2,3,4,6
SpreadSheetOption = 1
SpreadSheetOption.Enabled = False
case 5
SpreadSheetOption = 2
SpreadSheetOption.Enabled = False
case 7
SpreadSheetOption =2
SpreadSheetOption.Enabled = True
End Select
'since every option has Call QueryGroup at the end, put it outisde the Select-Case at the end, and call it only once
Call QueryGroup
'Use another select-case or if-then if only 2 options to open any appropriate forms now
Select Case ProcessGroup
Case 4
DoCmd.OpenForm ("VIEW REASONABLENESS REPORTS")
Case 7
'use FollowHyperlink here to open your Excel Spreadsheet
End Select
HTH
Noah
access_new
Hi Noah, thank you for your reply, I got what your trying to say but unfortunately, i cant change the if-then else statements because this is what the original code was. here is what exaclty what im doing, my boss gave me this complete DB and he just wants me to add another button to show another form that will have an option to get the report in an excel format.I made a separate form that has this, and for some reason i cant get it to open from the main form.
any help plzz..
thanks
NoahP
'getting the report in Excel format' is a different matter. From your original post, I thought you were wanting to just open an existing Excel spreadsheet.
pening a form should be straightforward with the DoCmd.OpenForm operation. Have you put in a break point in the code and stepped through it using the F8 key?
Noah
access_new
HI, sorry if i misled you. what i really wanted to do is to from my main form, i need to put a button that will open another form. And from that form, the user has to choose a certain company and yr. after that the user will open the result in an excel format. I made that form already. But when i tried to put that on the mian form by adding
"'DoCmd.OpenForm ("VIEW FORM1")", its giving me an error saying "you have no stored queries to run" but i already made a query. Im confused ... am i explaing it clearly though?
NoahP
Which exact line is highlighted in yellow?
oah
access_new
when i tested and run the form, click report in excel, it gives me a dialog box saying " you have no stored queries to run"
NoahP
So you're getting the error in Excel? Sorry, but I'm having a hard time following you on what, exactly, is happening.
oah
access_new
oh im sorry, ok what im trying to do is like this, i have a form1,this includes cmbo boxes that compars the values under company id and the yrs.and i also have 1 buton which is for showing the report in excel. This is working preety fine.
Then i hav to add this im my main DB. The codes for this one is the one i just posted. For some reason the button i just added as:
CODE

"If PROCESSGROUP = 7 Then
    Criteria_List = 1
    OptionGroup = 1
    SpreadSheetOption = 2
    SpreadSheetOption.Enabled = True
    'Call tmpFQuery2 (this is the query i made for the form1)
    Call QueryGroup
    'DoCmd.OpenForm ("VIEW EXTRACT 1 - SELECT YEARS AND COMPANIES")
    'DoCmd.OpenForm ("VIEW FORM1")
End If

This is not working when i tested it...
im also having a hrd time exaplaining so bare with me ok..thanks much..
freakazeud
Hi,
ok so lets see if we can streighten this out.
You have some sort of query which you want a user to restrict results with the use of comboboxes to choose a certain company and year. Am I on the right track? Do you have this part working?
Now when a user choses a value in the company and year combobox you want a button to be pressed which opens up the query results in an excel worksheet? Is that what you want!
We can defeniatly get that working, but I just want to verify your intensions before we all talk about something else!
HTH
Good luck
access_new
Hi freak, yeah u got it right. I got this form working and the query too. Im in the process of updating the old DB that my boss did before, and this includes putting another code so the user can open up a form that compard the values of each yr and comp_id and in the same time give the result iin an excel format.The main DB codes was the one i posted in my fist thread. And for some reason its not working...
thank you...did i explain it clearl =)
freakazeud
Ok,
so you have set up a form with two controls which are referenced as parameters in your query. And if you run the query and provide values on the form you get the correct results you want?
The only thing you have NOT working is to display these results in excel? Is that correct?
HTH
Good luck
access_new
not really, ok i have this main DB that my boss wants me to update. then i have a separate form which is FORM1 that includes two controls and button for excel report. I also have the query working for this form. My problem is , i need to incorporate this to the main DB. What i did was made a button that should open up the FORM1....
the code is the one i posted at the very first thread..but its not giving me the result i want..
freakazeud
Hi,
so do you have two seperate databases? The one "main db" and then one which has "FORM1" in it?
So basically you have everything on that form working, but don't know how to open the form?
HTH
Good luck
access_new
Im not sure if what i did was right though. what i did was i just copy the form1 and the query into the main db. then on the main form i added 1 button so this will open up form1. But its not giving me the result..
freakazeud
What was the query based on in your other database? Do you have that same table in the "main db" or did you import it into it as well?
What I would actually recommend is make a backup copy of the "main db", then create your form and query from scatch there and make it functional. Then just open it as noah mentioned earlier with docmd.open "yourformname" and it should work! You must have lost something on the way or some referencing in your query/form messed up.
HTH
Good luck
access_new
Actually i have the same table as what i have in my other DB with the main DB. But ill try to do what you told me and ill see if ill get errors..thank u for your help..
freakazeud
You're welcome.
Let us know if you run into anymore problems!
Good luck on future projects!
ScottGem
I'm going to jump in here on one point. You keep referring to a report in an "excel format". I think you need to clarify whether you want to open an Excel worksheet or just dispkay the results in a datagrid.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.