×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How can I protect spreadsheet after using VBA to get my Access into Excel

How can I protect spreadsheet after using VBA to get my Access into Excel

How can I protect spreadsheet after using VBA to get my Access into Excel

(OP)
I am able to use VBA to get data from an access database into an excel spreadsheet but now I would like to protect the data in excel from being changed. Changes to the data should only be made in access and not in excel. How would this be accomplished?

Using VBA 7.1 - Access 2013 - Excel 2013

Below is my code that works:
What can I insert to prohibit the ability of data being changed in the excel spreadsheet?


Sub test()
'
' test Macro
'

'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\swatier\Documents\Staff1.accdb;Mode=Share Deny " _
, _
"Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:En" _
, _
"gine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB" _
, _
":New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on" _
, _
" Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB" _
, _
":Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("Cell Assignments")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\Users\*******\Documents\Staff1.accdb"
.ListObject.DisplayName = "Table_Staff1.accdb"
.Refresh BackgroundQuery:=False
End With
End Sub


Thank you kindly.

RE: How can I protect spreadsheet after using VBA to get my Access into Excel

Add password to the worksheet after adding and configuring query table:

CODE -->

.Refresh BackgroundQuery:=False
    .Parent.Protect Password:="YourPassword"
End With
End Sub 

combo

RE: How can I protect spreadsheet after using VBA to get my Access into Excel

Please format your code with TGML tags, it is a lot easier to read.

1. Open Excel with some data
2. Click on View - Macros - Record macro
3. Follow steps from this place: How to Lock Cells and Protect Worksheets in Excel
4. Stop recording your Macro
5. Press Alt-F11 to see the code Excel created for you

Any questions about the code - ask here smile


---- Andy

There is a great need for a sarcasm font.

RE: How can I protect spreadsheet after using VBA to get my Access into Excel

I have used Data > Get External Date > From Access... many times and from many differenr databases over the past 20 years or so.

Usually its more than a one time effort. If that’s the case then doing what your code represents need only be done ONE TIME. No VBA required! You just need to Refresh your query.

WARNING: Running this code multiple times on the same sheet will add multiple ListObject/QueryTable objects to your sheet, and can cause unexpected problems.

So I never code an ADD ListObject/QueryTable object. Rather I might use code to change the Connection or the CommandText and Refresh.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How can I protect spreadsheet after using VBA to get my Access into Excel

(OP)
Thank you all for responding.
Sorry about the formatting of the code - this is my first foray into VBA and posting to this forum. Will certainly use the TGML tags for future postings.
Maybe I don't need to use VBA code?
The excel spreadsheet needs to be updated once a month.
The Access database will be updated through out the month.
So maybe I just need to get the external data --> from access whenever I need the current information.
Is it possible to create these steps on a command button?
Thanks.

RE: How can I protect spreadsheet after using VBA to get my Access into Excel

It depends how much automation you need. Having fixed table/query in access, you create manually connection to access once. Next time you only refresh data. It is possible to configure connection so that data is refreshed every time you open the file. With VBA, you can code Workbook_Open event with additional tests for refreshing data. Moreover, with VBA you can protect workbook with UserInterfaceOnly parameter set to True, so only the code can interact with worksheet (needs to be applied every time you open workbook).

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close