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
About

Software developer living in the Philippines.

Posted in Microsoft Access
2 comments on “Uploading XLS file using VBA.
  1. ronnie valero says:

    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.

    • hgminerva says:

      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

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: