Solved

Dumping multiple Excel output files with template and folder automation

Posted on 2007-11-18
18
251 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

626 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