Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Exported delimited text file group together by unique values in header and details from Access 2007

Posted on 2014-08-29
31
Medium Priority
?
314 Views
Last Modified: 2014-09-19
I have problem with my text file that was populated from Access. I have been trying to look for any solution but to no avail.
Please find attached text files:
APInvoice_MyCode.del = this is the code I have for now that populates from the table into text file. It shows Header and detail each time with the same Invoice No and FullGLAccountNo.
APInvoices_What_I_Want_Is_Group.del = this is what I want to accomplish. Group by same Invoice No and FullGLAccountNo.

What I want is to group the same InvoiceNo, FullGLAccountNo together.
This is the VBA code I have so far:
Dim strpath As String
Dim sfilter As String
Dim sfile As String
Dim lsCurPath As String
Dim rs As New ADODB.Recordset
Dim rsWrk As New ADODB.RecordsetDim rsHeader As New ADODB.Recordset
Dim rsDetail As New ADODB.Recordset
Dim TempBatch As String
Dim tempdate As String
Dim tempExtension As String
Dim lsUserID As String
Dim lsCommentVal As String
Dim lsCurrComment As String
Dim lsTempComment As String
Dim defaultIfNull As String
Dim lsCurCompanyID As String
Dim tranType As String
Dim detailType As String
Dim headerType As String
Dim useTaxType As String
Dim S As String
Dim tempInvoice As String
Dim lsquantity As String
Dim taxString As String
Dim vBatch As Date

  
    
    lsUserID = "admin"
    lsCommentVal = "0"
    lsTempComment = "Import-Invoice No:"
    defaultIfNull = "1"
    lsCurCompanyID = "Canton"
    taxString = "000 NOT TAXABLE"
    tranType = "401"
    detailType = "D"
    headerType = "V"
    useTaxType = "X"
    lsquantity = "1"
    S = ";"
    sfile = "APInvoices_"

    
 
            
    strsql = "Select ID,VendorID, InvoiceNo, InvoiceTotal, InvoiceDate, InvoiceDueDate, FullGLAcctNo, Comment from tblMainAPImport ORDER BY InvoiceNo"
    rsWrk.Open strsql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 

    If Len(strpath) <= 0 Then
        strpath = "\\network\project\development\"
    End If
    
    sfilter = "del" & Chr(0) & "*.*" & Chr(0)
    
    'open browse file box for input file
    lsCurPath = ahtCommonFileOpenSave(, strpath, sfilter, , , sfile, "Choose a Location for AP Invoice Export...", , False)
     
   
     If lsCurPath = "" Then
        MsgBox "The Export Invoices operation was canceled.", , " "
        GoTo Cancel_cmdExportExcel_Click
    End If
    
    'set filename of export file
    lsCurPath = lsCurPath & ".del"
        
    'open text file
    Open lsCurPath For Output As #1
    
Do Until rsWrk.EOF
        'open header and write to .del file
        rsHeader.Open "Select D.VendorID,D.InvoiceNo,D.InvoiceTotal,D.InvoiceDate,D.InvoiceDueDate,D.FullGLAcctNo,D.Comment" _
            & " from tblMainAPImport D" _
            & " where D.ID = " & rsWrk!ID, CurrentProject.Connection, adOpenDynamic, adLockPessimistic
        lsCurrComment = lsTempComment & Trim(rsHeader!InvoiceNo)
        
        If rsHeader.Fields("InvoiceTotal") < 0 Then
            tranType = "402"
        Else
            tranType = "401"
        End If
        
        'write to text file in MAS import format
        
        Print #1, headerType; S; S; S; Trim(rsHeader!VendorID); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; Trim(rsHeader!VendorID); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; Trim(rsHeader!InvoiceDate); S; Trim(rsHeader!InvoiceNo); S; tranType; S; S; defaultIfNull; S; lsUserID; S; S; S; S; S; S; Format(rsHeader!InvoiceDueDate, "mm/dd/yyyy"); S; S; S; S; S; S; S; S
        
        Print #1, detailType; S; S; lsCommentVal; S; S; Trim(rsHeader!InvoiceTotal); S; S; S; Trim(lsCurrComment); S; S; S; lsquantity; S; S; Trim(rsHeader!FullGLAcctNo); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; taxString; S; S; S; S; S; S; S; S; S; S
        
       rsHeader.Close
        rsWrk.MoveNext
        
    Loop
    Close #1
    rsWrk.MoveFirst
    rsWrk.Close
    
MsgBox "The AP Invoice has been created.", vbOKOnly, "Export Complete!"
        
    Set rsWrk = Nothing
    Set rsDetail = Nothing
    Set rsHeader = Nothing

Exit_cmdExportExcel_Click:
    Close #1
    Exit Sub
Cancel_cmdExportExcel_Click:
         GoTo Exit_cmdExportExcel_Click
End Sub

Open in new window

Please help.
0
Comment
Question by:Queennie L
  • 15
  • 8
  • 2
  • +1
26 Comments
 

Author Comment

by:Queennie L
ID: 40292771
This is my table in access.
Table.docx
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40293436
If I understand what you are looking for (unless the grouping must occur in the text document) could perhaps be resolved by opening the text document in Excel, use text-to-column to parse the data into separate columns (if it doesn't already do that for you), and create a pivot table that groups by invoiceNO and FullGLAcctNo.  If you don't want to leave it as a pivot table, you could then copy pivot table and paste "value".
0
 

Author Comment

by:Queennie L
ID: 40293486
The grouping must occur in the text document.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Queennie L
ID: 40303148
I've requested that this question be deleted for the following reason:

Edited this question.
0
 

Author Comment

by:Queennie L
ID: 40323736
I've requested that this question be deleted for the following reason:

Nobody responded.
0
 

Author Comment

by:Queennie L
ID: 40323697
Yes.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40323783
I think what you need to do is loop through the detail records until the ID changes

        'write to text file in MAS import format
        
        Print #1, headerType; S; S; S; Trim(rsHeader!VendorID); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; Trim(rsHeader!VendorID); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; Trim(rsHeader!InvoiceDate); S; Trim(rsHeader!InvoiceNo); S; tranType; S; S; defaultIfNull; S; lsUserID; S; S; S; S; S; S; Format(rsHeader!InvoiceDueDate, "mm/dd/yyyy"); S; S; S; S; S; S; S; S
        
    Do Until rsHeader!ID   <>  rsWrk!ID
        Print #1, detailType; S; S; lsCommentVal; S; S; Trim(rsHeader!InvoiceTotal); S; S; S; Trim(lsCurrComment); S; S; S; lsquantity; S; S; Trim(rsHeader!FullGLAcctNo); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; taxString; S; S; S; S; S; S; S; S; S; S
                rsWrk.MoveNext
   Loop
       rsHeader.Close

Open in new window


mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40327151
Have you tried my solution?

mlmcc
0
 

Author Comment

by:Queennie L
ID: 40328015
@mlmcc:

I tried it but still the same. See attach text file.

If VendorID and InvoiceNo are the same in Header then show only the Header once. It can be multiple details based on the VendorID and InvoiceNo.

I have been trying to work on this for few days.

Thank you for your help.
APInvoices-6.txt
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40328150
Please test this.  In this version of your routine, I'm opening up a Group By recordset of the header data.  For each header record, I'm retrieving the detail records for that invoiceNo.

Since your invoiceTotal varies with each detail row, I'm summing the TotalInvoice data for use in the loop.  This value determines the trantype.
Dim strpath As String
Dim sfilter As String
Dim sfile As String
Dim lsCurPath As String
Dim rs As New ADODB.Recordset
Dim rsWrk As New ADODB.RecordsetDim rsHeader As New ADODB.Recordset
Dim rsDetail As New ADODB.Recordset
Dim TempBatch As String
Dim tempdate As String
Dim tempExtension As String
Dim lsUserID As String
Dim lsCommentVal As String
Dim lsCurrComment As String
Dim lsTempComment As String
Dim defaultIfNull As String
Dim lsCurCompanyID As String
Dim tranType As String
Dim detailType As String
Dim headerType As String
Dim useTaxType As String
Dim S As String
Dim tempInvoice As String
Dim lsquantity As String
Dim taxString As String
Dim vBatch As Date

  
    
    lsUserID = "admin"
    lsCommentVal = "0"
    lsTempComment = "Import-Invoice No:"
    defaultIfNull = "1"
    lsCurCompanyID = "Canton"
    taxString = "000 NOT TAXABLE"
    tranType = "401"
    detailType = "D"
    headerType = "V"
    useTaxType = "X"
    lsquantity = "1"
    S = ";"
    sfile = "APInvoices_"

    
 
            
 

    If Len(strpath) <= 0 Then
        strpath = "\\network\project\development\"
    End If
    
    sfilter = "del" & Chr(0) & "*.*" & Chr(0)
    
    'open browse file box for input file
    lsCurPath = ahtCommonFileOpenSave(, strpath, sfilter, , , sfile, "Choose a Location for AP Invoice Export...", , False)
     
   
     If lsCurPath = "" Then
        MsgBox "The Export Invoices operation was canceled.", , " "
        GoTo Cancel_cmdExportExcel_Click
    End If
    
    'set filename of export file
    lsCurPath = lsCurPath & ".del"
        
    'open text file
    Open lsCurPath For Output As #1
    'open header and write to .del file
    rsHeader.Open "Select VendorID, InvoiceNo, InvoiceDate, InvoiceDueDate, Sum(InvoiceTotal) As SumOfInvoiceTotal" _
        & " from tblMainAPImport D " _
        & " Group By VendorID, InvoiceNo, InvoiceDate, InvoiceDueDate" _
        & , CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    
    Do Until rsHeader.EOF
        If rsHeader!SumOfInvoiceTotal < 0 Then
            tranType = "402"
        Else
            tranType = "401"
        End If

        Print #1, headerType; S; S; S; Trim(rsHeader!VendorID); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; Trim(rsHeader!VendorID); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; Trim(rsHeader!InvoiceDate); S; Trim(rsHeader!InvoiceNo); S; tranType; S; S; defaultIfNull; S; lsUserID; S; S; S; S; S; S; Format(rsHeader!InvoiceDueDate, "mm/dd/yyyy"); S; S; S; S; S; S; S; S
        
        strsql = "Select ID,VendorID, InvoiceNo, InvoiceTotal, InvoiceDate, InvoiceDueDate, FullGLAcctNo, Comment from tblMainAPImport Where InvoiceNo=" & rsHeader!InvoiceNO & " ORDER BY InvoiceNo"
        rsWrk.Open strsql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        
        Do Until rsWrk.EOF
            'write to text file in MAS import format
            Print #1, detailType; S; S; lsCommentVal; S; S; Trim(rsHeader!InvoiceTotal); S; S; S; Trim(lsTempComment & Trim(rsWrk!InvoiceNo)); S; S; S; lsquantity; S; S; Trim(rsHeader!FullGLAcctNo); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; taxString; S; S; S; S; S; S; S; S; S; S
        
            rsWrk.MoveNext
        Loop
        
        rsHeader.MoveNext
    Loop
    
    Close #1
    rsHeader.Close
    rsWrk.MoveFirst
    rsWrk.Close
    
MsgBox "The AP Invoice has been created.", vbOKOnly, "Export Complete!"
        
    Set rsWrk = Nothing
    Set rsDetail = Nothing
    Set rsHeader = Nothing

Exit_cmdExportExcel_Click:
    Close #1
    Exit Sub
Cancel_cmdExportExcel_Click:
         GoTo Exit_cmdExportExcel_Click
End Sub

Open in new window

Note: you might need to tweak the code in order accommodate these changes.  This code is not tested.
0
 

Author Comment

by:Queennie L
ID: 40329073
@aikimark:

There is an error message when I run this code:

Run-time error '3709':
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

Thank you.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40329094
what line does it stop on?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40329098
My instinct would be that it is on the rsWrk.Open statement.  If so, we need to close the recordset every time we go to the next header row.  If so, the bottom of the outer loop will need to look like this:
        rsHeader.MoveNext
        rsWrk.Close
    Loop
    
    Close #1
    rsHeader.Close

Open in new window

0
 

Author Comment

by:Queennie L
ID: 40329099
@aikimark:

I tweak the code that you have but my output is keeps repeating header and details. I know I am almost there.

My code:

On Error GoTo Err_Command25_Click
Dim strpath As String
Dim sfilter As String
Dim sfile As String
Dim lsCurPath As String
Dim rs As New ADODB.Recordset
Dim rsWrk As New ADODB.Recordset
'Dim rsWrk As DAO.Recordset
Dim rsHeader As New ADODB.Recordset
Dim rsDetail As New ADODB.Recordset
Dim TempBatch As String
Dim tempdate As String
Dim tempExtension As String
Dim lsUserID As String
Dim lsCommentVal As String
Dim lsCurrComment As String
Dim lsTempComment As String
Dim defaultIfNull As String
Dim lsCurCompanyID As String
Dim tranType As String
Dim detailType As String
Dim headerType As String
Dim useTaxType As String
Dim S As String
Dim tempInvoice As String
Dim lsquantity As String
Dim taxString As String
Dim vBatch As Date
Dim lsCurInvoiceNo As String
Dim detailDesc As String
Dim lsVendorID As String
Dim vHeaderFilter As String
Dim sqlstr As String
    
    lsUserID = "admin"
    lsCommentVal = "0"
    lsTempComment = "Import-Invoice No:"
    defaultIfNull = "1"
    lsCurCompanyID = "Canton"
    taxString = "000 NOT TAXABLE"
    tranType = "401"
    detailType = "D"
    headerType = "V"
    useTaxType = "X"
    lsquantity = "1"
    S = ";"
    detailDesc = ""
    sfile = "APInvoices_"
    vHeaderFilter = ""
    
 
       
    If Len(strpath) <= 0 Then
        strpath = "\\network\project\development\"
    End If
    
    sfilter = "del" & Chr(0) & "*.*" & Chr(0)
    
    'open browse file box for input file
    lsCurPath = ahtCommonFileOpenSave(, strpath, sfilter, , , sfile, "Choose a Location for AP Invoice Export...", , False)
     
     If lsCurPath = "" Then
        MsgBox "The Export Invoices operation was canceled.", , " "
        GoTo Cancel_Command25_Click
    End If
    
    'set filename of export file
    lsCurPath = lsCurPath & ".del"
    
    'open text file
    Open lsCurPath For Output As #1
    
    sqlstr = "Select * from tblMainAPImport " & vHeaderFilter & " ORDER BY InvoiceNo"
    Set rsHeader = New ADODB.Recordset
    
    rsHeader.Open sqlstr, CurrentProject.Connection, adOpenStatic, adLockPessimistic
    
     Do Until rsHeader.EOF
      
    lsVendorID = rsHeader.Fields("VendorID").Value
    lsCurInvoiceNo = rsHeader.Fields("InvoiceNo").Value

    
        If rsHeader.Fields("InvoiceTotal") < 0 Then
            tranType = "402"
        Else
            tranType = "401"
        End If
        
        'write to text file in MAS import format
       
       
        
        Print #1, headerType; S; S; S; lsVendorID; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; lsVendorID; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; Trim(rsHeader!InvoiceDate); S; lsCurInvoiceNo; S; tranType; S; S; defaultIfNull; S; lsUserID; S; S; S; S; S; S; Format(rsHeader!InvoiceDueDate, "mm/dd/yyyy"); S; S; S; S; S; S; S; S
        
        rsHeader.MoveNext
        lsCurrComment = lsTempComment & Trim(lsCurInvoiceNo)
        
        'open detail and write to .del file
        Set rsDetail = New ADODB.Recordset
        sqlstr = "Select d.* from tblMainAPImport d" _
            & " where d.InvoiceNo = '" & lsCurInvoiceNo & "'" & " ORDER By d.InvoiceNo"
            rsDetail.Open sqlstr, CurrentProject.Connection, adOpenStatic, adLockPessimistic
            
         
        Do While Not rsDetail.EOF
        
       
         
        Print #1, detailType; S; S; lsCommentVal; S; Trim(rsDetail!TransDesc); S; Trim(rsDetail!InvoiceTotal); S; S; S; Trim(lsCurrComment); S; S; S; lsquantity; S; S; Trim(rsDetail!FullGLAcctNo); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; taxString; S; S; S; S; S; S; S; S; S; S
      
        
        rsDetail.MoveNext
        Loop
        
        
        rsHeader.MoveNext
        Loop
        
        
        rsDetail.Close
        rsHeader.Close
    Close #1
    
    MsgBox "The Import Invoice has been created.", vbOKOnly, "Export Complete!"
        
    'Set rsWrk = Nothing
    Set rsDetail = Nothing
    Set rsHeader = Nothing
    
   
On Error Resume Next

Exit_Command25_Click:
    Close #1
    Exit Sub
    
Cancel_Command25_Click:
  '
    GoTo Exit_Command25_Click

Err_Command25_Click:

    GoTo Cancel_Command25_Click

Open in new window


Please  help.

Thanks.
APInvoices-Output.txt
0
 

Author Comment

by:Queennie L
ID: 40329102
@aikimark:

It stops at rsHeader.open
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40329133
Go back to the code I posted and start with that.  You have tweaked the code too much.

Since you're in an Access environment, instantiate your recordset variables like this:
set rsHeader = dbEngine(0)(0).OpenRecordset(strSQL, adOpenStatic)

Open in new window

0
 

Author Comment

by:Queennie L
ID: 40329146
@aikimark:


When I run the code it has an error message:

Run-time error '3001':
Invalid argument.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40329149
WHERE?
0
 

Author Comment

by:Queennie L
ID: 40329158
set rsHeader = dbEngine(0)(0).OpenRecordset(strSQL, adOpenStatic)


It stops right here.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40329160
replace strSQL with actual SQL
0
 

Author Comment

by:Queennie L
ID: 40330667
It is the same problem. Run-time error 3001.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40331298
Please post a database, containing some representative sample of the tblMainAPImport table.

What version of Access are you using?
0
 

Author Comment

by:Queennie L
ID: 40331503
Attached is the sample of tblMainAPImport table database.

Access 2007

Thank you for your help.
APInvoice.accdb
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40331643
This routine:
Sub Q_28508088()
Dim strpath As String
Dim sfilter As String
Dim sfile As String
Dim lsCurPath As String
Dim rs As Recordset
Dim rsWrk As Recordset
Dim rsHeader As Recordset
Dim rsDetail As Recordset
Dim TempBatch As String
Dim tempdate As String
Dim tempExtension As String
Dim lsUserID As String
Dim lsCommentVal As String
Dim lsCurrComment As String
Dim lsTempComment As String
Dim defaultIfNull As String
Dim lsCurCompanyID As String
Dim tranType As String
Dim detailType As String
Dim headerType As String
Dim useTaxType As String
Dim S As String
Dim tempInvoice As String
Dim lsquantity As String
Dim taxString As String
Dim vBatch As Date
Dim strSQL As String
  
    
    lsUserID = "admin"
    lsCommentVal = "0"
    lsTempComment = "Import-Invoice No:"
    defaultIfNull = "1"
    lsCurCompanyID = "Canton"
    taxString = "000 NOT TAXABLE"
    tranType = "401"
    detailType = "D"
    headerType = "V"
    useTaxType = "X"
    lsquantity = "1"
    S = ";"
    sfile = "APInvoices_"

    If Len(strpath) <= 0 Then
        strpath = "C:\users\mark\downloads\"          '"\\network\project\development\"
    End If
    
    sfilter = "del" & Chr(0) & "*.*" & Chr(0)
    
    'open browse file box for input file
    With FileDialog(msoFileDialogSaveAs)
        .AllowMultiSelect = False
        .Title = "Choose a Location for AP Invoice Export..."
'        .Filters.Add "del", "*.*", 1
        .InitialFileName = strpath
        
        .Show
        If .SelectedItems.Count = 0 Then
'            MsgBox "nothing selected"
'            Exit Sub
        Else
            lsCurPath = .SelectedItems(1)
        End If
        
    End With
    
    'lsCurPath = Application.FileDialog(msoFileDialogFolderPicker).Show(, strpath, sfilter, , , sfile, "Choose a Location for AP Invoice Export...", , False)
    
   
     If lsCurPath = "" Then
        MsgBox "The Export Invoices operation was canceled.", , " "
        GoTo Cancel_cmdExportExcel_Click
    End If
    
    'set filename of export file
    'lsCurPath = lsCurPath & ".del"
        
    'open text file
    Open lsCurPath For Output As #1
    'open header and write to .del file
    Set rsHeader = DBEngine(0)(0).OpenRecordset("Select VendorID, InvoiceNo, InvoiceDate, InvoiceDueDate, Sum(InvoiceTotal) As SumOfInvoiceTotal " & _
        " from tblMainAPImport D " & _
        " Group By VendorID, InvoiceNo, InvoiceDate, InvoiceDueDate" _
        , adOpenDynamic)
    
    Do Until rsHeader.EOF
        If rsHeader!SumOfInvoiceTotal < 0 Then
            tranType = "402"
        Else
            tranType = "401"
        End If

        Print #1, headerType; S; S; S; Trim(rsHeader!VendorID); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; Trim(rsHeader!VendorID); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; Trim(rsHeader!InvoiceDate); S; Trim(rsHeader!InvoiceNo); S; tranType; S; S; defaultIfNull; S; lsUserID; S; S; S; S; S; S; Format(rsHeader!InvoiceDueDate, "mm/dd/yyyy"); S; S; S; S; S; S; S; S
        
        strSQL = "Select ID,VendorID, InvoiceNo, InvoiceTotal, InvoiceDate, InvoiceDueDate, FullGLAcctNo, Comment from tblMainAPImport Where InvoiceNo='" & rsHeader!InvoiceNo & "' ORDER BY InvoiceNo"
        Set rsWrk = DBEngine(0)(0).OpenRecordset(strSQL, adOpenDynamic)
        
        Do Until rsWrk.EOF
            'write to text file in MAS import format
            Print #1, detailType; S; S; lsCommentVal; S; S; Trim(rsWrk!InvoiceTotal); S; S; S; Trim(lsTempComment & Trim(rsWrk!InvoiceNo)); S; S; S; lsquantity; S; S; Trim(rsWrk!FullGLAcctNo); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; taxString; S; S; S; S; S; S; S; S; S; S
        
            rsWrk.MoveNext
        Loop
        
        rsHeader.MoveNext
    Loop
    
    Close #1
    rsHeader.Close
    rsWrk.MoveFirst
    rsWrk.Close
    
MsgBox "The AP Invoice has been created.", vbOKOnly, "Export Complete!"
        
    Set rsWrk = Nothing
    Set rsDetail = Nothing
    Set rsHeader = Nothing

Exit_cmdExportExcel_Click:
    Close #1
    Exit Sub
Cancel_cmdExportExcel_Click:
         GoTo Exit_cmdExportExcel_Click
End Sub

Open in new window

produces the attached file.
API.txt
0
 

Author Closing Comment

by:Queennie L
ID: 40332876
@aikimark:

This is what I needed.

You are awesome smart.

I appreciate your help.

Thank you.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an article on how to answer questions, earn points and become an expert.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question