Solved: Automation of excel in word - making file hidden

[sandam]

MY only question is when saving the file is how do I set the files attributes (I need to make it hidden).

this is the code I use to create the excel spreadsheet.

[vba]
'sdir is a directory created in code.
'filepathforxl and excelfilename are constants

Public Const FIELDNAME_1 As String = "Correspondence"
Public Const FIELDNAME_2 As String = "Title"
Public Const FIELDNAME_3 As String = "First Name"
Public Const FIELDNAME_4 As String = "Surname"
Public Const FIELDNAME_5 As String = "Company Name"
Public Const FIELDNAME_6 As String = "Address"
Public Const FIELDNAME_7 As String = "Town"
Public Const FIELDNAME_8 As String = "Country"
Public Const FIELDNAME_9 As String = "Postcode"
Public Const FIELDNAME_10 As String = "DX Number"
Public Const FIELDNAME_11 As String = "DX Exchange"
Public Const FIELDNAME_12 As String = "Fax Number"
Public Const FIELDNAME_13 As String = "E-Mail"
Public Const FIELDNAME_14 As String = "Reference"
Public Const FIELDNAME_15 As String = "Heading"

Private Sub createExcelFileForHeading(sDir As String)
Dim ObjExcel As Excel.Application
Dim Wkb As Excel.Workbook
Dim WS As Excel.Worksheet

Set ObjExcel = New Excel.Application
Set Wkb = ObjExcel.Workbooks.Add
Set WS = Wkb.Sheets("Sheet1")
WS.Range("A1").Value = FIELDNAME_1
WS.Range("B1").Value = FIELDNAME_2
WS.Range("C1").Value = FIELDNAME_3
WS.Range("D1").Value = FIELDNAME_4
WS.Range("E1").Value = FIELDNAME_5
WS.Range("F1").Value = FIELDNAME_6
WS.Range("G1").Value = FIELDNAME_7
WS.Range("H1").Value = FIELDNAME_8
WS.Range("I1").Value = FIELDNAME_9
WS.Range("J1").Value = FIELDNAME_10
WS.Range("K1").Value = FIELDNAME_11
WS.Range("L1").Value = FIELDNAME_12
WS.Range("M1").Value = FIELDNAME_13
WS.Range("N1").Value = FIELDNAME_14
WS.Range("O1").Value = FIELDNAME_15
Wkb.SaveAs FileName:=FilePathForXL + sDir + ExcelFilename
Wkb.Close Savechanges:=True
Set ObjExcel = Nothing
Set Wkb = Nothing
Set WS = Nothing
End Sub[/vba]

[Tommy]

Hi sadam,

The code below will set the hidden bit. I am afraid it will only change the file if it is in the current directory, I didn't investigate further.
[VBA]
Sub SetHiddenBit(filespec)
Dim fs As FileSystemObject
Dim f As File
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(fs.GetFileName(filespec))
f.Attributes = 2
Set f = Nothing
Set fs = Nothing
End Sub
Sub SendFile()
Dim FilNm As String
FilNm = "usat1.dwg"
SetHiddenBit FilNm
End Sub
[/VBA]

[Tommy]

Ok I investigated further this one works across paths harddrives etc. If you have the rights you can do it
[VBA]
Sub SetHiddenBit(filespec As String, PathToFile As String)
Dim fs As FileSystemObject
Dim f As File
Dim p As Folder
Set fs = CreateObject("Scripting.FileSystemObject")
Set p = fs.GetFolder(PathToFile)
Set f = p.Files(filespec)
f.Attributes = 2
Set f = Nothing
Set p = Nothing
Set fs = Nothing
End Sub
Sub SendFile()
Dim FilNm As String
FilNm = "usat1.dwg"
SetHiddenBit FilNm, "c:\acad\"
End Sub
[/VBA]

[sandam]

Thanks Tommy - works an absolute treat and integrates in to my code so easily.

This one is solved

[Tommy]

Glad I could help