0

I have a database that writes data into a copied excel template. The template is hidden to keep the end user from tampering with it, however the final result is also hidden. Is there a way to change the hidden property when saving the new file?

Currently, the db copies the template and renames it.

fso.CopyFile "C:\Upload\Rebate_Upload_Files\Standard Form (Template) 
protected.xlsx", "C:\Upload\Rebate_Upload_Files\Rebate Contract " & 
Contract_Number & " " & Date$ & ".xlsx"

After that, it transfers the appropriate table and saves the file.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, 
"export_table", "C:\Upload\Rebate_Upload_Files\Rebate Contract " & 
Contract_Number & " " & Date$ & ".xlsx", False, "A12:L65000"

The process works fine, except that the final file is also hidden and I'd like it to be a normal file.

Thanks

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Richie2shoes
  • 5
  • 4

2 Answers2

0

Not for CopyFile-which is a FileSystemObject method, but there is one for a File object. We'll just update it after the copy is complete.

For simplicity i've replaced your file output path to a string variable.

originalFileName = "yourStartingFile"
copyFileName = "yourCopiedFile"

set fso.CopyFile OriginalFileName, CopyFileName
 --after copying, get file that was copied
 --set attributes value of file to 0. 0 = Normal, 2 = Hidden
f = fso.GetFile(copyFileName)
f.attributes = 0

More reading for additional details.

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/file-object

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/attributes-property

Mike
  • 624
  • 4
  • 14
0

Mike's code above worked, however there was a few more steps involved for me so I wanted to provide the full code for anyone stumbling upon this.

First, in order to use FileSystemObject, you need to enable it in your VBA Editor by going to Tools->References and enabling "Microsoft Scripting Runtime". Then, using the code below, you can copy a hidden file and set the new file (via f.Attributes) to not be hidden:

' SourceFile is the full path name to my original file
' FileNAme is the full path name to my new file
Dim fso As Scripting.FileSystemObject
Dim f As File
Set fso = New Scripting.FileSystemObject
Call fso.CopyFile(SourceFile, FileName, False) ' Set to true to overwrite
Set f = fso.GetFile(FileName)
f.Attributes = 0
TheoCS
  • 48
  • 4