Very Hidden For Excel

[skea]

I need some ideas here.
I export my tables to workbook sheets in excel using transferspreadsheet.The different sheets in my workbook are later imported to the corresponding access tables using transferspreadsheet.

1)I want to know how to totally hide my sheets in the workbook so that the users don't tamper with them after export using transferspreadsheet.

 

[skea]

i tried.

worksheets("EContacts").Visible = xlSheetVeryHidden

But when i open my excel file, it says that its already opened. which is true since i first open it using transferspreadsheet then, later i call the fuction that hides the sheets.
When i close and open the file again, my hidden sheets show. This is where iam beaten.

Here is my code.

Function TransferTables()
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Contacts", "C:\ExportedData\ExportedFile", True, "EContacts"
DoCmd.SetWarnings True
HideSheets
End Function

Function HideSheets()
 Dim oApp As Object
 Dim oWB As Object
 Dim oWS As Worksheet
 With oApp
   Set oApp = CreateObject("Excel.Application")
   Set oWB = oApp.workbooks.Open("C:\ExportedData\ExportedFile.xls")
   Set oWS = oWB.Worksheets("EContacts")
   oWS.Visible = xlSheetVeryHidden
   End With
 End Function

Private Sub ImportBtn_Click()
Call TransferTables
End Sub

 

[Jibbadiah]

Try adding a reference to save the file in your loop.

...
oWS.Visible = xlSheetVeryHidden
oWS.Save
End With

oWS.Quit

Set oApp = nothing
Set oWB = nothing
Set oWS = nothing

 

[skea]

Jibbadiah you are always a good boy.
thanks for the trick.
oWS.save
oWS.quit
do not workout
but oWB.save saves the whole workbook, except that my last sheet still
shows up.


cheers.

 

[namliam]

Have you defined the xlsheetveryhidden ???

The xl variables are not automaticaly available in Access.... A pain I know....

Try using oWS.Visible = 2

or declare a public constante in access
Public Const xlSheetVeryHidden As Integer = 2

Then use your line.

Regards

P.S. You should allways QUIT and CLOSE (where applicable) everything before doing '= nothing'.
Be sure to add them in....

 

[skea]

namlian,
the routine works fine whether one uses 2 or the actual xlvariable.
but what i was pointing out is that the sheets before the last sheet are hidden and the last sheet is visible.
i use the loop below

Set oWB = oApp.Workbooks.Open("C:\ExportedData\ExportedFile.xls")
  For i = 1 To oWB.Worksheets.Count - 1
   oWB.Worksheets(i).Visible = xlSheetVeryHidden
   Next i

the only problem now is how i can remove the readonly attribute of my excel file because while trying to import the files back, i have to unhide the sheets but the readonly attribute remains. so it prompts me to save my file under the same file name.

 

[namliam]

Just close it without saving the changes...

Alternatively, save it somewhere, anywhere in a temp folder with any name. Then simply delete it....

 

[skea]

What i have done is that on click of my import button, it unhides the sheets and automatically saves the workbook under other file name in the same folder. So the read only attribute is discarded. Then i use the new file to import the tables.

Whew....
Thanks every one