password issue fron access to excel 2

[chilly442]

Currently this code takes data from my Access database and exports it to a premade Excel file. It will close the file if it is open to allow it to properly transfer all the data, it then reopens it for the user to view the new data and graphs. As it stands that is all working fine.

What I would like the file to do is always be set to read-only so that the users can modify the data however they want and save it to their own file without me having to worry about the data being written over on the original. I had the code simply set the file to read only when it was done updating and to normal when it needed to be updated, but someone can modify that by simply clicking the file and unmarking the check box, so that wont work.

I have now set the file to require a password to modify, which effectively makes it read only without any chance of someone changing the file without knowing the password or deleting the file. The problem I am running into is that I cannot find a way to automate password entry when the first getobject is initiated to allow for data transfer, then when the second object is initiated to reinstate the password.

Thanks for any help you can give.

'Open Excel and Create a graph based on data subform

Private Sub CmdExcel_Click()

Dim objExcel As Object
Dim strFile As String
Dim objCount As Integer

strFile = "C:\File.xls"
objCount = [All subform].Form.RecordsetClone.RecordCount

'Checks to see if there are greater than 50 records and asks user if they would like to import all the data to excel if it is
If objCount > 50 Then
Select Case MsgBox("You are about to send a large amount of data to the excel file. The current graphs may not provide a proper view of the data if it is too large. Do you wish to transfer the data anyway?", vbExclamation + vbYesNo, "Caution")
Case vbYes
'code continues as normal
Case vbNo
GoTo Last 'skips the rest of the code so user can choose a new set of data
End Select
End If

'Grabs excel file saves it then closes it and changes it from read only so that data can be imported even if the file is open

Set objExcel = GetObject(strFile)
objExcel.Application.Visible = False
objExcel.Windows(1).Visible = False
objExcel.Close SaveChanges:=False
Set objExcel = Nothing

SetAttr "C:\File.xls", vbNormal

Data is transferred in at this point using a few transfer spreadsheet functions based on criteria on my Access form

'open the excel file for viewing
Set objExcel = GetObject(strFile)
objExcel.RefreshAll
objExcel.Application.Visible = True
objExcel.Windows(1).Visible = True

 

[ettienne]

Can you place the file in a share on a server where users have read only access? That way you can write to the files (if you have read write access to the share), but users cannot overwrite the original.

 

[Wootoid]

Thanks for the quick response.

This is actually my question, I had my friend post for me till I was able to register. (I personally have very few privileges on the server) I may be able to convince our IT department to allow me to place this on a server, but I would need to have them set that up and they are not always so willing. I will check with them though.

Still I was wondering if there was a way to have a password automatically entered when the GetObject is run, as this is when the box opens asking for a password and the code will not progress until a selection has been made.

If I have not stated my question properly, please ask for clarification. I will try to respond a little quicker.

 

[chandlm]

hi Chilly442,

You will need to change the way you create the excel object

This will open your workbook and automatically pass in the modify password.

Dim objxl As Object
Set objxl = CreateObject("excel.application")

objxl.Workbooks.Open FileName:="Your_Filename.xls", writeresPassword:="Your_Password", ReadOnly:=False

Hope this helps



Matt

 

[chandlm]

Thanks for the star Whoever you were!

Matt

 

[SkipVought]

"Thanks for the star Whoever you were!"

Mrs. Calabash, maybe?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Wootoid]

Well, Chilly and I have been working with the code you gave me and it works great, it bypasses the password exactly like I wanted it to do.

What I am running into now is that when the transfer runs the program freezes. I tried running the transfer by manually entering the password in and then running the transfer alone, and it freezes then too. So it seems the problem lies with transferring with the password to modify set.

This is what I have changed plus the part of the transfer method that freezes the program. The transfer is fairly repetitive, and if this part works it will all work. I have tried a few other methods to no avail, but this seems to be the path I must take.

Set objExcel = CreateObject("excel.application")

objExcel.Workbooks.Open FileName:="C:\Filename.xls", writeresPassword:="password", ReadOnly:=False
objExcel.Application.Visible = False
objExcel.Windows(1).Visible = False


If CheckMajor = True Then
If CheckAll = True Then
DoCmd.TransferSpreadsheet acExport, , "MajorTop All", "C:\Filename.xls", True, "Data"



P.S. I am trying to keep all of the code in Access, so that most of the maintenance that must be done is very centralized

 

[chandlm]

you could try setting the password to be blank then export the data to the spreadsheet before opening the file again and saving it with the modify password again.

code would look something like this

Set objexcel = CreateObject("excel.application")

objexcel.Workbooks.Open FileName:="C:\book1.xls", writeresPassword:="password", ReadOnly:=False

objexcel.Application.Visible = False
objexcel.Windows(1).Visible = False
objexcel.displayalerts = False 'stop do you want to overwrite file warning
objexcel.activeworkbook.saveas FileName:="C:\book1.xls", writeresPassword:="" 'Save the workbook without password
                                                                              'before import
objexcel.displayalerts = True ' turn on warnings again
objexcel.activeworkbook.Close
DoCmd.TransferSpreadsheet acExport, , "Table1", "C:\book1.xls", True, "Data" ' export data to excel file

objexcel.Workbooks.Open FileName:="C:\book1.xls" 'Open the workbook again
objexcel.displayalerts = False 'stop do you want to overwrite file warning
objexcel.activeworkbook.saveas FileName:="C:\book1.xls", writeresPassword:="password" 'Save the file with the modify password again
objexcel.displayalerts = True ' turn on warnings again
objexcel.Visible = True

I have tested this myself and it seems to work ok. Its a bit ugly but at least it lets you do what you want.

Hope this helps

Matt

 

[Wootoid]

Thanks, Matt

You have been very helpful, this is doing almost exactly what I wanted it to do. I had to make it visible before the activeworkbook.saveas would work, but that obviously wasn't a problem. The little bit I have left won't likely be an issue. Just some simple clean up.

I am OK with the code being a bit ugly, it is the last section of my program that wasn't working properly and now it does. So, thank you again.

Aren

 

[chandlm]

My Pleasure,

I like to help out on here as It was an invaluable help to me when I first started out programming.

Cheers.

Matt