Uploading XLS file using VBA.

Below is a sample code to upload Sales records from XLS file, which is downloaded from a different system, e.g., POS, etc..  In my previous blog, I already explain the XLS file transfer between two system but it needs more practical example and thus this new blog is written.  There are several steps in uploading a XLS file, the following are enumerated below:

1. Open a File Dialog, Line 3

2. Import the chosen file, Line 23

3. Append the records to the target table, in our example, the invoice, Line 31

    If MsgBox("Upload POS sales?", vbQuestion + vbYesNo, "POS") = vbYes Then

        'Open the dialog box
        Dim FileDialog As Object
        Dim ChoosenFile As String:  ChoosenFile = ""
        Set FileDialog = Application.FileDialog(3)

        With FileDialog
            .AllowMultiSelect = False
            .Title = "Please select one or more files"
            .Filters.Clear
            .Filters.Add "Microsoft XLS", "*.XLS"
            .Filters.Add "All Files", "*.*"
        End With

        If FileDialog.Show Then
            Dim i As Integer
            For i = 1 To FileDialog.SelectedItems.Count
                ChoosenFile = FileDialog.SelectedItems(i)
            Next i
        End If

        'Import the choosen file
        If Len(ChoosenFile) > 0 Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL "DELETE FROM tmpPOSSales"
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpPOSSales", ChoosenFile, True
            DoCmd.SetWarnings True
        End If

        'Upload to the invoice
        Select Case Nz(Me!CustomerOrderNoMem, 0)
            Case 1: 'Regular Sales
                DoCmd.RunCommand acCmdSaveRecord
                DoCmd.SetWarnings False
                DoCmd.RunSQL "DELETE FROM trnsalesitem WHERE SalesID=" & Nz(Me!IDmem, 0)
                DoCmd.OpenQuery "trnSalesDetailsPOSQ002 (Regular Sales)"
                DoCmd.SetWarnings True

                Me.trnSalesDetailSub.Requery
            Case 2 'Discounted Sales
                DoCmd.RunCommand acCmdSaveRecord
                DoCmd.SetWarnings False
                DoCmd.RunSQL "DELETE FROM trnsalesitem WHERE SalesID=" & Nz(Me!IDmem, 0)
                DoCmd.OpenQuery "trnSalesDetailsPOSQ004 (Discounted Sales)"
                DoCmd.SetWarnings True

                Me.trnSalesDetailSub.Requery
            Case 3 'Employee Sales
            Case 4 'Employee Credit
            Case 5 'OB Money
            Case 6 'GC Sponsor
            Case 7 'Cash Short/Over
            Case 8 'Reject Sales
            Case 9 'Representation
        End Select
    End If
Advertisements

2 thoughts on “Uploading XLS file using VBA.

  1. Nice code Glenn, how about one click upload those multiple excel files selected to invoice by putting inside a loop and adding DoEvents… I don’t know just my own analysis of the code shown.

    By the way, Glenn can you open up a topic and share your thoughts and experiences on how to bill clients/software estimate/ retainer fee and other management-economic stuff. I think this is also an interesting one. I was deeply moved by the plight of software developers community and software dev’t jobs in our country.

    1. Thanks ron, yep, you can modify the code to allow multiple upload, that is why I retain the for…loops in the sample code.

      Sure, but for Innosoft, I simplified the billing process, rather than “bill for a service model” the client buys a product. In Innosoft, we only have three products: POS, FMIS (ERP-Accounting) and HRIS (ERP-Payroll).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s