Dumping multiple Excel output files with template and folder automation

I have created an Access 2003 database and I have created a template in Excel to export my information into through a command button. The action that it takes is that it exports all records into the correct cells and creates a file name depending on a textbox in the database. What I would like for this export command to do is Name the file, Create a Folder named Risk Scorecard and Put the template in that folder also. I would also like to have the option to export EVERY record as seperate Excel files into that folder. Right now I have to manually create the folder and place the template into it. Here is the code that I have so far:  

Private Sub cmdDownloadToExcel_Click()
On Error GoTo Err_DTE


    Dim xlApp As Object
    Dim xlWB As Object
    Dim oSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
   
    Set xlWB = xlApp.Workbooks.Open("c:\risk scorecard\Projected Template V3.xls")
    Set oSheet = xlWB.Sheets("Composite Risk Score")
    Set iSheet = xlWB.Sheets("Financial Scoring")
    Set aSheet = xlWB.Sheets("Operational Scoring")
    Set eSheet = xlWB.Sheets("Country Risk")
    Set uSheet = xlWB.Sheets("Compliance Risk")
   
    oSheet.Range("Name_of_the_Supplier").Value = Me!txtThird_Party_Name
    oSheet.Range("Category").Value = Me!Category
    oSheet.Range("Report_Period").Value = Me![Risk Scorecard Qtr.]
    oSheet.Range("Tier").Value = Me!Tier
    uSheet.Range("Medium_CAT_Open").Value = Me!txtTPM_QA_Medium_Open_Issues_CAT
    uSheet.Range("Low_CAT_Open").Value = Me!txtTPM_QA_Low_Open_Issues_CAT
    uSheet.Range("High_Closed_CAT").Value = Me!txtTPM_QA_High_Closed_Issues_CAT
    uSheet.Range("Medium_Closed_CAT").Value = Me!txtTPM_QA_Medium_Closed_Issues_CAT
    uSheet.Range("Low_Closed_CAT").Value = Me!txtTPM_QA_Low_Closed_Issues_CAT
    uSheet.Range("Sub_Total_TPM_QA_Issues_CAT_Issues").Value = Me!    txtSubtotal_Open_and_Closed_TPM_QA_Issues_CAT
    uSheet.Range("Past_due_TPM_Activities").Value = Me!txtPast_Due_TPM_Activities
    uSheet.Range("Number_of_past_due_Activities").Value = Me!txtNumber_of_Past_due_Activies
    uSheet.Range("Subtotal_TPM_Activities").Value = Me!txtSubtotal_of_Past_due_Issues
    uSheet.Range("Compliance_Risk_Component_Score").Value = Me!txtComposite_Risk_Component_Score

Exit_DTE:
    xlWB.SaveAs "c:\risk scorecard\" & Me![Risk Scorecard Qtr.] & Me!txtThird_Party_Name & Me![Risk Scorecard] & ".xls"
    xlWB.Close
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
   
    Exit Sub
Err_DTE:
    MsgBox Err.Description
    Resume Exit_DTE:
   
End Sub
Trey31Asked:
Who is Participating?
 
William ElliottSr Tech GuruCommented:
copy the code below in a module and see what it does.
sub exportstuff()
	Dim dbs As DAO.Database
	Dim rst As DAO.Recordset
	Dim sSQL As String
	 
	sSQL = "SELECT * FROM [Supplier Information]"
	Set dbs = CurrentDb
	Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
	If Not rst.BOF Then rst.MoveFirst
	 
		Do Until rst.EOF
			call cmdDownloadToExcel
		Loop
end sub

Open in new window

0
 
curioustoknow2007Commented:
you just need to create a script that will do this for you easily.
0
 
Trey31Author Commented:
That is what i am asking advise on. I am stuck at this point and I have been working on this for hours not getting anywhere. Please advise.
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.

 
William ElliottSr Tech GuruCommented:
if the folder doesn't exist create it

   If Dir("c:\risk scorecard\", vbDirectory) = "" Then
        MkDir ("c:\risk scorecard\")
    End If


same with the file

   If Dir("c:\risk scorecard\template.xls", vbDirectory) = "" Then
       FileCopy "c:\som place\template.xls", "c:\risk scorecard\template.xls"
    End If
0
 
Trey31Author Commented:
Ok this is great this works. Thank you very much!  As for the command 'cmdExport_All_Records' so that there is an excel file for each record, I would appreciate advise on that. Thank you in advance.
0
 
William ElliottSr Tech GuruCommented:
show me the code for your 'cmdExport_All_Records'  and i'll show you what to change.
or
upload a sample database and i could set it up
or
look at this
http://www.databasejournal.com/features/msaccess/article.php/3563671 
0
 
Trey31Author Commented:
As of now I dont have any code for this option...
0
 
William ElliottSr Tech GuruCommented:
easiest way to answer all of your questions is to upload a sample database.

then tell me xactly what you want derived from the database.

we know you want the following:
1) Create a Folder named Risk Scorecard
2) and Put the template in that folder
3) to have the option to export EVERY record as seperate Excel files into that folder


simple enough.
how do you want the excel files named?

is the code below what you are looking for?
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
 
sSQL = "select * from RiskScorecard"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
If Not rst.BOF Then rst.MoveFirst
 
	Do Until rst.EOF
	'
	' 	this is where you tell it what to do
	' 	the next line for example 
	'call cmdDownloadToExcel
	'
	Loop

Open in new window

0
 
Trey31Author Commented:
Im not sure how to upload a sample database, I am not that experienced with the export process and had to have help writing my code. I can send you what I have so far. I would like the naming convention of the file to be borrowed from text fields:  xlWB.SaveAs "c:\risk scorecard\" & Me![Risk Scorecard Qtr.] & Me!txtThird_Party_Name & Me![Risk Scorecard] & ".xls"

I am just not sure how to put together the command. That is the code that I am looking for though.  I have not transferred my database to SQL I am not sure if this matters though.
0
 
William ElliottSr Tech GuruCommented:
zip the database and upload to here http://www.ee-stuff.com/
use ID:22969328 when it asks for the question number
i'll take it from there
0
 
Trey31Author Commented:
I am uploading the file now, are you still available to help me?
0
 
Trey31Author Commented:
They stated that my file was too large to upload. I dont have any information in the tables yet. How can I get around this?
0
 
William ElliottSr Tech GuruCommented:
hmm,.

http://www.submitside.com/free_storage.html
here is a list of other place to upload files.
you could always google for other places,...

i am in and out of here at different hours of different days.

to answer your earlier question,. MS access uses SQL queries, so does excel and pretty much anything that stores data.

sSQL = "select * from RiskScorecard"

the line above creates a variable call sSQL

"select *" = this means select all the fields
"from riskscorecard" = this tells it what the name of the table is that it is getting the data from .


Do Until rst.EOF
''''''
loop

the code above cycles through each row that was returned from the SQL query.
if you put your code between the loop, then you will get what you want.

0
 
Trey31Author Commented:
The only thing that i am stuck on is the fact that it does not know where to Select*From   I do not know what to put here, My table name is 'Supplier Information', My Query name is 'Supplier Information Query', But the Form with my formulas and information name is 'Supplier Details' and the whole database is called 'Risk Scorecard Database.mdb' Please advise on which one to put there. Thank you.
0
 
Trey31Author Commented:
This only exported one recordset.
0
 
William ElliottSr Tech GuruCommented:
ok now we need to change the filename so that it is a different name for each file.  this means editing the codes for both of these to have a seperate nameing convention.

sub exportstuff()
	Dim dbs As DAO.Database
	Dim rst As DAO.Recordset
	Dim sSQL As String
	Dim x as integer 
	sSQL = "SELECT * FROM [Supplier Information]"
	Set dbs = CurrentDb
	Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
	If Not rst.BOF Then rst.MoveFirst
	 
		Do Until rst.EOF
 
			call cmdDownloadToExcel(x)
			x = x + 1
		Loop
end sub
 
 
 
 
 
 
 
Private Sub cmdDownloadToExcel(mycount as integer)
On Error GoTo Err_DTE
 
 
    Dim xlApp As Object
    Dim xlWB As Object
    Dim oSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    
    Set xlWB = xlApp.Workbooks.Open("c:\risk scorecard\Projected Template V3.xls")
    Set oSheet = xlWB.Sheets("Composite Risk Score")
    Set iSheet = xlWB.Sheets("Financial Scoring")
    Set aSheet = xlWB.Sheets("Operational Scoring")
    Set eSheet = xlWB.Sheets("Country Risk")
    Set uSheet = xlWB.Sheets("Compliance Risk")
    
    oSheet.Range("Name_of_the_Supplier").Value = Me!txtThird_Party_Name
    oSheet.Range("Category").Value = Me!Category
    oSheet.Range("Report_Period").Value = Me![Risk Scorecard Qtr.]
    oSheet.Range("Tier").Value = Me!Tier
    uSheet.Range("Medium_CAT_Open").Value = Me!txtTPM_QA_Medium_Open_Issues_CAT
    uSheet.Range("Low_CAT_Open").Value = Me!txtTPM_QA_Low_Open_Issues_CAT
    uSheet.Range("High_Closed_CAT").Value = Me!txtTPM_QA_High_Closed_Issues_CAT
    uSheet.Range("Medium_Closed_CAT").Value = Me!txtTPM_QA_Medium_Closed_Issues_CAT
    uSheet.Range("Low_Closed_CAT").Value = Me!txtTPM_QA_Low_Closed_Issues_CAT
    uSheet.Range("Sub_Total_TPM_QA_Issues_CAT_Issues").Value = Me!    txtSubtotal_Open_and_Closed_TPM_QA_Issues_CAT
    uSheet.Range("Past_due_TPM_Activities").Value = Me!txtPast_Due_TPM_Activities
    uSheet.Range("Number_of_past_due_Activities").Value = Me!txtNumber_of_Past_due_Activies
    uSheet.Range("Subtotal_TPM_Activities").Value = Me!txtSubtotal_of_Past_due_Issues
    uSheet.Range("Compliance_Risk_Component_Score").Value = Me!txtComposite_Risk_Component_Score
 
Exit_DTE:
    xlWB.SaveAs "c:\risk scorecard\" & mycount & Me![Risk Scorecard Qtr.] & Me!txtThird_Party_Name & Me![Risk Scorecard] & ".xls"
    xlWB.Close
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    
    Exit Sub
Err_DTE:
    MsgBox Err.Description
    Resume Exit_DTE:
    
End Sub

Open in new window

0
 
Trey31Author Commented:
It stated an error saying that it had the wrong number of arguments or invalid property assignment
0
 
William ElliottSr Tech GuruCommented:
i'm not sure exactly what you are doing,.. walk me through your steps,. or find a place to upload the database,.
0
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.

All Courses

From novice to tech pro — start learning today.