Uploading Information

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • forrestgump
    New Member
    • Sep 2007
    • 34

    #1

    Uploading Information

    Previously i uploaded information into my database as xls via vba code in access e.g:

    DoCmd.TransferS preadsheet acImport, acSpreadsheetTy peExcel97, "tbl_MinWag e", "R:\HR\Mini mum Wage\Exports from ADP\NMW CH " & Text33, True.

    By using this method i would be able to save a excel spreadsheet in a folder and add a date on the end of the document e.g. NMW CH 081007.xls

    Then in my access form i would have a text box i.e. Text33 and just add ion the date e.g. 081007 and click a button. The vba code would then select the correct file e.g. NMW CH 081007.xls

    I an now trying the same thing in txt format but it does not work. I get an error. e.g.

    DoCmd.TransferT ext acImportDelim, "tbl_01_BKUK_Ac tive&Withdrawn_ Import Specification", "tbl_01_BKUK_Ac tive&Withdrawn" , "R:\HR\HR_Syste m_Reports_Folde r\ADP_Downloads \HC_RSCUK" & Text33, True.


    Does anyone know why or how I can get around this?
  • nico5038
    Recognized Expert Specialist
    • Nov 2006
    • 3080

    #2
    I miss the ".txt" extension in the text import.
    Are you sure that the "&" in the tablename and the filename causes no trouble?
    What is the message you get ?

    Nic;o)

    Comment

    • forrestgump
      New Member
      • Sep 2007
      • 34

      #3
      Thaks for reply.

      I get error message runtime 31519.

      any ideas? Do you have example code that works for You at all?

      I can get the following code to workto upload a txt file:-

      DoCmd.TransferT ext acImportDelim, "tbl_01_BKUK_Ac tiveWithdrawn_ Import Specification", "tbl_01_BKUK_Ac tiveWithdrawn", "R:\HR\HR_Syste m_Reports_Folde r\ADP_Downloads \HC_RSCUK.txt", False, ""

      However I cannot tailor it so i can get a specify a dated txt e.g. HC_RSCUK_121007 .

      Cheers

      Comment

      • nico5038
        Recognized Expert Specialist
        • Nov 2006
        • 3080

        #4
        Originally posted by forrestgump
        Thaks for reply.

        I get error message runtime 31519.

        any ideas? Do you have example code that works for You at all?

        I can get the following code to workto upload a txt file:-

        DoCmd.TransferT ext acImportDelim, "tbl_01_BKUK_Ac tiveWithdrawn_ Import Specification", "tbl_01_BKUK_Ac tiveWithdrawn", "R:\HR\HR_Syste m_Reports_Folde r\ADP_Downloads \HC_RSCUK.txt", False, ""

        However I cannot tailor it so i can get a specify a dated txt e.g. HC_RSCUK_121007 .

        Cheers
        Found several hits on this error, all indicating that it's the ".txt" that needs to be present in the filename.
        When you get a file named "HC_RSCUK_12100 7", then first use a FileCopy command to create a copy with the ".txt" to the name before processing the TransferText.

        Nic;o)

        Comment

        • forrestgump
          New Member
          • Sep 2007
          • 34

          #5
          Nico,

          Thanks you your response but I am still unsure with regards to this.

          The second part of the file name needs to be dynamic i.e. HC_RSCUK_121007 .txt or HC_RSCUK_171007 .txt depending on what file date I need???

          Apologies in advance if Ihave misunderstood.

          Regards,

          Forrest Gump

          Comment

          • nico5038
            Recognized Expert Specialist
            • Nov 2006
            • 3080

            #6
            Your present desription indicates an import of an .xls
            as your cammand looks like:
            "R:\HR\Mini mum Wage\Exports from ADP\NMW CH " & Text33
            the field Text33 should also hold the file extension: ".xls"
            otherwise you need to change the string into:
            "R:\HR\Mini mum Wage\Exports from ADP\NMW CH " & Text33 & ".xls"

            Getting the idea ?

            Nic;o)

            Comment

            • forrestgump
              New Member
              • Sep 2007
              • 34

              #7
              I can get this to work as a .xls it is when it is a .txt I am having a problem.

              Regards,

              Forrest


              Originally posted by nico5038
              Your present desription indicates an import of an .xls
              as your cammand looks like:
              "R:\HR\Mini mum Wage\Exports from ADP\NMW CH " & Text33
              the field Text33 should also hold the file extension: ".xls"
              otherwise you need to change the string into:
              "R:\HR\Mini mum Wage\Exports from ADP\NMW CH " & Text33 & ".xls"

              Getting the idea ?

              Nic;o)

              Comment

              • nico5038
                Recognized Expert Specialist
                • Nov 2006
                • 3080

                #8
                When the trailing ".txt" is present it should work with the TransferText command, what's the error message?

                Nic;o)

                Comment

                • forrestgump
                  New Member
                  • Sep 2007
                  • 34

                  #9
                  Brilliant!!!

                  Got it working!!

                  Thaks very much for your help on this.

                  Regards,

                  Forrest

                  Originally posted by nico5038
                  When the trailing ".txt" is present it should work with the TransferText command, what's the error message?

                  Nic;o)

                  Comment

                  Working...