Solved

Dumping multiple Excel output files with template and folder automation

Posted on 2007-11-18
18
246 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

828 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