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!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,178
Office Version
365
Platform
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,516
Messages
5,511,750
Members
408,864
Latest member
cmajewsk

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top