VBA help combine 3 Steps into Excel VBA

kakuta81

New Member
Joined
Jul 28, 2010
Messages
2
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:
Code:
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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,633
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,278
Messages
5,657,816
Members
418,414
Latest member
ECMdusty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top