MS Access RetVal Command

[santa12345]

Hello.
I currently have some vb code in Access that is working.
The code looks at a table in access. Lets say it has 400 line items of information.
The script will take the first record, run a few queries, export each table to an excel file (template). Let's say the template is called test.xls
So the queries are ran, tables are exported into various tabs within test.xls and using the following command, it saves the files with some variables. (date/region,etc...)

RetVal = Shell("cmd /c copy /y c:\123\test.xls c:\123\US\" & datename & "\" & strDistrict & "\" & strWI & txt_agree & "\" & strWI & txt_agree & strxcel, vbHide)

The process keeps looping thru the 400 line item table, and repeats. Everything is working, the only issue I have is the template grows in size after repeating this process.
If i simply open up the template, save it, close it, the file size decreases considerable.

I was wondering if there was a Retval command or some other line item command - that I can add after the above command.....that will open the template (if needed), save and close it.

Any input would be greatly appreciated.

 

[jackd]

If the Shell function successfully executes the named file, it returns the task ID of the started program. The task ID is a unique number that identifies the running program. If the Shell function can't start the named program, an error occurs.

Retval contains the number representing the TaskId. It is a variant. it could be name anything you want.

 

[santa12345]

Thank you for the reply. The current shell function i am using is currently working and names the file correctly.
Are you saying, get the task ID and use that to tell the system to save and close the template before the next loop / process starts?
Sorry...I'm sort of new to this. and if so - how do i do that.

 

[jackd]

Not sure, but it seems you may be incrementing some storage every time you go through the loop, thus increasing the size.
What exactly are you moving to Excel?
As I understand, and I need clarification, you have a table with 400 records.
You have some process to take each record, run some queries and add records to or create a new table that you want to copy to excel???

Please describe the template and how it fits with the 400 records in the table.
Access has TransferText and TransferSpreadsheet commands. There are other ways to copy files from 1 directory to another.

 

[santa12345]

I have a customer table that has 400 records.
The loop grabs the first line item and then runs 5 queries. The queries pull the data from various tables.
I am currently using the TransferSpreadsheet command.
ie...
DoCmd.OpenQuery "011c - 3 Year Totals", , acReadOnly
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Three_Year_Totals", "c:\123\test.xls", True

the test.xls will have tabs, calcs built in..etc...that will get populated from the output of those queries.
the retval command will then name the file accordingly..
then loops to the next record in the customer table
everything works great....with the exception of the template (test.xls)...grows in size as the loops continues.
I know if i manually stop the process....and just open save and close the template...the size goes from 36mb to 18mb ..just like that.
so the thought was right after it creates/names a file...and before it loops and grabs the next customer...
open save and close the template.
I hope that helps...if not..let me know.
Thanks!!

 

[xenou]

As far as I can tell if the workbook already exists ("C:\123\test.xls") then transferspreadsheet/export will append data to a new tab in the workbook in the last position and name it automatically. Is that what is happening? So, this would not any kind of template in the literal sense or true sense - it is just a destination workbook which you are calling a template. It should be filling up with tabs as you continue to export it. But I have no idea why any Excel file whatever would decrease in size upon opening, so that is just something I find confusing. What is actually in this workbook (to start with, and when you are done with your process). Is the workbook influenced by any VBA code in it?

as far as just opening a workbook and closing it, you can use Excel Automation in MSAccess to do that but I don't generally suggest doing this unless the workbook is used only by your process and is not shared with other people (they should get copies).

 

[santa12345]

Hello and thank you for replying. The data is not appending to the template. It overwrites the existing data within the different tabs of the template and then the Retval performs the save file as xyz depending on the customer attributes etc....If i stop the process...the template will only have the dataset of one of the customer in it. So for sure...no appending going on.

The template exists with calculations and lookups in various tabs. when the queries export data into the template, the calculations and lookups are then complete.
You mention Excel Automation in MSAccess. The template is not used by anybody else except by the process when i start it. So that might be an option. How does that work?
Can I have the automation open the template, save the template and close it? The key is the save part, if i manually open the file and close it, the size doesn't change. Only when I open, save and close it.
Thanks!!

 

[xenou]

something like this would be one option:

Sub Foo()

Dim xlApp As Object, wkbk As Object


    On Error Goto errHandler

    Set xlApp = CreateObject("Excel.Application")

    With xlApp
        Set wkbk = .Workbooks.Open(path)
        wkbk.Close(True) '//Close and save changes
        .Quit
    End With


procDone:

Set wkbk = Nothing
Set xlApp = Nothing

Exit Sub

errHandler:

Resume procDone

End Sub

However, this is subject to some pitfalls. If the workbook is in use it might not be able to be saved and your code will hang forever waiting for you to answer a dialog. Its dangerous.

Not much of what you are saying makes a lot of sense - if that is how TransferSpreadsheet works then its undocumented (so far as I can tell). So you are stuck with whatever trial and error gives you, as there is nothing that is "supposed to happen". Also still no clue at all why a workbook that doesn't change would grow in size, or why a workbook that has grown in size would suddenly get smaller when opened.

 

[santa12345]

Here is the code if that helps.



Private Sub cmd_Monthly_Customer_Click()

DoCmd.SetWarnings 0

Dim strWI, strDistrict, strxcel, objFolder, strBO

Dim Temp As Long

Dim ddate As Date

Dim dddate As Integer

Dim datename As String

'Create an FSO for creating needed folders

Set objFSO = CreateObject("Scripting.FileSystemObject")

'Convert the inputed expiration date into the next Month

ddate = CDate(TxtExp)

ddate = ddate + 1

dddate = Month(ddate)

datename = MonthName(dddate)

Dim RetVal

Set objFolder = objFSO.CreateFolder("C:\123\US\" & datename)

Set objFolder = objFSO.CreateFolder("c:\123\US\" & datename & "\" & "4004")

Set objFolder = objFSO.CreateFolder("c:\123\US\" & datename & "\" & "4005")

strxcel = ".xls"

strWI = "A#"

Dim rs As DAO.Recordset

Dim db As DAO.Database

Set db = CurrentDb

Set rs = db.OpenRecordset("AGMT_Analysis", dbOpenTable)

'We've opened the table now go to the first line

rs.MoveFirst

'Keep reading through table until we hit the last entry

'For each record we find, create an ARS excel file

Do Until rs.EOF

txt_agree = rs![agreement]

strDistrict = rs![Sales Org]

DoCmd.OpenQuery "009 - Pull single Agreement", , acReadOnly

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Full Agreement", "c:\123\us\test.xls", True

'Sleep (2000)

DoCmd.OpenQuery "009A - Distributor Customer Name ID", , acReadOnly

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Full Agreement1", "c:\123\us\test.xls", True

'Sleep (2000)

DoCmd.OpenQuery "010 - Agreement Header 1", , acReadOnly

DoCmd.OpenQuery "010 - Agreement Header 2", , acReadOnly

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Header_Data", "c:\123\us\test.xls", True

'Sleep (2000)

DoCmd.OpenQuery "011c - 3 Year Totals", , acReadOnly

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Three_Year_Totals", "c:\123\us\test.xls", True

'Sleep (2000)

DoCmd.OpenQuery "012 - Participants"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Participants", "c:\123\us\test.xls", True

'Sleep (2000)

Set objFolder = objFSO.CreateFolder("c:\123\us\" & datename & "\" & strDistrict & "\" & strWI & txt_agree)

RetVal = Shell("cmd /c copy /y c:\123\us\test.xls c:\123\us\" & datename & "\" & strDistrict & "\" & strWI & txt_agree & "\" & strWI & txt_agree & strxcel, vbHide)

Sleep (2000)

rs.MoveNext

Loop

DoCmd.SetWarnings -1

End Sub

 

[xenou]

I'd probably start with a template that is not dumped with data in a loop like this. Keep your template clean and safe.

So, for example, save your "template" as c:\123\us\test_TEMPLATE.xls

then for the first step copy it:
RetVal = Shell("cmd /c copy /y c:\123\us\test_TEMPLATE.xls c:\123\us\test.xls", vbHide)
Sleep (2000)

Now you always start with a clean start.