Disabling the Save button in the "File"drop down. Also when attempting to close file. Exce 1

[B Shirk]

I have developed a data application in Access and, using a Macro in Excel, import the selected table into Excel. The Excel sheet has formulas and text that need to stay there every time it is used. The idea is for the user to import the data and save it to a new, dated file. I cannot have them "Save" the Excel file. I only want them to do a "Save As" operation so the original sheet remains unchanged.

I've tried a couple of things I found on line but with no success. Any Ideas?

 

[Andrzejek]

Can you save your Excel files (where you have all your logic) as [tt]MyTemplate.[blue]xltx[/blue][/tt] ?
You can do whatever you want with this template, but you cannot overwrite it / Save it. You have to SaveAs


---- Andy

There is a great need for a sarcasm font.

 

[B Shirk]

I tried this but it did not work. The saved file is MyTemplate.xltx. I entered some data and hit save; and it saved.

 

[Andrzejek]

How did you create the MyTemplate.xltx ?
Did you just get 'regular' [tt].xlsx[/tt] file and renamed it to [tt].xltx[/tt]?
Or did you Save the file (in Excel) as [blue][tt]Save as type: Excel Template (*.xltx)[/tt][/blue] ?

When I create the Excel Template, I cannot overwrite it. I have to Save It as some other Excel file. That's why I was suggesting the Template route.


---- Andy

There is a great need for a sarcasm font.

 

[B Shirk]

One more issue with this application.

The excel template has three macro buttons on it. Each one loads a different block of data from a MSACCESS query. When I load one of them, I want to hide the buttons so none of them can be clicked a second time.

Here is the tail end of my code where I do some formatting. What code could I use to hide these buttons.

==========================================================
.ListObject.DisplayName = "Table_PlantData"
.Refresh BackgroundQuery:=False
End With
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Range("C37").Select
End Sub
============================================================

Thanks

 

[SkipVought]

When is the user supposed to push these three buttons to run three query refreshes?

Why not automatically run all three in whatever order you prefer on the opening of your workbook? No buttons required. There are setting for queries to run at specified times.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[B Shirk]

I do run all the queries via an Access macro. After they have finished, I open the Excel template that has the macro buttons that are used to
import the query into the template. The user has to choose which one to import; never all three. Once the import is complete, the user can review
the data then save it to a conventional Excel file.

 

[SkipVought]

1) run all three queries via an Access macro
2) open the Excel template
3) press macro buttons to import the query into the template
4) save your workbook

There is a feature in Excel to be able to query many databases like Access, Oracle, DB2, SQL Server, Excel Sheets, text files, web tables.

Wouldn't it be convenient to simply

1) open the Excel workbook containing these three queries that automatically run.
2) save a copy or the original.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[B Shirk]

/The steps you listed:

1) run all three queries via an Access macro
2) open the Excel template
3) press macro buttons to import the query into the template
4) save your workbook

are exactly what I want to accomplish and the way it is working now. But, after step 3, I want to prevent the user from hitting any of the macro buttons again because this causes a "runtime error".

 

[SkipVought]

You'll need to post all your code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

You may also directly from ms access vba leave user with ready to use copy of existing excel file used as template, with early binding:

Dim xlApp As Excel.Application, xlWbk As Excel.Workbook
Set xlApp = New Excel.Application
Set xlWbk = xlApp.Workbooks.Add("Drive:\Path\FileName.xlsm)
xlApp.Visible = True

You may also create master excel application that does the same (simpler, no new excel application needed: [tt]Set xlWbk = Workbooks.Add("Drive:\Path\FileName.xlsm)[/tt]).
As Skip wrote, please post the code, it has to change something in second run. You may add a line to your macro and programmatically replace the button with info text, or disable the button (if it activex one), or assign dummy macro (forms button) with changed caption.



combo

 

[Andrzejek]

1) run all three queries via an Access macro
2) open the Excel template
3) press macro buttons to import the query into the template
4) save your workbook

So your user has to (1) start Access, run your application in Access in order to run your Access macro queries, close Access, (2) open Excel template, (3) run another logic in Excel and (4) save Excel with data.

Way too many steps for me.

Me, as a user, I want one click (well, maybe a double-click) and in a few seconds I want my data.
Either do all of that in Access, or in Excel - like Skip and combo suggest.

Just my opinion...


---- Andy

There is a great need for a sarcasm font.

 

[B Shirk]

I appreciate your comments. Let me explain this application from the start.

I have a desktop icon that launches a batch file which opens Access and an Access macro that displays a "beginning date" and "ending date" form with a prompt. This is a one click operation. The user enters the desired dates and hits "continue". (the present database starts in 2005) After hitting "continue", Access runs three separate queries, closes Access and opens the Excel Template with some formatting on it and three separate macro buttons. The user then clicks on one of these buttons which imports desired report (result of the query for the desired dates) into the Excel template. From there, the user can save the data to a standard Excel file.

All this works fine. But, if after importing the data the user hits any of the macro buttons again, I get a runtime error. They are not supposed to do that but hey, stuff happens. That is why I want to hide these buttons after the data is imported. I have pasted the code below that imports one of the queried tables.



Range("A1").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\Barry-DTW7\Desktop\Data Collection\PlantData.md" _
, _
"b;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Passwo" _
, _
"rd="""";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transa" _
, _
"ctions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBo" _
, _
"n't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Da" _
, "ta=False;Jet OLEDB:Bypass UserInfo Validation=False"), Destination:=Range( _
"$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("Data Between Dates")
.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\Barry-DTW7\Desktop\Data Collection\PlantData.mdb"
.ListObject.DisplayName = "Table_PlantData"
.Refresh BackgroundQuery:=False
End With
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Range("C37").Select
End Sub

 

[Andrzejek]

How about:

[blue]Cells.ClearContents[/blue]
Range("A1").Select
 With ActiveSheet.ListObjects.Add(...
...

And if user wants to run any macro another 7 times, let them.


---- Andy

There is a great need for a sarcasm font.

 

[combo]

Actually you may not need any code in excel workbook. The design:
1. excel template file (regular "xlsx" excel file that serves as template) with connection to access query (or queries),
2. access application that:
a) asks for dates and internally processes data,
g) creates new excel workbook basing on the template (as in my code above),
c) refreshes links in newly created file,
d) breaks connection ([tt]xlWbk.Connections("ConnectionToAccess_Name").Delete[/tt]).

As a result you get, after single click, ready to save excel workbook with data, without macros and live connections


combo