Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Runaway Excel

Help! I use Excel (2007) all the time and I've got this strange issue. Anytime I open a spreadsheet, Excel goes crazy and accelerates to the end of the spreadsheet.

Well, actually what I think is causing the problem is one particular spreadsheet. After I open that one - and it is a very simple spreadsheet with no macros - then all subsequent spreadsheets that I open just start running away to the end of the spreadsheet with no mouseclicks or any action on my part.

Anybody ever had this before? Is it a bug? Or some weird control character embedded in that spreadsheet?

Thanks in advance...


Post your answer or comment

comments powered by Disqus
I open excel...and when I go to click in any cell or row or column...the entire column or row highlights and is like a runaway train! Then when I try to shut it down it won't shut down...I have to control alt delete and end task...it then says "Microscoft excel can't quit" and then finally it quits. It's like a crazed animal. HELP! I need to work on the damm file...this happens with any excel files I open...

About half of the time I open an Excel file, I try to position the cursor on
a particular cell and the cursor just runs away, cannot stop it, have to kill
the session with task manager. Any ideas? This is Excel 2003

Hi, I am writing a macro to import excel files from a folder into new sheet that I want to be named automatically after the name of the excel file that was imported.

How do I do that? I know I can use sheet.add; however, how do I name it after the imported file???

Please, let me know how I can do that.

Thank you,

Erzhena

Hi geniuses,

I have little experience using VBA with PPT. What I want to do is relatively simple.

I have two excel sheets with 5 charts, each chart has a specific name.

I have a powerpoint presentation with 10-15 slides, all with different content but five of those slides (slide numbers vary every month) hold the excel charts. Those five slides also have other content like textboxes, etc.

I want to:
1) open the PPT presentation from my hard drive
2) paste the 5 charts into their "places" in the PPT.

Is this possible? How do I "identify" in PPT where each chart goes? Is there a placeholder I can use in PPT or would the excel VBA code include "Top" and "Left" data for each target slide?

If someone could put some sample code I would appreciate it.

Thanks a million,

Onetimesten

First of all, thank you all for your help! Here is the scenario:

I have a primary excel spreadsheet that I work from. The architecture is as follows:

Sheet 1: Called "Information"
Column A: Name
Column B: Date of Birth
Column C through Z: Various bits of information.

Sheet 2: Called "Master"
Cell B1: Contains the date and time of last update from the VBA I am asking for below.

On a weekly basis I get sent a "Update" spreadsheet that is constructed the same way as my primary. This is what I would like to do with some VBA:

From my primary sheet I run the VBA and it opens a pop up that allows me to select the updated worksheet. Next it cycles through both worksheets (Primary and Update). It compares Column A and B, if it finds a match it updates columns C through Z from the "Update". In order to get a match cell A1 and B1 of the primary worksheet has to match Cell A1 and B1 of the update sheet exactly.

The second thing I would like it to do is if the update sheet contains a new entry...in other words the update sheet has a row that does not match the primary it copies the row from the Update sheet to the Primary. In this way, the Primary sheet is always growing with new information and updating any old information it matches.

The last thing I would like to do is copy the current date and time after the comparison is complete to the Primary workbook to sheet "Master" in cell B1. This way I always know the time and date of the latest update. I hope this makes sense. I am sure it is easy for you guys. Thank you again for any help! Brian

First off great website, great resource, im about half way through the free VBA lessons.

I am an engine design analysis engineer and i am trying to (using office 2003):
1st organize my data
2nd organize the data in such a way it can be used for analysis

The data is currently a mess. A test is performed, the results are then placed in a template (which has varied over time) and is placed on any of multiple network harddrives to float around for eternity. We now have the resources(kind of) to create a more efficient system, i am sort of doing this as a side project. My plan of attack is to go through all of my data an apply it to an excel template, each test will be a separate file (say 50-150 total); this will create a "standard" for future use. All the files will then be located on a (probably) shared drive.

I then plan to create an excel "database" file for analysis/organization (filenames are extensive and wont suffice, opening each file is overwhelming given the data). I have many ideas for the analysis side but I'll start with what will be the organization sheet.

After doing some googling and reading here are some ideas I have:
Create a List in the database excel file (which needs to remain functional, i.e. can still sort using List options)
Paste the file directory into a cell within "add data" row of the list (or filename if there is a search directory option)
Write macros that will look-up the file (not sure of the best look-up method yet, I have only found direct windows path look-ups not relative or floating look-ups, i.e. the folder the "database.xls" file is located) and generate the the row within the list (and in the future generate data in other analysis sheets). I did read over this thread http://www.ozgrid.com/forum/showthread.php?t=42979 and would prefer the method of not opening the file when looking up data given their size.

Using this method anyone could apply the test data to the template, add it to the master directory, open the database file, paste the name and be done. I'm not sure the order of operations is consistent with how VBA works (just started working with it today) but I would love to hear some ideas/insight. I do have a contact with someone that writes excel VBA code for a living but im hoping this forum might have a little more patience than my friend.

Future ideas:
Multiple plotting utilities on different sheets, check boxes for what tests you would like to see etc.
I'd like to stay excel based for now but if this goes well it might expand into something access/web based and include data for all of my group's testing (we are a mid-sized company so we have quite a bit of data)

I appreciate any help, ideas, or comments!

I've decided to take up a VBA course and I'm deciding between learning Excel VBA on Excel 2003 platform as well as Excel VBA on Excel 2007 platform. The cost and duration for both courses are the same. Which course would you recommend I take?

-connor

Hi there,

I've recently been writing an excel spreadsheet and have encountered a weird problem.
i have a workbook where buttons open userforms which add and remove worksheets.

Clicking a button opens a userform called DelSysForm which asks weather you want to delete the sheet or not.
the code for the form is


	VB:
	
 Userform_Initialize() 
    Label1.Caption = "Are you sure you wish to delete the system?" & vbNewLine & "This process is not reversible" 
    Noshts = Sheets("Info").Range("TotSysRng").Value 
    NoDel = Range("PgNo").Value 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
NoDel and Noshts are private integers

the coding for the userform button is this


	VB:
	
 
Private Sub Yes_Click() 
    Call DelSheets(NoDel, Noshts) 
    Unload DelSysForm 
End Sub 

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


	VB:
	
 DelSheets(ShtNo, TotShts) 
    Dim i As Long, StrNu As String 
    For i = 1 To TotShts 
        StrNu = CStr(i) 
        If i < ShtNo Then 
             'Do Nothing
        ElseIf i = ShtNo Then 
             'delete sheet
            Sheets("System " & StrNu).Delete 
             
             
        ElseIf i > ShtNo Then 
             'rename sheets
        End If 
         
         
    Next i 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now what happens next is really weird.
When I try to run the userform from the worksheet the code runs smoothly but just after the process is completed excel crashes and i have to restart.
I tried debugging through the whole process and found that it is all working perfectly. the sheet to be deleted is removed etc but then at the end of the process once again excel crashes.
So i tried a few other things. I made a new userform and rewrote the code but once again crashed
I then tried not deleting the worksheet i.e. made the line "Sheets("System " & StrNu).Delete" into comments and presto didn't crash.
I also tried running the userform from vb editor and presto didn't crash
I should also point out that it only crashes when ShtNo is greater than one.

i am completely baffeled. I've been trying for a few hours to figure out what is causing the error to no avail
If anyone has any suggestions please help.
I dont mind changing the code completely if that creates a solution but right now this seems the easiest and best way to do it

Cheers

and by the way where ive put rename sheets is purely because i havent written that part yet

Every day i want to send email to remind about the team activity , i have all the information filled in excel sheet. Macro need to look in to the date column and triger the mail with the subject mentioned in the other column , to the address mentioned in the other column.

I tried Many of the sample code but i dont find a solution out of it.Appreciate if any one can do it.

sample file has been attached for reference.

Hi,

Sometimes we convert database file to Excel file but the structure is not what we want. See db.xls, B2:D20. I want to rearrange the cell range B2:D20 to the format F2:R6. Put the corresponding numbers or letter (D3:D20) in the range G3:R6. If there is no sale level in certain month, put 0.

Is there a formula that can be put in the cell range G3:R6 to achieve this?

Thanks

hi all,
i have a very simple code for hiding/showing fields. i actually have no problam writing it for every tab, but excel crashes so i guess i overflowed it.

my current code is

	VB:
	
 Range 
     
     
    Set myRng = Me.Range("a15:af15,a16:af16,a17:af17,a18:af18") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
 
 
Private Sub CommandButton11_Click() 
     
     
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a5:af5,a6:af6,a7:af7,a8:af8") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton12_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a10:af10,a11:af11,a12:af12,a13:af13") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton2_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a20:af20,a21:af21,a22:af22,a23:af23") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton3_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a25:af25,a26:af26,a27:af27,a28:af28") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton4_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a30:af30,a31:af31,a32:af32,a33:af33") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton5_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a35:af35,a36:af36,a37:af37,a38:af38") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton6_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a40:af40,a41:af41,a42:af42,a43:af43") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton7_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a45:af45,a46:af46,a47:af47,a48:af48") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton8_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a50:af50,a51:af51,a52:af52,a53:af53") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton9_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a55:af55,a56:af56,a57:af57,a58:af58") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 
 
 
Private Sub CommandButton10_Click() 
    Dim myRng As Range 
     
     
    Set myRng = Me.Range("a60:af60,a61:af61,a62:af62,a63:af63") 
     
     
    myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
i need to use the exact same code for all 400 tabs(sheets)

tried to find an answer but failed to. so if u can help it would be much appreciated.

BR,
Tal

Hi All,

i have some vba code to remove share mode for all excels of folder, but i need code to skip excel file which is password protected. Please help me to solve this.


	VB:
	
path = GetDirectory 
FileName = Dir(path & "*.xls", vbNormal) 
Do Until FileName = "" 
    If FileName  ThisWB Then 
        Set Wkb = Workbooks.Open(FileName:=path & "" & FileName) 
         
        If ActiveWorkbook.MultiUserEditing Then 
            ActiveWorkbook.ExclusiveAccess 
        End If 
         
        Wkb.Save 
        Wkb.Close False 
    End If 
    FileName = Dir() 
Loop 

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


I'm fairly new to vb, and i've been writing small scripts to interface between Excel and SAP r/3 in order to automate batch entry functions. I'm a bit hung up on a need to increment and loop a series of entries that are based upon a session id from within SAP. See current entry below:

PHP Code:
Dim session
Function CallSAPTransaction()
Dim Application
Dim material, plant As String
Dim sbarmessage
If Not IsObject(Application) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set Application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = Application.Children(0)
End If
If Not IsObject(session) Then
   Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject Application, "on"
End If
InputRow = InputBox("Enter the Starting Row", "Starting Row", 4)
Row = CInt(InputRow)
p_Item = Worksheets("BOMUpdate").Cells(Row, 1)
While p_Item  ""
    p_Item = Worksheets("BOMUpdate").Cells(Row, 1)
    p_Pn = Worksheets("BOMUpdate").Cells(Row, 2)
    p_Qty = Worksheets("BOMUpdate").Cells(Row, 3)
    
    session.findById("wnd[0]").resizeWorkingPane 97, 44, False
    session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/txtRC29P-POSNR[0,0]").Text = p_Item
    session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/ctxtRC29P-IDNRK[2,0]").Text = p_Pn
    session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/txtRC29P-MENGE[4,0]").Text = p_Qty
     
    Row = Row + 1
    p_Item = Worksheets("BOMUpdate").Cells(Row, 1)

Wend
session.findById("wnd[0]").sendVKey 0
End Function 
The 3 field names are constant, but their location identification changes. [0,0], [2,0], and [4,0] represent row column and row, row being the initial at 0. So, for the next row, the location values would change to [0,1], [2,1], and [4,1], then [0,2], [2,2], [4,2], and on.

I can figure out how to do a standard loop on the line, but i cant figure out how to loop AND increment. Any ideas would be appreciated.

Hi All,

1st time poster so forgive me if the tags etc are wrong.

we have a workbook, that is designed to specify the correct boiler for our company, what happens once the user form is completed the details are pasted into a sheet named ("Arks") from here at present we have the following code to save from excel and save the sheet. What we would like to do now is to change the file type from an .XLS to a .Doc (Word document) The reason why is because when the workbook is open it disables all keys and hides the complete menu bar and when a user opens a previously saved specification there is no way to close the worksheet without having to close the workbook.

Here is my current code i was hoping someone could provide help with how to change it to make it open, paste and save into a word document instead.
Many thanks in advance.


	VB:
	
 
Sub Saveaccopy() ' This macro will take a specified range and paste it into a new worksheet for you.
    Dim vLinks As Variant 
    Dim lLink As Long 
    Dim NewName As String 
    Dim wbkSource As Workbook, wbkTarget As Workbook, wshSource As Worksheet, wshTarget As Worksheet 
    Dim rngSource As Range, rngTarget As Range 
    Dim MyDate As String 
    Application.DisplayAlerts = False 
    Application.ScreenUpdating = False 
    Set wbkSource = ActiveWorkbook 
    Set wshSource = wbkSource.Worksheets("OrderForm") 
    MyDate = wshSource.Range("C14") 
     'MyDate = Left$(MyDate, 2) & Mid$(MyDate, 4, 2) & Right$(MyDate, 4)
    NewName = wshSource.Range("I14").Value & "-" & MyDate 
    Set rngSource = wshSource.Range("A1:K58") 
    Set wbkTarget = Application.Workbooks.Add(xlWBATWorksheet) 
    wbkTarget.Sheets("Sheet1").Name = "OrderForm" 
    wbkTarget.Names.Add Name:="tempRange", RefersTo:="=OrderForm!$C$14" 
    Set wshTarget = wbkTarget.Worksheets(1) 
    Set rngTarget = wshTarget.Range("A1:K58") 
     'copies the range and its formats
    rngSource.Copy Destination:=wshTarget.Range("A1:K58") 
     'copies the column widths and their formats
    wshSource.Range("A1:K56").Copy 
    wshTarget.Range("A1:K56").PasteSpecial xlPasteColumnWidths 
    ActiveWindow.DisplayGridlines = False 
     
     
     ' Define variable as an Excel link type.
    vLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) 
     
     
     ' Break all links in the active workbook.
    For lLink = LBound(vLinks) To UBound(vLinks) ' Due to the workbook source not being needed for this worksheet, all links
will be broken.
        ActiveWorkbook.BreakLink _ 
        Name:=vLinks(lLink), _ 
        Type:=xlLinkTypeExcelLinks 
    Next lLink 
     
     'ActiveWindow.DisplayRulers = False
    Worksheets("OrderForm").Range("A1").Select 
    ActiveWorkbook.SaveCopyAs ("J:Technical SupportC-ManualsSpecifications") & NewName & ".xls" 
    ActiveWorkbook.Close SaveChanges:=False 
     
     
End Sub 
[SIZE=4][/SIZE] 

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


Hi,

I know my request may seem strange but if a solution is found it will be very useful for people having the same of my situation.
I am gonna make it simple to explain.

I have a workbook that holds guests requests of a hotel, it consists of 31 sheets (a complete month) each sheet/each day will contain in Column A the different guests requests up to 100 requests with thier relevant values in other columns (time, status, durationÖetc)

The last sheet is called Monthly Summary that has formulae applied on all the days.

As you know, the maximum number of requests throughout the month can be 31*100=3100 requests, so there are 3 columns in this Monthly Summary sheet hold formulae that do calculations based on the above-mentioned relevant values in each day, I had to drag down these formulae till the cell 3000 to cover to the maximum number of requests throughout the month but in the other hand this caused the Excel file to have a very large size and causing delay in opening and saving (charts and macro also added as well).

I tried to reduce the filled cells with formulae, and indeed the Excel file size has dropped significantly.

My question, is there any way or a Macro VB that can count the number of requests in each day and based on that drag down or fill the formulae in these 3 column, in this case the file size will get larger based on the number of requests.

It is important to note that in case what I am asking can be done, the formulae are dynamic and changeable according to the cells, we are not filling a constant values.

I hope I could make clear.

I uploaded the file so other people can benefit from it and can also have a clearer look on it for a better help and contribution.

The zip file contains two files, the original and the reduced size one (big difference you can see)

You will notice in the reduced size file that I deleted rows (101 till 3102) in the "Delayed" sheet in the Column A, B, C
These are the column that I want their rows to expand automatically starting from the cell 101 and get filled automatically with the formulae.

The expansion number is based on the total orders in each day; you can benefit from the total orders sum I have put in the end of each day.

It is on this link http://www.mediafire.com/?28ca2n7pvdfb32q

Thanks in advance for your help.

Hi all,

I am opening a PDF document fromm VBA excel. After opening document it should save the document as excel file or text file in one folder.
I am trying to do this with some code but not able to , pls help me thatnks.


	VB:
	
 
Dim AcroApp As Acrobat.CAcroApp 
Dim Part1Document As Acrobat.CAcroPDDoc 
Set Part1Document = CreateObject("AcroExch.PDDoc") 
Part1Document.Open ("Z:EG MI InformationMISRequestsReq_156NO.1.pdf") 
 
 
 
 
Dim app As Object, avdoc As Object, pageview As Object 
Set app = CreateObject("AcroExch.App") 
Set avdoc = app.GetActiveDoc 
app.MenuItemExecute ("SaveAs") 

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

Is there any way we can use excel user form in iPads?
Otherwise, can we use any app to automatically populate an excel spreadsheet when new info is entered?

Thanks a lot
any help will be appreciated :-)

Hi,

I have been trying to write a macro to extract data from multiple TRA files into columns in excel.

The data in the TRA file is 6000 rows text delimited with a comma as per quoted. I want to put the data from each TRA file into two columns with the filename as column title. All side by side in one worksheet.

Originally Posted by TRA File 18.082,1.37259 17.8991,1.39007 17.7279,1.40678 Originally Posted by Excel 18.082 1.37259 17.8991 1.39007 17.7279 1.40678
This is what I have so far, it does extract the data and the filename but puts it into one column and leaves one blank column. I'm sure it's something with the redim array but I can't figure it out.


	VB:
	
 
    Dim txt As String, n As Long, x, a() 
     
     ' set folder path
    myDir = "C:UsersmyuserDesktoptest" 
    fn = Dir(myDir & "*.TRA") 
     
    Do While fn  "" 
        n = n + 2 
        txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(myDir & fn).ReadAll 
        x = Split(txt, ",", , vbTextCompare) 
        Redim a(1 To UBound(x) + 1, 1 To 1) 
        For i = 0 To UBound(x) 
            a(i + 1, 1) = x(i) 
        Next 
        Cells(1, n).Value = CreateObject("Scripting.FileSystemObject").GetBaseName(fn) 
        Cells(2, n).Resize(UBound(a, 1)).Value = a 
        fn = Dir 
         
    Loop 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for any help!

Hi,

I am a very new member of this forum and this is my first post.

I have prepared an excel macro which prepares n numbers of word files from "Data" sheet and saves it with a name driven by a range of cells. Then emails each attachment to a specific recipient (driven by a range of cells) with a subject, body (driven by a range of cells) etc from outlook. But, I am stuck while trying to do the same thing from Lotus Notes 8.5 instead Outlook. Your expert openion highly appretiated.

Any advise? Codes I am using for Outlook are as follows:


	VB:
	
 Test() 
     
    Dim i As Long 
    Sheets("Data").Select 
     'Script should run upto 999 rows of data
     'Since we come across 150 NSW Disclaimer Letters a day 9999 rows should be more than sufficient
    FinalRow = range("A999").End(xlUp).Row 
     
    For i = 2 To FinalRow 
         
        Sheets("Data").Select 
         'To copy data from A2 cell of "Data" Sheet and to paste it to C4
         'Cell of "Template" sheet
        range("A" & i).Copy Destination:=Sheets("Template").range("C4") 
        Sheets("Template").Select 
         'To copy Disclaimer template from "Template" sheet
        range("C32:I71").Copy 
         
         
         'Microsoft Word 8/9/10/11/12 Object Library added from reference library
         'Tools ----> References ----> Microsoft Word 12.0 selected
         'To create a new instance of Word Application
        Dim appWD As Word.Application 
        Set appWD = CreateObject("Word.Application") 
        appWD.Visible = True 
         'To open a pre existing formatted** word file from desktop
         '**For this script go to Home --> Paragraph --> Spacing Before & Aftershould be 0 pt
         '**and "Don't add space between paragraphs of the same style" should be ticked.
         '**select it as Default settings
        appWD.Documents.Open ("C:UserssomnathDesktopDoc1.docx") 
         'To maximize to word file
        Application.WindowState = xlMaximized 
         'To paste Disclaimer template to opened word file
        appWD.Selection.Paste 
         'To save the word file with name derived from "H11" cell of "Template" sheet
        Sheets("Template").Select 
        ThisFile = range("H11").Value 
        appWD.ActiveDocument.SaveAs Filename:=ThisFile 
         'To close active word document
        appWD.ActiveDocument.Close 
         'To close word application
        appWD.Quit 
         
        Dim ToContact As Outlook.Recipient 
        Dim olMailItem As MailItem 
        Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream 
        Dim rngeSend As range, strHTMLBody As String 
         
        On Error Resume Next 
        Set rngeSend = Sheets("Data").range("M1:AE15") '.SpecialCells(xlCellTypeVisible)
         
        If rngeSend Is Nothing Then Exit Sub 'User pressed Cancel
        On Error Goto 0 
         
        ActiveWorkbook.PublishObjects.Add(xlSourceRange, "C:UserssomnathDesktopTemp.htm", rngeSend.Parent.Name,
rngeSend.Address, xlHtmlStatic).Publish True 
         
        Set FSObj = New Scripting.FileSystemObject 
        Set TStream = FSObj.OpenTextFile("C:UserssomnathDesktopTemp.htm", ForReading) 
         
        strHTMLBody = TStream.ReadAll 
         
        Set OLF = GetObject("", _ 
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox) 
        Set olMailItem = OLF.Items.Add 'creates a new e-mail message
        Sheets("Data").Select 
        ThisFile2 = range("L1") 'Recipient
         
        With olMailItem 
            .Subject = ThisFile '"Subject for the new e-mail message" ' message subject
            Set ToContact = .Recipients.Add(ThisFile2) ' add a recipient
             'Set ToContact = .Recipients.Add(ThisFile2) ' add a recipient
             'ToContact.Type = olCC ' set latest recipient as CC
             'Set ToContact = .Recipients.Add(ThisFile2) ' add a recipient
             'ToContact.Type = olBCC ' set latest recipient as BCC
            .HTMLBody = strHTMLBody '"This is the message text"
             ' the message text with a line break
            .Attachments.Add ("C:UserssomnathDocuments" & ThisFile & ".docx") 
            .OriginatorDeliveryReportRequested = False ' delivery confirmation
            .ReadReceiptRequested = False ' read confirmation
            .Save ' saves the message for later editing
            .Send ' sends the e-mail message (puts it in the Outbox)
             
        End With 
        Set ToContact = Nothing 
        Set olMailItem = Nothing 
        Set OLF = Nothing 
        Set FSObj = Nothing 
        Set TStream = Nothing 
        Set rngeSend = Nothing 
         
    Next i 
     
    MsgBox ("NSW Disclaimer Letter Attachment Generation Completed") 
    Application.Visible = True 
     
End Sub 

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


Hi,

I am trying to create a macro that will allow me to easiely open like 10 excel files and then save them and then close them.

I am going to make the excel files all hyperlinks in a seperate spreadsheet and have the macro in that spreadsheet. I am doing this because the files that need to be open will change on a monthly basis, and instead of amending the macro, I figure it would be easier to amend the hyperlink.

I have started out with this basic code that I recorded. Can anyone comment on if they think this will work or if they see any additional code that is needed. I am not the best at vba and usually figure it out by recording my tasks. I recorded this one using 2 hyperlinks, but i will expand it to my 10 hyperlinks later. Also I was wondering if there was a way to put in the code a popup box at the begining that says "are you sure you want to proceed" with a yes and no option. yes would run the macro, no would cancel the macro?, and if there was a way to put a pop up box at the end of the code that says "the process has been completed". Thanks for anyones help


	VB:
	
Range("C4").Select 
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True 
Windows("Book2").Activate 
Range("C7").Select 
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True 
ActiveWorkbook.Save 
ActiveWindow.Close 
Windows("Book1.xls").Activate 
ActiveWorkbook.Save 
ActiveWindow.Close 

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


Hi,

First of all I am using excel 2003 other wise this would be easier, but since you can't refrence another worksheet in 2003 when using conditional formating I was wondering If there was another way to do it.

My problem is I have two sheets, one is a plannned break down for an activity while the other is the actuall breakdown of what happend. The activity is broken down into a month by month basis, with an x percentage of hours for each month. Now what I am trying to do is say you have a total of 500 work hours, and you work 30 hours one month and the plan said it should be 20, is there a way to highlght that cell and also the remaining cells you have left to work in.

Below I attached a example work book witch will make it easier to understand

Thanks in Advance

Hi Everyone
I have searced this forum from top to tale for help, but either this is a first I I have missed something.

I have a workbook which contains a worksheet with part numbers and prices for extruded plastic automotive parts there is very little pattern with the part numbers depending whether they are OEM car manufacturer part numbers or our internal part numbers. At the moment to generate a quote I have to know the exact part number for the piece, what I would liek to do is have a search facility that allows me to type a few characters that i know are in the part nyumber and have excel (VBA) produce a list of matching parts and their associated description from which i can select the required piece.

for instance the database looks like:
column A Column B
NC536-01 8mm Brass Half Cartridge (Fluorocarbon & Nitrile O-Rings)
SL746-E 12mm - 8mm Barb Elbow Connector. (Non SAE). Vent
NY-F11-M8FE-3R/W 8mm - 6mm Barb Straight Connector

So on the quotation page in the workbook if I type in "536" i will get a list of part numbers and descriptions with 536..

At the moment I am using
=IF(ISNA(VLOOKUP(B21,'Master price list'!1:65536,1,FALSE)),"Invalid part number",VLOOKUP(B21,'Master price list'!1:65536,1,FALSE))

But as I said i have to know the exact part number otherwise I get back rubbish..

Any help would be awesomely appreciated!!

kind regards

Nick

Hi

I have a list box form control on excel with multi select property. Its allow to select multipul values with control key in excel 2007 but when we try to select multipul values using the same way it select the list box control instead of value of list box.

We have procted the sheet and Edit Object is already true.

Can somebody help me on this?

Thanks,
Rajiv

I have a VBA script that automatically adds rows into a spreadsheet. Whenever I do that, my vanilla Excel formulas with column ranges stop functioning. Is there a fix to this problem?


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