Good Day,
I created a macro in Excel that works great. It formats multiple sheets exactly as expected, but when it completes it seems like I have to close out four or five worksheets before I can close out of excel completely.
One item of note: I experienced errors when I first built the macro. When I went in to edit the macro, I noticed a ton of temp macros that were never there before. Could I assume this is the problem? Has anyone heard of such a problem? Would it be safe to delete all the temp macros that seem to be hung up?
Here is the code: - Sub VVStats()
-
'
-
' VVStats Macro
-
' modified weekly VV stats excel for DP3
-
-
Dim ws As Worksheet
-
-
For Each ws In Sheets
-
ws.Activate
-
-
Cells.Select
-
With Selection.Font
-
.Name = "Arial"
-
.Size = 11
-
.Strikethrough = False
-
.Superscript = False
-
.Subscript = False
-
.OutlineFont = False
-
.Shadow = False
-
.Underline = xlUnderlineStyleNone
-
.ThemeColor = xlThemeColorLight1
-
.TintAndShade = 0
-
.ThemeFont = xlThemeFontNone
-
End With
-
With Selection.Font
-
.Name = "Arial"
-
.Size = 8
-
.Strikethrough = False
-
.Superscript = False
-
.Subscript = False
-
.OutlineFont = False
-
.Shadow = False
-
.Underline = xlUnderlineStyleNone
-
.ThemeColor = xlThemeColorLight1
-
.TintAndShade = 0
-
.ThemeFont = xlThemeFontNone
-
End With
-
Rows("1:1").Select
-
Selection.Font.Bold = True
-
Rows("1:1").Select
-
With ActiveWindow
-
.SplitColumn = 0
-
.SplitRow = 1
-
End With
-
-
If Not ActiveSheet.AutoFilterMode Then
-
ActiveSheet.Range("A1").AutoFilter
-
End If
-
-
Cells.EntireColumn.AutoFit
-
Cells.EntireRow.AutoFit
-
ActiveWindow.FreezePanes = True
-
-
-
Next ws
-
End Sub
-
Thank you so much for your continued support. I don't know what I would do without sites like this and my personal mentors!
UTS
8 4079 zmbd 5,501
Recognized Expert Moderator Expert
There's nothing in in the VBA code you've posted that should be creating any additional worksheets nor creating any additional code within the workbook.
Personally I would add: ws.Range("B1"). Select
on line 53 to bring your user back to the first cell under your header row.
As for your temporary VBA procedures (Wish MS didn't call these "Macros" in Excel): MAKE A COPY of your workbook so if something goes in the fire you can recover. I highly recommend making backup copies during development. I usually start a new day with a new copy and work with the copy. If making any major changes to something that I already have working, I make a copy first and make the changes there - five minutes for a backup has saved me hundreds of hours of work!
Now go in and delete the "Temporary" procedures
Close and reopen (shouldn't need this step; however, it doesn't hurt)
Run your code - and see if all works as expected
I always make back ups to the back ups, heck yeah. Ok I will try that and let you know, thanks.
Interesting...I can't delete the .tmp items when I go into Macros. The delete button does not activate when I click on them. Oh well. Closing a couple of extra worksheets is still a lot better than what they did before I created the sub, which is open the workbook in the export email as an attachment and manually format each of seven pages! Thanks, I guess we can close this one out. I will do some more research as to why the delete button does not activate for the .tmp macros. Thanks and have a great weekend!
zmbd 5,501
Recognized Expert Moderator Expert
> Question:
Are you saving to a network share folder?
These may be artifact temporary workbooks that may be being left behind for some reason and not actual VBA-Code/Procedures.
The file is created from a database on a network share and added to an email. I open it from the mail attachment before I send it to click/run the macro before I send it.
However, when I perform the same process from a copy of the database on my desktop I experience the same situation.
Interesting.
zmbd 5,501
Recognized Expert Moderator Expert
This could be an artifact of the database:
If it is Access, then how is it making the Excel file? If from VBA, is it creating an Excel-Instance? If it is, then is the code properly closing the Instance?
The other issue could be any add-ins to your Excel.
<Windows><R>
excel /safe
>Open the file with your VBA - enable the macro if requested.
Check to see if there are any *.tmp files showing code
Run the code
Save the file
See if there are any *.tmp files
If not then may be one of the add-ins
Z,
To be honest, I did not understand some of your instructions, so I attempted to start over and delete the current PERSONAL.XLSB (I only had a couple of recorded macros).
When I got to the folder, I saw four temp files in there with the PERSONAL file, so I deleted the temp files and the problem hath been solved.
Thanks for hanging with me! You guys rock!
UTS
zmbd 5,501
Recognized Expert Moderator Expert
! two thumbs up !
Better than my day today... sometimes the instruments in the lab win the hill.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Mrs Howl |
last post by:
I don't know if there's even a way to do what I want. I click on a
button in an Access form, and it opens an instance of Excel, and opens
a workbook and runs a macro that's in it. So far, fine, I know how
to do this. I'm preferring to keep the Excel instance invisible, but
while the macro is running, it would be nice to see some sort of
progress indicator.
(By the way, here's basically what the Excel macro is doing: it's
opening...
|
by: geronimo_me |
last post by:
Hi,
I have the following code in an access module:
Sub Run_Excel_Macro()
Dim xls, xlWB As Object
Dim strFile, strMacro As String
|
by: geronimo_me |
last post by:
Hi,
I am trying to run an Excel macro from an Access module, however when I
run the code the macro runs but then I get an error in Access. The
error is: Run-time error "440", Automation error.
My code is:
Sub Run_Excel_Macro()
Dim xls, xlWB As Object
|
by: modularmix |
last post by:
Does anyone know how to run the Excel Macro for two different spreadsheets in parallel. Here is the code that works sequentially.
Workbooks.Open Filename:="C:\Documents and Settings\Administrator.SLB_EUROPE\Desktop\norm ops 150607.xls"
' Select Asset & Portfolio Parameter sheet
Sheets("Asset & Portfolio Parameter").Select
' Change the number of iterations
Range("D7").Select
|
by: scolivas |
last post by:
I don't know if this is the right place to ask or not...so here it goes...
I have an excel spreadsheet with a macro that refreshes data which is pulled from an external data source, which happens to be Access Database Query which pulls data from an SQL Server DB - when there is an ODBC Failure - all other reports that are scheduled get hung up - is there a way to force the application to quit if there is an ODBC Failure/Timeout? Or any other...
| |
by: NeverLift |
last post by:
This is probably answered elsewhere, but I've searched the Web and VBA for Excel manual, find no answers.
I have a VBA-coded macro in an Excel workbook that is to open another existing workbook -- a .xls file, not .csv -- copy data from it, paste that into the original workbook where the macro resides, then close the source workbook.
If I use the debugger to step through the macro -- putting a breakpoint at its first executable line, run...
|
by: CF FAN |
last post by:
Can Report Builder Export Excel Files with multiple Sheets
How can I create a report that it can export in excel by multiple
|
by: Catbkr1 |
last post by:
I have to automatically create some Excel Spreadsheets based on automatically generated .CSV files that are produced overnight. Each .CSV has several columns that need to be deleted. The same columns (both in location and name) are deleted each time. The macro checks for Column Headers/Names and if the value isn't equal to one of 8 specifc values, I want to delete the column. I am attempting to create a Macro that executes automatically when...
|
by: preciouslife73 |
last post by:
I have read a few available functions similar to my concerns. And I have noticed some were not answered for a long time. My concerns is that what was provided in the forum, when a query or a table has been exported to Excel from Access. It does not open with the Excel Macro in order to run. I was able to transfer the data to Excel and I was able to open the Macro Excel but when it runs, it runs on itself not on the exported file. As of right now...
|
by: preciouslife73 |
last post by:
'actually I have a macro that exports multiple querys or table into many excel spreadsheet with multiple worksheets.
'but this is just to test the Excel Macro to work on the exported files.
'is there any way to create just one RunCode that will work with for all of these files in a ACCESS Macro?
Function testfunction()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Customers",...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |