none
How can I use constant path with vba command "DoCmd.TransferSpreadsheet acExport" in access 2013? RRS feed

  • Question

  • Hi

    I want to define the path of the file at the top of the vba code only once, to avoid I need to replace it in access 2013 button for button.  With all vba commands it's not creating problem except the one below

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "9_2_1_PRIO report 2015", "C:\Users\johon.peacock\Documents\Data-base\ALL system Top Defects\Template.xlsm", True, "BGW".

    So I replace this with

    Const MYPATH0 = "C:\Users\johon.peacock\Documents\Data-base\"

    Private Sub Command103_Click()

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "9_2_1_PRIO report 2015", MYPATH0 & "Template.xlsm", True, "BGW".

    End Sub

    It will complain out the path as it's writing info to a template ("Template.xlsm").  Is there a way to use a constant path with command  DoCmd.TransferSpreadsheet acExport?


    Anri

    Monday, June 27, 2016 9:09 AM

Answers

  • Hi HAns,

    Excellent!!!

    I see the problem was ()  I thought they are mandatory. It's working now correcctly  when removing the () in the command below

    sFile = (PATH0 & "All System ALARMS v1.xlsx")  

    best Regards,


    Anri

    • Marked as answer by Anri2018 Wednesday, June 29, 2016 3:36 PM
    Wednesday, June 29, 2016 3:36 PM
  • Thanks!!

    Excellent support and suggestion!!

    Best Regards,

    Anri


    Anri

    • Marked as answer by Anri2018 Wednesday, June 29, 2016 6:46 PM
    Wednesday, June 29, 2016 6:46 PM

All replies

  • 1) Is MYPATH0 declared in the same module as the one you use it in? If not, you should declare it as Public:

    Public Const MYPATH0 = "C:\Users\johon.peacock\Documents\Data-base\"

    2) Does Template.xlsm exist already? As far as I can tell, DoCmd.TransferSpreadsheet will export to an existing .xlsm workbook, but it won't create one. This is different from the behavior for .xlsx workbooks.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, June 27, 2016 2:30 PM
  • Hoi Hans.

    I have tried the command "Public Const" in access 2013, but get every time the warning " Constants, fixed-length,arrays', user-defined types and Declare statements notallowed as Public memebers of objects modules?

    I have tested it with

    Public Const MYPATH = "C:\Users\johon.peacock\Documents\Data-base\"

    Public Const MYPATH  as string = "C:\Users\johon.peacock\Documents\Data-base\"

    Public Constant  MYPATH = "C:\Users\johon.peacock\Documents\Data-base\"

    Public Constant MYPATH  as string = "C:\Users\johon.peacock\Documents\Data-base\"

    Some how access 2013 doesn't like the public statement?  


    Anri

    Monday, June 27, 2016 5:42 PM
  • You didn't answer Hans' question: Where do you define the Const? I use many Consts and define them in a module like this:

    Public Const VERSION = "3.10b"

    I can access them from all modules and all form code


    Best regards, George

    Monday, June 27, 2016 5:49 PM
  • Hi George, Hans,

    Let me think I am defining the costant between other Subs in the code behind access buttons.

    In access you can create via questionnaire a button that is linked to a query or form, after that you do the translation code and implement other changes directly in the vbA code.

    I hope that answers you question?

    regards,

    Anri

         


    Anri

    Monday, June 27, 2016 5:57 PM
  • Ok, you're not doing it in VBA but in some "wizard". In any case, it does make me suspicious that you're not telling us the whole story. VBA or Wizard?

    Best regards, George

    Monday, June 27, 2016 6:23 PM
  • Hi George,

    I am not so familiar with names of the access modules, but you are right I created first a button using command button Wizard in access 2013, and after translation to vba you can access the code directly.  So I define my public Const beween other subs see below

    Private Sub xxxx

    End sub

    Public Const MYPATH = "C:\Users\johon.peacock\Documents\Data-base\"

    Private Sub ddddd

    End sub    


    Anri

    Monday, June 27, 2016 6:36 PM
  • You need to structure your code:

    1. Create a module 'Constants' (or whatever you like)
      Make all Const in that module
    2. Create a module 'Code' (or whatever you like)
      Place your VBA code here
    3. Stop using wizards, they will not teach you anything. Instead you need to learn how VBA works from the ground up. You cannot just wish you can speak Japanese, you must put some effort in it. The same goes with VBA.


    Best regards, George

    Monday, June 27, 2016 6:42 PM
  • So, you are declaring the constant in the same module as the code that uses it. In that case, there is no need to use the keyword Public. But the declaration

    Const MYPATH = "C:\Users\johon.peacock\Documents\Data-base\"

    should be near the top of the module, above all Subs and Functions. Placing it between two Subs, as in your example, is not valid.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, June 27, 2016 6:45 PM
  • Hi Hans,

    I have used some constant declarings for other commands and they are woring fine on top of the same module.  The only problem I have is that command DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "9_2_1_PRIO report 2015", "C:\Users\johon.peacock\Documents\Data-base\ALL system Top Defects\Template.xlsm", True, "BGW".  Isn't allowing the use of a constant .

    I tried it with 

       Const MYPATH2 = "C:\Users\johon.peacock\Documents\Data-base\ALL system Top Defects\Template.xlsm"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "9_2_1_PRIO report 2015", MYPATH2, True, "BGW".  

    It's NOT allowing the use of a constant it wants to see the full path, so maybe a public contant will solve it?


    Anri

    Monday, June 27, 2016 6:58 PM
  • Hans and I have already given you all the answers you need.

    Best regards, George


    Monday, June 27, 2016 7:01 PM
  • Hi Anri,

    >> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "9_2_1_PRIO report 2015", "C:\Users\johon.peacock\Documents\Data-base\ALL system Top Defects\Template.xlsm", True, "BGW".

    Did this code works correctly? In my option, TransferSpreadsheet with acExport supports xlsx, xls and xlsb, and I make a test with xlsm file extension, it did not work. I suggest you try xlsx instead of xlsm.

    >> Is there a way to use a constant path with command  DoCmd.TransferSpreadsheet acExport?

    For a constant path, I suggest you refer suggestions from Hans and George.

    >>It's NOT allowing the use of a constant it wants to see the full path, so maybe a public contant will solve it?

    What is error you got? If you still have issues about this, it would be helpful if you could share us detailed steps and code which we could reproduce your issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, June 28, 2016 6:45 AM
  • Hi George, Hans

    Below my script that is linked to a button in access 2013.  So I can't change or replace the "C:\USers\John...path with PATH0 or PATH1 as debugger starts compaing about it directly.

    Const PATH0 = "C:\Users\John.peacock\Documents\Data-base\2_Linked files\"
    Const PATH1 = "C:\Users\John.peacock\Documents\Data-base\2_Old extracts\"
    '------------------------------------------------------------
    ' Command2_Click
    '
    '------------------------------------------------------------
    Private Sub Command2_Click()
    Dim wkb As Workbook
    Dim FSO, sOutput
    Dim sFile As String
    Dim fndList As Variant
    Dim rplcList As Variant
    Dim x As Long
    MsgBox "Wait for confirmation!!", vbExclamation
     '***************************************************delete file
      sFile = ("C:\Users\John.peacock\Documents\Data-base\2_Linked files\All System ALARMS v1.xlsx")
      Set FSO = CreateObject("scripting.FileSystemObject")
      If FSO.FileExists(sFile) Then
          FSO.DeleteFile sFile, True
      Else
        MsgBox " File not found"
      End If
      'MsgBox "check file!!", vbExclamation
       '***************************************************delete file
    fndList = Array("n/a", "na", "NA", "N/A", "NONE", "NAV", "999", "ALL", "T*", "t*", "test", "")
    rplcList = Array("0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0")
    Set wkb = Workbooks.Open("C:\Users\anri.koekoek\Documents\Data-base\2_Old extracts\All system ALARMS report v5 v2 today with comments.xlsx")
    'Loop through each item in Array lists
      For x = LBound(fndList) To UBound(fndList)
        'Loop through each worksheet in ActiveWorkbook
        wkb.Sheets("Sheet1").Range("F2:I5000").Replace What:=fndList(x), Replacement:=rplcList(x), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
              SearchFormat:=False, ReplaceFormat:=False
        Next x
        wkb.Sheets("Sheet1").Range("I:I").NumberFormat = "@"
        wkb.Sheets("Sheet1").Range("A:AR").RowHeight = 12

      wkb.Close SaveChanges:=True
      FileCopy "C:\Users\John.peacock\Documents\Data-base\2_Old extracts\All system ALARMS report v5 v2 today with comments.xlsx", "C:\Users\John.peacock\Documents\Data-base\2_Old extracts\All system ALARMS report v5 v2 today with comments (" & Format(Date, "mm.dd.yyyy") & ").xlsx"
           
      FileCopy "C:\Users\John.peacock\Documents\Data-base\2_Old extracts\All system ALARMS report v5 v2 today with comments.xlsx", "C:\Users\John.peacock\Documents\Data-base\2_Linked files\All System ALARMS v1.xlsx"
             MsgBox "Confirmation of completion!!", vbExclamation
      DoCmd.SetWarnings True
      'DoCmd.EnableEvents True
    End Sub

    '------------------------------------------------------------


    Anri

    Tuesday, June 28, 2016 9:55 AM
  • I notice that the username is anri.koekoek in one instruction, but john.peacock in the others. Could that be the cause of the problem?

    Apart from that, it should be possible to replace

      sFile = ("C:\Users\John.peacock\Documents\Data-base\2_Linked files\All System ALARMS v1.xlsx")

    with

      sFile = PATH0 & "All System ALARMS v1.xlsx"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, June 28, 2016 2:49 PM
  • No that's not the issue.  It seems that some commands it expect the full path otherwise it isn't working.  The same with command DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "9_2_1_PRIO report 2015", "C:\Users\johon.peacock\Documents\Data-base\ALL system Top Defects\Template.xlsx", True, "BGW".  it will never accept PATH1 & "Template.xlsx".

    Anri

    Tuesday, June 28, 2016 6:29 PM
  • I simply can't believe that. I have often used similar code using a variable to specify the path, and to make sure, I tested it again just now (using a valid path on my PC of course):

    Sub Testing123()
        Dim strPath As String
        strPath = "C:\Users\gebruiker\Documents\Excel\"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
            "tblShippers", strPath & "Shippers.xlsx", True
    End Sub

    It worked without a hitch.

    BTW "john.peacock" or "johon.peacock"?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, June 28, 2016 7:28 PM
  • Hi Hans, Excellent!

    It working with setting up the variable in the sub, but for the delete section it's not possible to include it?   I have tested it with the command below but get warnings?

     '***************************************************delete file
      sFile = ("C:\Users\John.peacock\Documents\Data-base\2_Linked files\All System ALARMS v1.xlsx")

    ' sFile = (PATH0 & "All System ALARMS v1.xlsx") with this I get still warnings?
      Set FSO = CreateObject("scripting.FileSystemObject")
      If FSO.FileExists(sFile) Then
          FSO.DeleteFile sFile, True
      Else
        MsgBox " File not found"
      End If
      'MsgBox "check file!!", vbExclamation
       '***************************************************delete file


    Anri

    Wednesday, June 29, 2016 2:10 PM
  • It should work for deleting a file too. I tested the following code, using a path and a filename on my computer:

    Sub TestDelete()
        ' The value of PATH0 must end in a backslash \
        Const PATH0 = "C:\Users\gebruiker\Documents\Excel\"
        Dim sFile As String
        Dim FSO As Object
        sFile = PATH0 & "Book2 - Copy.xlsm"
        Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FileExists(sFile) Then
            FSO.DeleteFile sFile, True
        Else
            MsgBox "File not found"
        End If
    End Sub

    The file Book2 - Copy.xlsm was deleted without an error message.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 29, 2016 2:42 PM
  • Hi HAns,

    Excellent!!!

    I see the problem was ()  I thought they are mandatory. It's working now correcctly  when removing the () in the command below

    sFile = (PATH0 & "All System ALARMS v1.xlsx")  

    best Regards,


    Anri

    • Marked as answer by Anri2018 Wednesday, June 29, 2016 3:36 PM
    Wednesday, June 29, 2016 3:36 PM
  • No, that is NOT the cause of the problem. The parentheses aren't necessary, but they don't do any harm either. I also ran the code successfully with the parentheses.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 29, 2016 4:36 PM
  • Thanks!!

    Excellent support and suggestion!!

    Best Regards,

    Anri


    Anri

    • Marked as answer by Anri2018 Wednesday, June 29, 2016 6:46 PM
    Wednesday, June 29, 2016 6:46 PM