How would I run this code in Access 2007?

I successfully ran the code below in an Access 2007 DB about 4 years ago. It exports the results from multiple queries
into an excel file as one sheet/query. When I attempt to run it from a module I created, nothing happens now.
Embarrassingly, I may have forgotten some important detail necessary to run the code.
Please advise. Thanks


Option Compare Database

Private Sub ExportQueries()


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "A_DEMOGRAPHICS", "C:\eDIGSdata.xlsx", True, "A_DEMOGRAPHICS"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "B1_MEDICAL_HISTORY", "C:\eDIGSdata.xlsx", True, "B1_MEDICAL_HISTORY"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "AA_ATTENTION_DEFICIT_HYPERACTIVITY_DISORDER", "C:\eDIGSdata.xlsx", True, "AA_ATTENTION_DEFICIT_HYPERACTIVITY_DISORDER"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "C1_MODIFIED_MINI_MENTAL_STATUS_EXAMINATION", "C:\eDIGSdata.xlsx", True, "C1_MODIFIED_MINI_MENTAL_STATUS_EXAMINATION"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "C2_TELEPHONE_INTERVIEW_FOR_COGNITIVE_STATUS", "C:\eDIGSdata.xlsx", True, "C2_TELEPHONE_INTERVIEW_FOR_COGNITIVE_STATUS"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "E_OVERVIEW_OF_PSYCHIATRIC_DISTURBANCE", "C:\eDIGSdata.xlsx", True, "E_OVERVIEW_OF_PSYCHIATRIC_DISTURBANCE"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "F_MAJOR_DEPRESSION ", "C:\eDIGSdata.xlsx", True, "F_MAJOR_DEPRESSION"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "G_MANIA_HYPOMANIA1 ", "C:\eDIGSdata.xlsx", True, "G_MANIA_HYPOMANIA1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "G_MANIA_HYPOMANIA2 ", "C:\eDIGSdata.xlsx", True, "G_MANIA_HYPOMANIA2"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "H_DYSTHYMIA_CYCLOTHYMIA ", "C:\eDIGSdata.xlsx", True, "H_DYSTHYMIA_CYCLOTHYMIA"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "I_ALCOHOL_ABUSE_AND_DEPENDENCE", "C:\eDIGSdata.xlsx", True, "I_ALCOHOL_ABUSE_AND_DEPENDENCE"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "J_TOBACCO_MARIJUANA_AND_OTHER_DRUG_ABUSE_AND_DEPENDENCE", "C:\eDIGSdata.xlsx", True, "J_TOBACCO_MARIJUANA_AND_OTHER_DRUG_ABUSE_AND_DEPENDENCE"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "K_PSYCHOSIS", "C:\ eDIGSdata.xlsx", True, "K_PSYCHOSIS"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "N_COMORBIDITY_ASSESSMENT", "C:\eDIGSdata.xlsx", True, "N_COMORBIDITY_ASSESSMENT"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "O_SUICIDAL_BEHAVIOR", "C:\eDIGSdata.xlsx", True, "O_SUICIDAL_BEHAVIOR"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "P_ANXIETY_DISORDERS", "C:\ eDIGSdata.xlsx", True, "P_ANXIETY_DISORDERS"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "P_POST_TRAUMATIC_STRESS_DISORDER", "C:\eDIGSdata.xlsx", True, "P_POST_TRAUMATIC_STRESS_DISORDER"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"PP_POST_TRAUMATIC STRESS_DISORDER", "C:\eDIGSdata.xlsx", True, "PP_POST_TRAUMATIC STRESS_DISORDER"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "A_DEMOGRAPHICS", "C:\eDIGSdata.xlsx", True, "A_DEMOGRAPHICS"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "R_PATHOLOGICAL_GAMBLING", "C:\eDIGSdata.xlsx", True, "R_PATHOLOGICAL_GAMBLING"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    " S_ANTISOCIAL_PERSONALITY ", "C:\eDIGSdata.xlsx", True, " S_ANTISOCIAL_PERSONALITY "
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"T_GLOBAL_ASSESSMENT SCALE _GAS ", "C:\eDIGSdata.xlsx", True, " T_GLOBAL_ASSESSMENT SCALE _GAS"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "X_INTERVIEWERS_RELIABILITY_ASSESSMENT ", "C:\eDIGSdata.xlsx", True, "X_INTERVIEWERS_RELIABILITY_ASSESSMENT"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "Y_NARRATIVE_SUMMARY ", "C:\eDIGSdata.xlsx", True, "Y_NARRATIVE_SUMMARY"




End Sub
BiopsychAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
to run the codes anywhere in your app

change Private to Public

 'Private Sub ExportQueries()

should be

Public Sub ExportQueries()



do you have the excel file   "C:\eDIGSdata.xlsx" ?  in the C; drive
0
BiopsychAuthor Commented:
Thanks for your input. Problem is that  Windows 7 will not allow me to move the eDIGSdata.xlsx file to C:\
Will only let me drag into a folder and prefer not to change pathway code.
Should be simple to just drag excel file to C:/
Know anything about this issue?

Thanks,
0
PatHartmanCommented:
Rather than hard-coding the full path/file name, make it a variable.  Then you can have the user choose a path and you can concatenate that with your hard-coded file name.  And all the statements will use the variable name.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Problem is that  Windows 7 will not allow me to move the eDIGSdata.xlsx file to C:\
That's most likely due to User Access Control (UAC), and you can't get around that without the user modifying their system (and that's never a good idea).

If that's the case, you'll need to save the file somewhere else, like the users Documents folder, or the App Data.
0
Rey Obrero (Capricorn1)Commented:
use this codes

Public Sub ExportQueries()
dim xlFile As String
xlFile = Environ("userprofile") & "\Documents\eDIGSdata.xlsx"  

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "A_DEMOGRAPHICS", xlFile, True, "A_DEMOGRAPHICS"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "B1_MEDICAL_HISTORY", xlFile, True, "B1_MEDICAL_HISTORY"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "AA_ATTENTION_DEFICIT_HYPERACTIVITY_DISORDER", xlFile, True, "AA_ATTENTION_DEFICIT_HYPERACTIVITY_DISORDER"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "C1_MODIFIED_MINI_MENTAL_STATUS_EXAMINATION", xlFile, True, "C1_MODIFIED_MINI_MENTAL_STATUS_EXAMINATION"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "C2_TELEPHONE_INTERVIEW_FOR_COGNITIVE_STATUS", xlFile, True, "C2_TELEPHONE_INTERVIEW_FOR_COGNITIVE_STATUS"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "E_OVERVIEW_OF_PSYCHIATRIC_DISTURBANCE", xlFile, True, "E_OVERVIEW_OF_PSYCHIATRIC_DISTURBANCE"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "F_MAJOR_DEPRESSION ", xlFile, True, "F_MAJOR_DEPRESSION"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "G_MANIA_HYPOMANIA1 ", xlFile, True, "G_MANIA_HYPOMANIA1"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "G_MANIA_HYPOMANIA2 ", xlFile, True, "G_MANIA_HYPOMANIA2"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "H_DYSTHYMIA_CYCLOTHYMIA ", xlFile, True, "H_DYSTHYMIA_CYCLOTHYMIA"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "I_ALCOHOL_ABUSE_AND_DEPENDENCE", xlFile, True, "I_ALCOHOL_ABUSE_AND_DEPENDENCE"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "J_TOBACCO_MARIJUANA_AND_OTHER_DRUG_ABUSE_AND_DEPENDENCE", xlFile, True, "J_TOBACCO_MARIJUANA_AND_OTHER_DRUG_ABUSE_AND_DEPENDENCE"

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "K_PSYCHOSIS", "C:\ eDIGSdata.xlsx", True, "K_PSYCHOSIS"
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "N_COMORBIDITY_ASSESSMENT", xlFile, True, "N_COMORBIDITY_ASSESSMENT"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "O_SUICIDAL_BEHAVIOR", xlFile, True, "O_SUICIDAL_BEHAVIOR"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "P_ANXIETY_DISORDERS", "C:\ eDIGSdata.xlsx", True, "P_ANXIETY_DISORDERS"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "P_POST_TRAUMATIC_STRESS_DISORDER", xlFile, True, "P_POST_TRAUMATIC_STRESS_DISORDER"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
 "PP_POST_TRAUMATIC STRESS_DISORDER", xlFile, True, "PP_POST_TRAUMATIC STRESS_DISORDER"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "A_DEMOGRAPHICS", xlFile, True, "A_DEMOGRAPHICS"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "R_PATHOLOGICAL_GAMBLING", xlFile, True, "R_PATHOLOGICAL_GAMBLING"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     " S_ANTISOCIAL_PERSONALITY ", xlFile, True, " S_ANTISOCIAL_PERSONALITY "
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
 "T_GLOBAL_ASSESSMENT SCALE _GAS ", xlFile, True, " T_GLOBAL_ASSESSMENT SCALE _GAS"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "X_INTERVIEWERS_RELIABILITY_ASSESSMENT ", xlFile, True, "X_INTERVIEWERS_RELIABILITY_ASSESSMENT"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
     "Y_NARRATIVE_SUMMARY ", xlFile, True, "Y_NARRATIVE_SUMMARY"

 End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BiopsychAuthor Commented:
Capricorn,
Thanks for taking the time to edit my code. Will test as soon as I can.
0
John TsioumprisSoftware & Systems EngineerCommented:
As a note...instead of having a massive code to do query exports you could use some kind of iteration for all the queries that match a naming criteria and avoid the above
e.g.  queries that are eligible for export have a naming like this : queryName+"expxls"
So you have a code like this
Dim qdf as querydef
Dim db as Database

set db = currentDb

for each qdf in db.QueryDefs
 if instr(qdf.name,"expxls")>0 then
      'Your export code
end if
next

Open in new window

0
BiopsychAuthor Commented:
Capricorn1

When I was testing your edited code, I noticed that the names of the qrys coded to be exported to the xls file
had hyphens not underscores in their names.  After correcting this, the code ran.
Although your code did not actually solve the issue, I will give you most of the points as
you indirectly helped me find the problem.  Thanks for taking the time to address this issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.