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.Applic
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\r
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_
oSheet.Range("Category").V
oSheet.Range("Report_Perio
oSheet.Range("Tier").Value
uSheet.Range("Medium_CAT_O
uSheet.Range("Low_CAT_Open
uSheet.Range("High_Closed_
uSheet.Range("Medium_Close
uSheet.Range("Low_Closed_C
uSheet.Range("Sub_Total_TP
uSheet.Range("Past_due_TPM
uSheet.Range("Number_of_pa
uSheet.Range("Subtotal_TPM
uSheet.Range("Compliance_R
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
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.Applic
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\r
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_
oSheet.Range("Category").V
oSheet.Range("Report_Perio
oSheet.Range("Tier").Value
uSheet.Range("Medium_CAT_O
uSheet.Range("Low_CAT_Open
uSheet.Range("High_Closed_
uSheet.Range("Medium_Close
uSheet.Range("Low_Closed_C
uSheet.Range("Sub_Total_TP
uSheet.Range("Past_due_TPM
uSheet.Range("Number_of_pa
uSheet.Range("Subtotal_TPM
uSheet.Range("Compliance_R
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
curioustoknow2007
you just need to create a script that will do this for you easily.
ASKER
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.
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
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
ASKER
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.
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
or
upload a sample database and i could set it up
or
look at this
http://www.databasejournal.com/features/msaccess/article.php/3563671
ASKER
As of now I dont have any code for this option...
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?
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
'
LoopASKER
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.
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.
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
use ID:22969328 when it asks for the question number
i'll take it from there
ASKER
I am uploading the file now, are you still available to help me?
ASKER
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?
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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This only exported one recordset.
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 SubASKER
It stated an error saying that it had the wrong number of arguments or invalid property assignment
i'm not sure exactly what you are doing,.. walk me through your steps,. or find a place to upload the database,.