VBA help combine 3 Steps into Excel VBA

[kakuta81]

Hello

I was wondering if you could help me. I am currently working on a process of uploading files in a certain format. The process currently entails 3 steps, please see:

-- Clicking on a .bat file Rename current files (records2.xls to records2.bak, records3.xls to records3.bak......)
-- MS Access is then launched
-- Macro is run doing the following:
-- deleting records table
-- importing records from Details.xls
-- Query records.. should now be repopulated based on the query information of each records.. using static data from tables
-- Save records2 table/query to C:\Data\records2.xls
-- Save records3 table/query to C:\Data\records3.xls
-- Save records4 table/query to C:\Data\records4.xls
-- Launch Excel - Run Macro to convert each individual file in records2.prn, records3.prn etc with a FileFormat:= _
xlTextPrinter

As the files cannot be directly saved as .prn from Access the requirement is to then use another macro in Excel.

I am trying to look at a way of incorporating the 3 steps into Excel.

Please see the VBA code from access:

Function Create_records()
On Error GoTo Create_records_Err
    DoCmd.DeleteObject acTable, "records"
    ' Import Details.xls
    DoCmd.TransferSpreadsheet acImport, 8, "records", "C:\Data\Details.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "records2", "C:\Data\records2.xls", False, ""
    DoCmd.TransferSpreadsheet acExport, 8, "records3", "C:\Data\records3.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "records4", "C:\Data\records4.xls", False, ""
    Beep
End Function

Is there anyway of doing this?

Thanking you!

 

[Joe4]

Welcome to the Board!

As the files cannot be directly saved as .prn from Access the requirement is to then use another macro in Excel.

Not exactly. Access cannot export directly to a "prn" extension, but it has no problems exporting to a Fixed Width file format. Then simply just add a few lines in VBA that renames the file it exports to change the extension (no need to export all the way over to Excel and do it there).

The key is to create a Fixed Width Export Specification that tells Access the size of all the fields being exported (if different from the actual field sizes in the table). Simply go through the steps of manually exporting the table once. Just before clicking on "Finish", click Advanced, and it will show you the Export Specification you have just built. Then click on "Save As" as give it a name (and remember this name so you can use it in your VBA code!).

So here is what that section of code would look like:

    DoCmd.TransferText acExportFixed, "Export_Specs", "records3", "C:\Data\records3.txt", True, ""
    DoCmd.TransferText acExportFixed, "Export_Specs", "records4", "C:\Data\records4.txt", True, ""
    FileCopy "C:\Data\records3.txt", "C:\Data\records3.prn"
    FileCopy "C:\Data\records4.txt", "C:\Data\records4.prn"
    Kill "G:\C\FSA\records*.txt"
    Beep