Solved

Dumping multiple Excel output files with template and folder automation

Posted on 2007-11-18
18
235 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:Trey31
  • 9
  • 8
18 Comments
 

Expert Comment

by:curioustoknow2007
ID: 20310206
you just need to create a script that will do this for you easily.
0
 

Author Comment

by:Trey31
ID: 20310240
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
 
LVL 19

Expert Comment

by:weellio
ID: 20310270
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
 

Author Comment

by:Trey31
ID: 20310302
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
 
LVL 19

Expert Comment

by:weellio
ID: 20317296
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
 

Author Comment

by:Trey31
ID: 20317368
As of now I dont have any code for this option...
0
 
LVL 19

Expert Comment

by:weellio
ID: 20317542
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
 

Author Comment

by:Trey31
ID: 20317938
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
 
LVL 19

Expert Comment

by:weellio
ID: 20321346
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Trey31
ID: 20321838
I am uploading the file now, are you still available to help me?
0
 

Author Comment

by:Trey31
ID: 20321969
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
 
LVL 19

Expert Comment

by:weellio
ID: 20324603
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
 

Author Comment

by:Trey31
ID: 20324797
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
 
LVL 19

Accepted Solution

by:
weellio earned 500 total points
ID: 20325290
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
 

Author Comment

by:Trey31
ID: 20325439
This only exported one recordset.
0
 
LVL 19

Expert Comment

by:weellio
ID: 20328658
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
 

Author Comment

by:Trey31
ID: 20341889
It stated an error saying that it had the wrong number of arguments or invalid property assignment
0
 
LVL 19

Expert Comment

by:weellio
ID: 20344848
i'm not sure exactly what you are doing,.. walk me through your steps,. or find a place to upload the database,.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now