Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dumping multiple Excel output files with template and folder automation

Posted on 2007-11-18
18
Medium Priority
?
256 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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
 

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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

715 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