Append using FileTransfer

  • Thread starter Thread starter David French
  • Start date Start date
D

David French

I have a macro that I have pasted in the VB code for below.
This is for a file transfer to our bank.
The macro ends up creating 4 separate files which after I created this macro
the bank informed me that their systems see all of our data as one big file.
So now what I do is MANUALLY copy and paste into one file that I send to the
bank.
How can I change what I'm doing to I CREATE 1 file instead of 4?
The files I'm referring to are the .TXT files.
It would be nice to give them one .RTF file as well but I'm not going to be
greedy.

Thanks,
Dave French


Function mcrHOP()
On Error GoTo mcrHOP_Err

DoCmd.TransferSpreadsheet acImport, 8, "tblHOP",
"\\Poughkeepsie\mis\DBSi\AP Bank Rec\APBR HOP.xls", True, "a1:d3000"
Beep
MsgBox "The file was imported.", vbInformation, ""
qryHOP1 = "SELECT Count(REPORTS_V_CHK_VW_INFO.[CHECKVIEWCHK#]) AS
[CountOfCHECKVIEWCHK#], Sum(REPORTS_V_CHK_VW_INFO.CHECKVIEWNETPAYAMT) AS
SumOfCHECKVIEWNETPAYAMT"" FROM REPORTS_V_CHK_VW_INFO WHERE
(((IIf(([CHECKVIEWVOIDCHIND]=""N""),0,1))=0) AND
((REPORTS_V_CHK_VW_INFO.CHECKVIEWCHKVCHRCD)=""C"") AND
((REPORTS_V_CHK_VW_INFO.COMPANYCODE)=""8YT"") AND
((Month([CHECKVIEWPAYDATE]))=[Enter Numeric Month]) AND
((Year([CHECKVIEWPAYDATE]))=[Enter Year])) OR
(((IIf(([CHECKVIEWVOIDCHIND]=""N""),0,1))=0) AND
((REPORTS_V_CHK_VW_INFO.CHECKVIEWCHKVCHRCD)=""C"") AND
((REPORTS_V_CHK_VW_INFO.COMPANYCODE)=""8YS"") AND
((Month([CHECKVIEWPAYDATE]))=[Enter Numeric Month]) AND
((Year([CHECKVIEWPAYDATE]))=[Enter Year]));"

DoCmd.TransferText acExportFixed, "HopTest Export Specification",
qryHOP1, "\\Poughkeepsie\Keybank\APBR HOP.txt", False, ""
DoCmd.OpenReport "rptHOPsummary", acViewPreview, "", ""
DoCmd.OutputTo acOutputReport, "", "RichTextFormat(*.rtf)",
"\\Poughkeepsie\Keybank\HO Penn AP Summary.rtf", False, ""
DoCmd.Close acReport, "rptHOPsummary"
Beep
MsgBox "The files have been created.", vbInformation, ""
 
David,

First of all, this is not a macro. It is a VBA procedure. But in any
case, I can't see how this code is creating 4 text files?
 
My apologies.
That was the wrong one.
Here is the macro I'm using CONVERTED to VBA.

Function mcrAllInOne()
On Error GoTo mcrAllInOne_Err

DoCmd.TransferSpreadsheet acImport, 8, "tblHOP",
"\\Poughkeepsie\mis\DBSi\AP Bank Rec\APBR HOP.xls", True, "a1:d3000"
Beep
MsgBox "The HO Penn file was imported.", vbInformation, ""
DoCmd.TransferText acExportFixed, "HopTest Export Specification",
"qryHOP", "\\Poughkeepsie\Keybank\APBR HOP.txt", False, ""
DoCmd.OpenReport "rptHOPsummary", acViewPreview, "", ""
DoCmd.OutputTo , "", "RichTextFormat(*.rtf)", "\\Poughkeepsie\Keybank\HO
Penn AP Summary.rtf", False, ""
DoCmd.Close acReport, "rptHOPsummary"
Beep
MsgBox "The files have been created.", vbInformation, ""
DoCmd.TransferSpreadsheet acImport, 8, "tblPR",
"\\Poughkeepsie\mis\DBSi\AP Bank Rec\APBR PR.xls", True, "a1:d3000"
Beep
MsgBox "The Penn Rents file was imported.", vbInformation, ""
DoCmd.TransferText acExportFixed, "HopTest Export Specification",
"qryPR", "\\Poughkeepsie\Keybank\APBR PR.txt", False, ""
DoCmd.OpenReport "rptPRsummary", acViewPreview, "", ""
DoCmd.OutputTo , "", "RichTextFormat(*.rtf)",
"\\Poughkeepsie\Keybank\Penn Rents AP Summary.rtf", False, ""
DoCmd.Close acReport, "rptPRsummary"
Beep
MsgBox "The files have been created.", vbInformation, ""
DoCmd.TransferText acExportFixed, "Payroll Export Specification",
"qryHOPennEOMPayroll", "\\Poughkeepsie\Keybank\HO Penn Payroll.txt", False,
""
DoCmd.OpenReport "rptHOPennEOMPayrollSummary", acViewPreview, "", "",
acNormal
DoCmd.OutputTo , "", "RichTextFormat(*.rtf)", "\\Poughkeepsie\Keybank\HO
Penn EOM PR Summary.rtf", False, "", 0
DoCmd.Close , ""
Beep
MsgBox "The HO Penn files have been created.", vbInformation, ""
DoCmd.TransferText acExportFixed, "Payroll Export Specification",
"qryPennRentsEOMPayroll", "\\Poughkeepsie\Keybank\Penn Rents Payroll.txt",
False, ""
DoCmd.OpenReport "rptPennRentsEOMPayrollSummary", acViewPreview, "", "",
acNormal
DoCmd.OutputTo , "", "RichTextFormat(*.rtf)",
"\\Poughkeepsie\Keybank\Penn Rents EOM Summary Report.rtf", False, "", 0
DoCmd.Close , ""
Beep
MsgBox "The Penn Rents files have been created.", vbInformation, ""


mcrAllInOne_Exit:
Exit Function
Steve Schapel said:
David,

First of all, this is not a macro. It is a VBA procedure. But in any
case, I can't see how this code is creating 4 text files?

--
Steve Schapel, Microsoft Access MVP


David said:
I have a macro that I have pasted in the VB code for below.
This is for a file transfer to our bank.
The macro ends up creating 4 separate files which after I created this macro
the bank informed me that their systems see all of our data as one big file.
So now what I do is MANUALLY copy and paste into one file that I send to the
bank.
How can I change what I'm doing to I CREATE 1 file instead of 4?
The files I'm referring to are the .TXT files.
It would be nice to give them one .RTF file as well but I'm not going to be
greedy.

Thanks,
Dave French


Function mcrHOP()
On Error GoTo mcrHOP_Err

DoCmd.TransferSpreadsheet acImport, 8, "tblHOP",
"\\Poughkeepsie\mis\DBSi\AP Bank Rec\APBR HOP.xls", True, "a1:d3000"
Beep
MsgBox "The file was imported.", vbInformation, ""
qryHOP1 = "SELECT Count(REPORTS_V_CHK_VW_INFO.[CHECKVIEWCHK#]) AS
[CountOfCHECKVIEWCHK#], Sum(REPORTS_V_CHK_VW_INFO.CHECKVIEWNETPAYAMT) AS
SumOfCHECKVIEWNETPAYAMT"" FROM REPORTS_V_CHK_VW_INFO WHERE
(((IIf(([CHECKVIEWVOIDCHIND]=""N""),0,1))=0) AND
((REPORTS_V_CHK_VW_INFO.CHECKVIEWCHKVCHRCD)=""C"") AND
((REPORTS_V_CHK_VW_INFO.COMPANYCODE)=""8YT"") AND
((Month([CHECKVIEWPAYDATE]))=[Enter Numeric Month]) AND
((Year([CHECKVIEWPAYDATE]))=[Enter Year])) OR
(((IIf(([CHECKVIEWVOIDCHIND]=""N""),0,1))=0) AND
((REPORTS_V_CHK_VW_INFO.CHECKVIEWCHKVCHRCD)=""C"") AND
((REPORTS_V_CHK_VW_INFO.COMPANYCODE)=""8YS"") AND
((Month([CHECKVIEWPAYDATE]))=[Enter Numeric Month]) AND
((Year([CHECKVIEWPAYDATE]))=[Enter Year]));"

DoCmd.TransferText acExportFixed, "HopTest Export Specification",
qryHOP1, "\\Poughkeepsie\Keybank\APBR HOP.txt", False, ""
DoCmd.OpenReport "rptHOPsummary", acViewPreview, "", ""
DoCmd.OutputTo acOutputReport, "", "RichTextFormat(*.rtf)",
"\\Poughkeepsie\Keybank\HO Penn AP Summary.rtf", False, ""
DoCmd.Close acReport, "rptHOPsummary"
Beep
MsgBox "The files have been created.", vbInformation, ""
Click to expand...
Click to expand...
 
David,

The process you are using is importing data from the Excel worksheets
one by one into separate tables, and then outputting a text file for
each of the imported sets of data.

I think you could do it loke this instead...
1. Import all 4 of the Excel data first, into your 4 tables, i.e. do
all 4 of your TransferSpreadsheet actions first.
2. Run 3 Append Queries, to add the records from 3 of the tables of
imported data into the 4th one.
3. Then just one TransferText from this composite table to a single
text file.

Hope this makes sense. Does this help?
 
I think that may work.
I knew there was an append query...I guess I was just hoping it would work
directly to the TXT file.

There are actually 2 Excel sheets that get transferred...2 TransferText...
THEN there are 2 queries run directly on our Payroll database (ODBC) and
then the other 2 TransferText commands.

It will mean setting up some temporary tables but that may work better.
I'm attempting to make this as end user friendly as possible to unload this
as an End-of-Month procedure I need to perform.

I guess that's what I get for being a Jack-of-All-Trades...Master of NONE!

Thanks for your help.

Dave French
MOS XP Master Instructor
 
Best of luck, David. Yes, setting up some blank tables to temporarily
hold the data while you are manipulating it around, certainly seems like
a good idea, and then just run a delete query on them when finished,
ready for next month!

Please don't hesitate to drop back if you need further assistance.
 
Back
Top