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

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

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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)

Answer 9

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

Answer 10

Hans and I have already given you all the answers you need.

---

Best regards, George

Answer 11

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.

Answer 12

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

Answer 13

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)

Answer 14

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

Answer 15

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)

Answer 16

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

Answer 17

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)

Answer 18

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

Answer 19

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)

Answer 20

Thanks!!

Excellent support and suggestion!!

Best Regards,

Anri

---

Anri