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?

--
Steve Schapel, Microsoft Access MVP


David French wrote:
> 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, ""
>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:eLqic%(E-Mail Removed)...
> 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 French wrote:
> > 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,

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?

--
Steve Schapel, Microsoft Access MVP


David French wrote:
> 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

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

"Steve Schapel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> David French wrote:
> > 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

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.

--
Steve Schapel, Microsoft Access MVP

David French wrote:
> 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
>