Free Microsoft Excel 2013 Quick Reference

Vba close without saving changes Results

Okay - I realize that excel is not the best place to do database type work - but it's what we have and work is not about to change it.

I am not a VBA expert by any means and google is my friend when it comes to helping determine the code needed to create a macro.

What I have now is multi macro, formulated file containing multiple spreadsheets that is used for creating sale documents. The workbook is formulated all using defined names so it doesn't matter if things move lines etc.

So what I want is to set up in this file a command button so that when it goes to the sales coordinator they click the button and it will open and specific file - find the last row and populate the columns - if it was a simple copy and paste it would be easy enough to do but the data comes from various places within the workbook. I then want the work book to save and close but that I can handle without a problem.

Hope I have given enough information

If you have some basic code to help with this it would be greatly appreciated.

Thanks

Hello,

I have been noticing since I got migrated to Office2000 that my excel
workbooks with pivot tables frequenty (and unexplainably) will prompt
me to "save changes" when I have just opened them to look at data and
try to close the file again.

NOTE: The workbooks I'm referring to do NOT have any programmed macros
or VBA code.

Here are my observations:

- workbooks with embedded pivot tables are more likely to exhibit the
"phantom save as" behavior that workbooks without pivot tables.

- the behavior usually occurs when you open the workbook and activate
any cell within its pivot table. It appears that excel thinks you
clicked "refresh table".

- by answering "yes" to the "save as" prompt, the behavior (at least
temporarily) goes away. (next time you open it, and close without
changing anything, you don't get asked to "save as")

Anybody else notice this happening?

Hi, I am in desperate need of someone with more VBA knowledge than me (not difficult). I have managed to get the following code (courtesy of Ron de Bruin) working on my workbooks but it isn't quite doing what it needs to and I don't have the skills to cobble from other sources

Sub MergewithAutoFilter()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long, FNum As Long
Dim mybook As Workbook, BaseWks As Worksheet
Dim sourceRange As Range, destrange As Range
Dim rnum As Long, CalcMode As Long
Dim rng As Range, SearchValue As String
Dim FilterField As Integer, RangeAddress As String
Dim ShName As Variant, RwCount As Long
'**************************************************************
'***Change these five lines of code before you run the macro***
'**************************************************************
' Change this to the pathfolder location of the files.
MyPath = "network pathfolder name)"
' Fill in the name of the sheet containing the data.
' Use ShName = "Sheet Name" to use a sheet name instead if its
' index. This example uses the index of the first sheet in
' every workbook.
ShName = "Pipeline"
' Fill in the filter range: A1 is the header of the first
' column and G is the last column in the range and will
' filter on all rows on the sheet.
' You can also use a fixed range such as A1:G2500.
RangeAddress = Range("A1:I" & Rows.count).Address
' Set the field that you want to filter in the range
' "1 = column A" in this example because the filter range
' starts in column A.
FilterField = 1
' Fill in the filter value. Use the "<>" if you want to
' filter on the absence of a term. Or use wildcards such
' as "ron*" for cells that start with ron, or use
' "*ron*" if you look for cells where ron is a part of the
' cell value.
SearchValue = "<>"
'**********************************************************
'**********************************************************
 
' Add a slash after MyPath if needed.
If Right(MyPath, 1) <> "" Then
MyPath = MyPath & ""
End If
' If there are no Excel files in the folder, exit.
FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
' Fill the myFiles array with the list of Excel files in the
' folder.
FNum = 0
Do While FilesInPath <> ""
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
MyFiles(FNum) = FilesInPath
FilesInPath = Dir()
Loop
' Change application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
' Select workbook and worksheet to paste the copied date into
Set BaseWks = ActiveWorkbook.Sheets("BaseData")
rnum = 1
' Loop through all files in the myFiles array.
If FNum > 0 Then
For FNum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MyPath & MyFiles(FNum))
On Error GoTo 0
If Not mybook Is Nothing Then
On Error Resume Next
' Set the filter range.
With mybook.Worksheets(ShName)
Set sourceRange = .Range(RangeAddress)
End With
If Err.Number > 0 Then
Err.Clear
Set sourceRange = Nothing
End If
On Error GoTo 0
If Not sourceRange Is Nothing Then
' Find the last row in target worksheet.
rnum = RDB_Last(1, BaseWks.Cells) + 1
With sourceRange.Parent
Set rng = Nothing
' Remove the AutoFilter.
.AutoFilterMode = False
' Filter the range on the
' value in filter column.
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue
With .AutoFilter.Range
' Check to see if there are results
' after after applying the filter.
RwCount = .Columns(1).Cells. _
SpecialCells(xlCellTypeVisible).Cells.count - 1
If RwCount = 0 Then
' There is no data, only the
' header.
Else
' Set a range without the
' header row.
Set rng = .Resize(.Rows.count - 1, .Columns.count). _
Offset(1, 0).SpecialCells(xlCellTypeVisible)
 
' Copy the range and the file name
' in column A.
If rnum + RwCount < BaseWks.Rows.count Then
BaseWks.Cells(rnum, "A").Resize(RwCount).Value _
= mybook.Name
rng.Copy BaseWks.Cells(rnum, "B")
End If
End If
End With
'Remove the AutoFilter
.AutoFilterMode = False
End With
End If
' Close the workbook without saving.
mybook.Close savechanges:=False
End If
' Open the next workbook.
Next FNum
' Set the column width in the new workbook.
BaseWks.Columns.AutoFit
 
 
MsgBox "Look at the merge results in the workbook" & _
"after you click on OK."
End If
' Restore the application properties.
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub
What I would like to do is to check whether the worksheet that is receiving the data (the BaseData sheet specified in the code) is empty (apart from header row which starts at A1) and, if it isn't, to clear the data before pasting anything from the other workbooks.

I would also like, if possible in the same macro to then format the pasted cells with borders all round. The columns in use are always A:J but the number of rows may vary (and there may or may not be data in any of the cells in Col J.

TIA.
Nicki

Hi,

I'm a newbie in VBA, search alot of post about how to copy data from child workbook to master workbook by searching matching records without overwritng the existing data.

What i was trying to do is copy all the records from child workbook by searching matching records(company code) from the master workbook. After found the matching records then it will insert the data from child workbook to master workbook.

I was only able to insert the data at the end of the rows. Can anyone help me write marco on how to search matching records then insert the whole row into the matching records.

Sub Merge()
    Dim i As Integer
    Dim Source As Workbook
    Dim Destination As Workbook
    Dim myFileName As String
     
     
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
     
    On Error Resume Next
     
    Set Destination = ThisWorkbook
     
     
    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "C:Documents and SettingsAdministratorDesktoptestslave"
        .FileType = msoFileTypeExcelWorkbooks
         
         
        If .Execute > 0 Then 'Workbooks in folder
            For i = 1 To 3 '  Loop through all
                 'Set Paste target first
                myFileName = Dir(.FoundFiles(i))
                
                'Open Souce file
                Set Source = Workbooks.Open(.FoundFiles(i))
                With Destination.Sheets(1)
                     '''''' Here I would then have to make an if to do a different selection based on what input file I have
                    Select Case myFileName
                    Case "Slave1.xls"
                        'Here selection for first file
                    Case "Slave2.xls"
                        'Here selection for second file
                    End Select
                    Set Tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
                End With
                 'Copy and paste to destination
                Source.Sheets(1).Range("A4:D20").Copy Tgt
                 'Close source file without  saving
                Source.Close False
            Next i
             
        End If
         
    End With
     
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.CutCopyMode = False
End Sub
Please help!!!

is it possible to copy one sheet in a workbook onto a new workbook then save it but not open the file?
I know i can open, save then close all in vba, but is there a way to do it in the background?

my current code looks like this

BidFile = Workbooks("Bid Sheet Creator").Sheets("Bid
Sheet").Rows(3).Columns(2)
Application.CutCopyMode = False
Sheets("Bid Sheet").Copy
ActiveSheet.Name = BidFile
So i am copying my "Bid Sheet" to a new file and giving it a name. I have code below this saving the file then closing it but i am wondering if there is a way to change my above code to do it all in the background?

Thanks in advance

I have a VBA code that copy a number of workbooks linked to a masterdata workbook to a new location, while changing all links to a new masterdata location. This works fine on one computer, but not on two other computers on the same network?

The code performs the following actions:

1) Opening the masterdata workbook
2) Copying all workbooks to a new location (Except the masterdata workbook)
3) Opening all files from the new location
4) Saving the masterdata workbook to a new location
5) Close all files

As described this works just fine on one computer, but when I run the code from two other computers on the same network, I get an error when opening the files from the new location. They can't find the masterdata workbook, which is still with the original name in the original location? So I loose the link when copying to the new location.

As a result saving the masterdata workbook to the new location does not update the link.

I tried doing the process manually, and I get excactly the same error on the two computers, so I guess it can't be the code, which is the problem.

All computers are running Excel 2010. The computer without error runs an English version, while the other two runs a Danish version.

I have gone through the Excel options to see if there was any difference there, but I can't see any.

Please help me!

Best regards, Lars

Hi,

I've got a macro that formats certain excel tables and then automatically pastes them into a blank word document where they can easily be cut and pasted into a report.

However, if another word document is open when attempting to close out of the newly created word doc I get a series of prompts to save the normal.docm template.

Is there a way to change the VBA code to exclude the option to save the normal.docm template and allow the user to just close out of the file when finished.

Note: This macro will be used by many novice users and so changing the normal.docm save option in word options isn't an ideal solution.

Here's the code:

    
Set AppWord = New Word.Application
    AppWord.Visible = True
    Set DocWord = AppWord.Documents.Add
    For Each ws In ActiveWorkbook.Worksheets
      If ws.Range("A11").Value = "INCOME STATEMENT" Then
        ws.Activate
        ActiveSheet.Range("A1:E132").Copy
        AppWord.Selection.PasteSpecial DataType:=wdPasteMetafilePicture,      Placement:=wdInLine
        Application.CutCopyMode = False
      End If
    Next ws
    Set AppWord = Nothing
    Set DocWord = Nothing
    ActiveWorkbook.Close False
Thanks for the help.

Hi,
Can someone please remind me the VBA code for autosaving the active workbook
with the same name without beeing prompt by windows ("do you want to save
the changes you made to "aaa.xls") ?
TIA
J_J

Hey guys (and gals),

I'm really frustrated, I had this working, lost my code, and now I can't figure out where I going wrong trying to get it working again. The code below opens Microsoft Word, uses mail merge, then outputs the Word Doc in PDF, and it names that PDF and saves it to a specified location...

The first time I run the Macro it works perfectly, but the second time I run it it either crashes or Excel freezes on me. If it crashes and I hit debug, and then run the macro again without changing anything- it works perfectly. I'm really stumped here and frustrated.

When I step through it...every time the mail merge runs it is creating a new document (IE "Letter1") and each subsequent run of the macro creates Letter2, Letter3, etc. I don't know why it's doing this. Originally the way it worked it just used the source Doc for mail merge without creating a new document...but I can't seem to get it working again.

Lastly...as you can see in my IF Len(Dir function...if the file exists it creates another file with "-2" on the end of the file name. I would like to make it such that it continues this...(either up to -10 or infitinitely if possible) but my skills are limited so I just have "-1" and "-2". Thank you all for your time!

Sub
Merge()

    Dim wd As Object
    Dim wdocSource As Object
    Dim strWorkbookName As String

    On Error Resume Next
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then
        Set wd = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set wdocSource = wd.Documents.Open("C:Documents and Settingschristopher.klineDesktopWork
FilesProjectsPendingAutomated Bank MemoAIB Bank Memo.doc")

    strWorkbookName = ThisWorkbook.Path & "" & ThisWorkbook.Name
    wdocSource.MailMerge.MainDocumentType = wdFormLetters

    wdocSource.MailMerge.OpenDataSource _
        Name:=strWorkbookName, _
        AddToRecentFiles:=False, _
        Revert:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
        SQLStatement:="SELECT * FROM `Data$`"

    With wdocSource.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        
        With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
        End With
        
        .Execute Pause:=False
    End With

    wdocSource.Close SaveChanges:=True
    wd.Visible = False

    Set wdocSource = Nothing
    Set wd = Nothing

    If Len(Dir("P:LNL Finance 2008BANK MEMOSCreated and Sent" & "" & "BankTEST- " &
Format(Date, "mm-dd-yy") & "-1.pdf")) Then
        ActiveDocument.ExportAsFixedFormat OutputFileName:="P:LNL Finance 2008BANK MEMOSCreated and Sent" &
"" & "BankTEST- " & Format(Date, "mm-dd-yy") & "-2.pdf", ExportFormat:=
_
        wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
        BitmapMissingFonts:=True, UseISO19005_1:=False
    Else
        ActiveDocument.ExportAsFixedFormat OutputFileName:="P:LNL Finance 2008BANK MEMOSCreated and Sent" &
"" & "BankTEST- " & Format(Date, "mm-dd-yy") & "-1.pdf", ExportFormat:=
_
        wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
        BitmapMissingFonts:=True, UseISO19005_1:=False
    End If

    ActiveDocument.Close (False)
    Word.Application.Quit

End Sub


Intro
It's a long story. If anyone has some suggestions, I'd be very grateful.

My workbook works fine in Excel 2003 and in Excel 2007 compatibility mode. The problems start showing up when the file is converted to the 2007 format.

When the original xls file is converted to xlsm format and then reopened using the Trust Center option "run all macros without notification", I get the error:
run-time error 57121 (Application-defined or object-defined error)which then proceeds to crash Excel entirely if I select the "Debug" option.

However, if I press the "End" option when the error appears, there doesn't appear to be any further problems. Also, the error never appears if the Trust Center is set to "disable all macros with notification" and I manually enable the macros when opening the file.

This led me to suspect event code is being executed before the entire application is fully compiled, and I found the following problem.

Incomplete Compilation?
When I edit the VBA code so that the editor flags it as needing to be compiled (simply adding a comment works), and I close the file without compiling, then the above problem goes away. I also noticed that compiled VBA doesn't stay compiled when re-opening the xlsm file.

ISSUE (and solution?) #1: Do not save the file when fully compiled. Instead, make sure it needs compiling before being saved.

Unfortunately, after fixing the above problem, a more serious problem became forefront when the file is opened:

"Compile Error: Circular dependencies between modules".
or "Compile Error: Member or data not found".
As with the original problem, this happens only when the Trust Center is set to "run all macros without notification". This time I could tell where in the compile sequence it failed, as it brought up VBE automatically, pointing to the problem subroutine in break mode. Turns out it couldn't find one of the worksheet objects. To fix, I had to replace code such as

with the code
In other words, the worksheet code-named InputSheet was not yet properly visible to the compiler. It was visible if I stopped
execution and recompiled, but by then, the initialization routines had already aborted. By using the indexed Worksheets
method, it resolves objects at run-time and I was able to avoid having the compiler having to know about the object, but this
is obviously a serious flaw. Also if a user changes the name of the worksheet tab, this workaround will fail, which is why I
prefer using the worksheet's code-name.

ActiveX and Worksheet Event interactions
Although it seemed that things were getting better, turns out I was mistaken. ActiveX and worksheet events were plaguing the whole system. The events would appear to execute OK but when the file was saved and then re-opened, Excel would immediately crash. These are normal ActiveX controls such as Microsoft's ComboBox.

ISSUE 2: Event triggered code can damage the file because an object isn't yet initialized. This problem exists on ActiveX events as well as worksheet events. The damage occurs before (during?) the filesave, but Excel crashes immediately after the workbook is reopened with "run all macros without notification".

I ran into variants of issue 2 when opening the xslm file using the file converter for Excel 2003. (Remember that the original file fully functions in Excel 2003). When converting the xlsm file back to Excel 2003, I get the following error:
Uninitialized ActiveX controls will not be opened in this version of Excel.
Can't exit design mode because Control 'OK_Button' can not be created.
The worksheet code-name InputSheet that triggered the "missing object" during compile has ActiveX controls. So I thought maybe I simply need to rebuild the controls from scratch or delete the event code for the ActiveX controls. Neither option worked. Things would work the first time the file was opened, but after saving and reopening it, Excel would crash immediately. No luck trying to find out what aspect didn't work.

Sorry about such a long bug report, but I thought the more info, the better the chances something I observed might have been seen before. If this can be fixed, it would be wonderful. In the meantime, I can continue to operate using the .xls compatibility mode.

Also posted on MSDN

hello all,
I am having a recurring problem:
My Excel suddenly becomes VERY SLOW, I mean it takes a around 45 - 55 seconds to do the following (it used to do it in 2 - 5 seconds:

	VB:
	
ActiveChart.location Where:=xlLocationAsNewSheet, name:=chart_name 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
or this one

	VB:
	
Charts.Add 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Everything else works at the speed it always did but these two chart codes take much longer.
That part of the code :
1-creates an object chart in the active sheet then
2- exports the chart as a jpg
3- moves the chart to another sheet (the reason I am doing it this way is a printing issue)

then repeats this for 2 more charts
then displays the charts on the userform.

... But once this lag happens nothing helps, I mean I can restart my PC 10 times with no effect.
Even without using VBA and doing the location changing of the chart takes that long.
it is not specefic to one file.
and even if I created an empty chart object then tried moving the location to a sheet, it takes the same time.
I had this happen once before and I eventualy decided to REPAIR Microsoft office, and that fixed the problem for 1 day, today the same problem reoccured??
Has anyone encountered this?
any ideas of things I might check?
Could it have something to do with AutoSaving or AutoRecovery (i am not sure what it is called) since that takes about the same time as what is happening? How can disable that?

UPDATE:
I opened a workbook, went to options and disabled Auto Recover (checked) and disabled the SAve Auto recover info every (unchecked), saved the workbook. closed it . reopened it, made an empty object chart, changed the location to a sheet named "trial" and pressed ok. And it took the same amount of time 46 seconds to do the change location. So I guess it is not an Auto Recover issue!!!!

Layth

Hi all,
I have created a worksheet where column B has a drop down menu that gives the user options, however, if the user selects "Other" as their option from the drop down in Column B, I need to make the corresponding cell in Column F mandatory. For example, if someone selects "Other" in B3, then they need to explain by entering something in F3. I don't want it to be left blank. I would like for there to be a pop up at least. At best, I'd like for it to be impossible to close the workbook without entering something in column F.

I'm completely new to VBA, so if your answer is to do something with that, you're going to have to walk me through it. I tried some formulas before and nothing worked for example, I tried this one:

I first created a formula in Column G and copied it down the column that read:

=IF(AND(B2="Other", F2=""),"Yes","No")

I then entered this code in VBA:


	VB:
	
) 
     
    If Range("G").Value = "Yes" Then 
        MsgBox ("Please leave a comment") 
         
        Cancel = True 'cancels the save event
    End If 
    If Range("G").Value = "No" Then 
        MsgBox ("Please leave a comment") 
         
        Cancel = True 'cancels the save event
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I then saved this in VBA, went to the worksheet, selected "Other", left the comments field blank, and went to save the document, and not only did it let me, it let me close the document as well. No message box, nothing.
I even tried changing my values in VBA to a single cell just to make sure; meaning that instead of just saying "G", I specified "G2", and tried again. Still nothing. Do I have to do something to make the sub macros run?
Do you have a better solution? Please help!

FIRST AND FOREMOST:Apologies on if the title is not correct, I do not whatsoever understand all of the lingo that is applied to VBA, I am already roughing it as it is. If there was a way to "Preselect a title", I would be more than happy to use that.

Okay, I have these codes in place in my excel file, there is nothing wrong with them at all. Here is the the problem : My superior runs the macros in place in the file almost daily, and when he is done he then closes it and all is well.
The macros does things to other files so they are changed in a way that the supervisors like them. I avoided putting macros in these individual files, because of the attempt to make it as simplistic as possible without them being able to affect overall file.
The problem happens when he (my superior) accidently makes a change in the primary sheet in the workbook with all the macros, it affects alot of my formulas and macros that are already in place. Within this workbook, it keeps a log of things from the other files results, so I have to have a record of it in the workbook, hence having a save event in place.

The Question: Is it possible to create a macro button, that will allow him to disable the codes below, so that he can close it and open again, so that it doesn't show any of the errors that happens by accident and try to close it again so there is no errors? Basically, I am asking what is the code for this, and where would I place it in.

	VB:
	
) 
    Dim mylogoff 
    Set mylogoff = Sheet1.Range("A65536").End(xlUp).Offset(0, 1) 
    mylogoff.FormulaR1C1 = "=NOW()" 
    mylogoff.Copy 
    mylogoff.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = Fals 
    mylogoff.Offset(0, 1).FormulaR1C1 = "=RC[-1]-RC[-2]" 
    Application.DisplayAlerts = False 
    Sheets("Start Here").Select 
    ThisWorkbook.Save 
    ThisWorkbook.Close 
End Sub 
Private Sub Workbook_BeforeSave _ 
    (ByVal SaveAsUI As Boolean, Cancel As Boolean) 
     'comment this out to be able to save as a seperate name
     'this macro will allow to save elsewhere but not allow file name changes
    Dim NamePath As String 
    Dim strName As String 
    Dim lFind As Long 
     
    If SaveAsUI = True Then 
        Cancel = True 
        With Application 
            .EnableEvents = False 
            NamePath = .GetSaveAsFilename 
            strName = Mid(NamePath, InStrRev(NamePath, "", -1, vbTextCompare) + 1, 256) 
             
            If NamePath = "False" Then 
                .EnableEvents = True 
                Exit Sub 
            ElseIf strName  Me.Name Then 
                MsgBox "You cannot save as another name" 
                .EnableEvents = True 
                Exit Sub 
            Else 
                Me.SaveAs NamePath 
                .EnableEvents = True 
            End If 
        End With 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I hope this is somewhat explanitory enough, as I have had to figure out what he was attempting to do. He still should be able to have access to add and remove contents within the workbook. He just doesn't mess with the VB side of it, and I would like to keep it that way.
And yes, I would like to avoid having a msg box appear asking if he wants to save it. I prefer that it gets saved everytime regardless. Unless of course, it affects the whole workbook

obtw, HAPPY NEW YEAR everyone!!!!

Thanks in advance,
David

I have a workbook that is saved under many different file names that users use to update sales forecasts. From time to time, I have to update the workbooks to make changes. Instead of opening each workbook, making the change and saving/closing, I created a macro to open each workbook, perform a macro (below listed as "Personal.xls!Macro10") and save/close.

What I need to do within the "Personal.xlsMacro10" is toggle between a workbook I'll call "Test.xls" and the workbook I've opened. Without adding this step into each line of code for each named workbook, can I use VBA to copy a worksheet in "Test.xls", switch to "116.xls" (this example) without calling out "116.xls" ? I tried 'Thisworkbook' but that didn't work. This would save me hours of time if someone can assist. Thanks


	VB:
	
 dailysalesfix() 
     '
     ' dailysalesfix Macro
     
    Application.ScreenUpdating = False 
    ChDir "U:AccSalesDAILY SALES & MDS2007 SPRINGENT01" 
    Workbooks.Open Filename:= _ 
    "U:AccSalesDAILY SALES & MDS2007 SPRINGENT01116.xls", UpdateLinks:=3 
    Sheets("Sales Forecast").Select 
    ActiveSheet.Unprotect Password:="profit" 
    Application.Run "PERSONAL.XLS!Macro10" 
    ActiveSheet.Protect Password:="profit" 
    ActiveWorkbook.Save 
    ActiveWorkbook.Close 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Environment - XP/SP2 or 2000, Excel 97-2003, Excel experience - I get by, VB experience - pretty good, VBA experience - just started.

I am using the "count cells by color" VBA I found on the FAQ here, to count only the correctly color-coded cells within a large range, testing on cell.interior.colorindex and using cell.value, and it works a treat : D , but, changeing background color is not an event so does not force recalculation, as confirmed by looking through the FAQ here.

If I "dirty" a cell within the range of the function, it recalcs fine. But I'm writing this application for others to use, and want to specify a method whereby they can be confident all cells are correct without changing the input data.

So how do I force a recalc of the entire worksheet? I have clicked on a cell and then F9 recalcs that particluar cell for me, but I want all cells recalced in one go.

I have tried F9 with the whole sheet selected, CTRL+ALT+F9, tools->options->calculations Calc Now(F9) or CalcSheet, even save/close and then re-open the spreadsheet, and none of those activities causes the VBA function to update its result. That last failure really surprised me. I notice that in tools->options->calculation there is a "recalute before save" check box, but it's greyed out for me.

What obvious thing am I missing?

Clicking on a cell and F9 does NOT recacluate, you have to select a cell, then click in the formula bar, then F9, then it works. I've spotted the greyed out "recalc before save" thing, I was on auto recalc. Changing that to manual gave me the option, but, it still doesn't recalc on save. The ONLY thing that recalcs the function seems to be to dirty the data in range, nothing else. Euggh!

Yet once again I come before the great VBA wiz's out there.

I have a problem that thwarts me from every angle. The basic setup is this:

[tadbit of background history]
I initially tried having 1 file shared, but at times data being saved at same time caused unexpected crashes and odd results. So we have broken our files into individual ones. It works, but is annoying because we know have multiple excel files open, when only one should be necessary, and cannot update. This limits our productivity.

[current change trying to work]

on a network location I have two files. One is the controller file, the other is the database file. The controller gets saved on the local machine under an All Users profile. The database is currently unshared because of a SQL query (which can be popped out to its own workbook if needed).

The problem is saving at the same time. The database of course kicks out that so so has it locked and is read only. OK fine, so I toyed with some code presented on this site to check if workbook is open. This did nothing, my guess is that the code works only if in a local machine environment, or that saving at the same time initiated the check at the same time, both pass the test so then they both try to access the closed database.

So second test was to have a third file called DBAVAIL. When a user wants to use the database it would save that file as DBINUSE and close. Then the filesearch for DBINUSE would see it and stop the rest of my process. The problem with this is I couldnt figure out a way to (preferably) rename DBAVAIL to DBINUSE and then back again, or delete this added excel file from the network location. Because this only works once, without the aid of someone manually removing it from the folder.

So third and final test was having a shared workbook and then testing a cell inside of it ie if "A1"="in use" or blank. Two problems, file reads as readonly, even when it is shared. (which I believe has more to do with the second problem) The simultaneous event calls for saving which just bugs up the entire thing, and even if it goes through (a delay in saves by a few seconds) there is no method to set up a priority for who has control which causes an eventual readonly error when it goes to the appropriate read only database.

Does anybody have anything to solve for this? The second test was the best one but still had its issues.

Help with opening hidden Excel workbook when first workbook opens.

I do pretty well with Excel but the VBA code part has a tendency to confuse me. So I am asking for help.
What I’m looking for is this.

When workbook A is opened, I need workbook B to open in the background. (Preferably invisible to the user)
When user closes workbook A, then workbook B will automatically save itself and close.

Furthermore:

If user accidentally finds workbook B and opens it. I need workbook A to open instead. (Of course workbook B will open in the background)
And:
If workbook B cannot be hidden, and user discovers it open and decides to close it. I need workbook A two automatically close all so.
In other words both workbooks must be open at the same time.
You may ask my purpose here. I am looking to use workbook A as the main workbook and workbook B as a database.
Workbook A will receive changes over time and I can then distribute the new workbook without disturbing the main data throughout the different work terminals. Where workbook B will hold the critical information that is changed periodically by workbook A.
Thanks in advance.

Hi,

I am running into a problem specifying a mail merge range in Excel 2002/2003 that only includes non-blank cells. In Excel/Word 2000 I was able to achieve this by hiding unused rows in Excel before performing the mail merge. This also added simplicity to the Excel “form” by showing the user only rows needing to be filled out at the time. In Excel/Word 2002 and beyond, this no longer seems to work, however, one peculiarity seems to make me thing perhaps Excel 2002 and beyond is still capable of limiting a mail merge data source range in this way.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

The actual mail merge is being performed using Microsoft Word VBA code (as I ran into far too many problems in executing the mail merge in Excel) while the Excel VBA code is used for showing/hiding cells, clearing the form, determining the number of printed pages in the final step, and saving and continuing the next step in the mail merge process.

Full details to follow and I’d be more than happy to upload screen captures if that would be of help. If you believe this to be a Word VBA solution I will try and find a Word coding forum in which to ask this question.

Thanks much!

-------------------------------------------------------------------------------------

A year ago I came up with an automation “mini-app” that staff can use to generate the correct number of student aide tags needed for the student aides helping them in their classes. This automation uses a batch script, Excel VBA and Word VBA to complete a mail merge in Microsoft Word.

The batch file copies the Excel and Word documents to the user’s Desktop, launches Excel first, inviting staff to fill out the required columns “TEACHER’S LAST NAME,” “STUDENT’S FULL NAME,” “PERIOD # (INCLUDING SUFFIX),” and “TYPE OF TAG.” There is a textbox in column F that allows users to show or hide cells (eight cells are shown by default), a button to clear the form, a button to find out how many printed pages they should expect (based on a mathematical formula that divides the number of shown cells by eight and another formula that divides the number of filled cells by eight) and a button that saves and closes the Excel document.

Upon clicking the “Save, Close and Continue” button in the Excel spreadsheet, the batch script launches the Microsoft Word document on the Desktop where Word VBA code automatically completes the mail merge with the Excel document on the user’s Desktop and initiates the printing of the completed merged document to the default printer, closes the Word mail merge template document and (upon closure of the Word application) deletes the temporary documents off the user’s Desktop.

The version of Microsoft Word used in our building prior to the upcoming school year was Microsoft Office 2000 (the OS is Windows XP). However, we recently upgraded all of our licenses to Microsoft Office 2003 and I soon realized that I also needed to make a few adjustments to my “mini-app” in order for it to work with the upgraded version of Office.

Everything is now working as it should except for one interesting little bug. In column F of the Excel spreadsheet I added a textbox with some VBA code to hide/show rows in the spreadsheet. By default, only nine rows (eight plus the header row in row 1) show as there are eight student aide tags to a printed page once the mail merge has been completed in Word. The instructors can use the textbox to hide or show additional rows as they need to. This works to simplify the user’s view by allowing them to only show the number of rows needed but also, in Excel/Word 2000, creates a situation where (by default) Word only performs a mail merge against the unhidden cells. This limits the number of pages printed and eliminates the unnecessary printing of blank pages following the completed mail merge page(s).

The rub is this. In Excel 2000 hiding unused rows eliminates the blank pages from being generated in Word by limiting the data source range. However, in Excel 2002 and beyond, hiding rows does not limit the data source range in Word and a number of blank pages (based upon the number of rows unhidden in the first place) are generated following the final mail merge. It doesn’t seem to make a difference if I manually rehide the rows or rehide these using the VBA code associated with the textbox.

For example, the Excel document starts with only nine rows unhidden, and if I precede without unhiding any additional rows only one printed page is generated in Microsoft Word. However, if I unhide say the first 50 rows and then, prior to saving and continuing, decide to rehide all but the first nine rows, six additional blank pages are still generated in Microsoft Word upon the completion of the mail merge. Blank pages occur when unhiding more rows than the user fills out.

What I find interesting about this is that there has to be something saved in the Excel 2003 document upon hiding/unhiding rows. I can temporarily rectify the additional blank pages problem by copying the original, untouched, Excel document and reapplying the mail merge. So I’d deduce that there must be something saved in the original, untouched, Excel 2003 document that allowed Word to limit the data source range to only the non-hidden cells; There must be something that changes in the Excel document when I unhide/rehide cells? However, I have not yet been able to duplicate the results.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

Thanks much!

-Matt-

----------------------------------------------

Here is the VBA code from the Microsoft Excel file:

Excel - Sheet 1 (Generate Aide Tags App):

Option Explicit
Private varNumCellsToShow
Public varReturnDetected

Private Sub btnHowManyPrintedPgs_Click()
Call HowManyPrintedPgs
End Sub

Private Sub btnNumStudsEnter_Click()
On Error Resume Next
Application.ScreenUpdating = False
varNumCellsToShow = txtNumCellsToShow.Text + 2
If txtNumCellsToShow.Text = 65535 Then
MsgBox ("You entered a number greater than or equal to 65535. Showing all 65535 rows.")
txtNumCellsToShow.Text = 65535
varNumCellsToShow = 65536
Range("B1").Select
End If
If varNumCellsToShow = "" Then
Exit Sub
End If
Cells.Select
Selection.EntireRow.Hidden = False
Rows(varNumCellsToShow & ":65536").Select
Selection.EntireRow.Hidden = True
varNumCellsToShow = ""
End Sub

Private Sub btnSaveAndClose_Click()
ActiveWorkbook.Save
Excel.Application.Quit
End Sub

Private Sub bttnClear_Click()
Application.ScreenUpdating = False
ActiveWindow.SmallScroll Down:=-33
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B2").Select
End Sub

Private Sub txtNumCellsToShow_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
varReturnDetected = 1
Call btnNumStudsEnter_Click
End If
End Sub

Private Sub txtNumCellsToShow_LostFocus()
If varReturnDetected 1 Then
Call btnNumStudsEnter_Click
End If
varReturnDetected = 0
End Sub

Excel - Module 1:

Option Explicit
Private mycount As Long

Sub HowManyPrintedPgs()
Dim FinalRow
Dim LastFilledRow
Dim NumberofPages As Integer
Call NumberUnhiddenBlank
If mycount = 0 Then
FinalRow = (Range("B65536").End(xlUp).Row)
If FinalRow 7 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 student aide tags to a page and you have elected to print " & FinalRow - 1 & " tags. Tags will print to " & NumberofPages & " pages.")
End If
End If
If mycount > 0 Then
FinalRow = (Range("B65536").End(xlUp).Row) + mycount
LastFilledRow = (Range("B65536").End(xlUp).Row)
If FinalRow 2 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 tags to a page, you have elected to print " & LastFilledRow - 1 & " tags. IMPORTANT NOTE: You have " & mycount & " blank rows at the bottom of your form. To reduce the number of pages printed, please be sure to adjust your Number of Tags to Create textbox settings. Tags will print to " & NumberofPages & " pages.")
End If
End If
End Sub

Sub NumberUnhiddenBlank()
Dim c As Range
mycount = 0
For Each c In Range("B1:B65536")
If Rows(c.Row).Hidden = False And c.Value = "" Then mycount = mycount + 1
Next c
'MsgBox ("The number of unhidden, blank, rows is " & mycount & ".")
End Sub

Here is the VBA code from the Microsoft Word file:

Option Explicit

Private Sub ActivateMailMerge_Click()
Call Document_Open
End Sub

Private Sub Document_Open()
Dim UserName
UserName = Environ("username")
MsgBox ("This step will take a couple of seconds. Please hang on...")
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:Documents and Settings" & UserName & "DesktopStudent Aides.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
:="", SQLStatement1:=""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

Dim NumPgs As Long

NumPgs = Selection.Information(wdNumberOfPagesInDocument)

If NumPgs < 2 Then
Select Case MsgBox("Please insert " & NumPgs & " page of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

If NumPgs >= 2 Then
Select Case MsgBox("Please insert " & NumPgs & " pages of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

End Sub

Hi Guys,

This is something someone asked me and told me it's possible without VBA. I don't know if it is. I'm sure if it is possible, someone here would definitely know!

I have workbook, which I can not protect. It's sort of a template, so is used again and again with different data. It's accessible to many users. However, I don't want any changes in that workbook once it is closed.

For example, a user opens the workbook, he makes changes in the data, takes the outputs and uses it somewhere else. Now, when he closes it, it should revert back to the same as it was when it was opened. Even if the user saves it and closes, it should remain the same.

Is there any way to do this. Eagerly awaiting replies !!

Thanks !!

I need to do similar to the INDIRECT() function, to closed workbooks.
Obviously indirect doesn't work for this. The trouble being that the user of
the worksheet will always want to change the settings of the sheet, looking
at diffierent names, values etc. It is not practical to have all this
information stored on 1 sheet, so it is saved in different sheets, and a
number of workbooks.

Doing a search I found a similar problem from someone back in Feb and
followed the answer, I downloaded PULL() and put it in the VBA for the
workbook.

=pull("'S:Close Assistance"&Main Files&""&Field Managers&"["&Field
Managers.xls&"]"&YYComp&"'!"&C65517) (C65517 contains "E25") returns #NAME?

Tried without all the "& stuff in the middle but to no avail.

Can anyone assist me with this matter? Also, why isn't there a built in
function in Excel that already does this?


No luck finding an answer? You could always try Google.