I have been using a macro in Word created from pieces all over the internet (credit to a user named matt198992 for recursion script). The code prompts a user for folder, then runs a macro called Publish as PDF to all word files in the folders/subfolders.
I want to adapt the code in Excel, but I am having trouble. Error on the line "Workbooks.Open Filename:="Path & DirN".
[Code].....
I have a column for "Comments". Is there a way to make each cell work more like a word document? ie. paragraph formation, tabs, etc.
View 2 Replies View RelatedI need to count how many times the word Test is in the range B4:H9 with
Range N2 = Test the formula below works if Test is only in the cell once.
=COUNTIF($B$4:$H$9,"*" & N2 & "*")
But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?
5
Test
8am-2pm
Test
5pm-10pm
I am trying to automate the below process:
1. I have a excel file with Japanese words in column A and their English equivalents in column B.
2. I am trying to create a Macro and assign it to a button. On pressing the button Macro should be able to:
3. open a form where I can enter location of a word file.
4. Macro should open the word file specified in (3).
5. Macro will pick up first Japanese word from excel file (Sheet 1 - A1)
6. Search for that word in Word file
7. Replace the Japanese words in Word file with their English equivalent from excel (B1)
8. Then it should search for second word (A2) and replace with its equivalent (B2) and so on till it reaches last filled cell in excel file column A.
After a lot of search I could find a code from net (Below), made a few changes, but it is not working.
========================================================
Private Sub OK_Click()
' Requires a reference to Microsoft Word xx.x Object Library
Dim sFile As String
[Code]....
I am in the middle of automating a process here at work, the program takes a word, "pencil" for example, from excel. It will then open up a word document with content already in it (premade template). The program will then find all instances of a string, "placepencilhere" for example, and will replace that instance with the string from excel.
Basically I want to be able to take a variable that has a stored string value from excel and use it to replace another variable in a word document.
I tried recording a replace (ctrl+f, replace tab) macro, copying the code, and inserting it into the excel vba code.. but I get a error message. Here is what I have:
---
Although I have seen lots of formulae to total a dynamic column, I found a brilliant formula on the Internet, as follows
HTML Code:
=SUM(OFFSET(INDIRECT("R1C",),,,ROW() -1))
The problem is I cannot work out how to adapt it to total across rows (which will expand with new columns).
Is it possible to display a name on a chart that is not the X or Y value?
I've been sent this macro which displays the school name on a chart when the mouse is on the data point - its all well and good but on the graph I am working on, the X and Y value are both values and I want the school name to come up as well.
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
'macro that gets chart to show user details of a point on the chart when the mouse is moved over it
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX, myY As Variant
With ActiveChart
.GetChartElement x, y, ElementID, Arg1, Arg2
If ElementID = xlSeries Then 'checks to see which part of the chart the mouse is over
The solution to a challenge here does almost exactly what I need except it doesn't manage partial matches.
Function MatchColors(strValue As String, rngList As Range) As String
Dim regEx, Matches, i, strResult, bFlag
'Create and set the parameters for the regular expressions object
Set regEx = CreateObject("vbscript.regexp")
regEx.Global = True
regEx.IgnoreCase = True
I've recorded this Macro to sort the cells in a column alphabetically if any cells have content.
I would like to use the Macro on the whole workbook however the AB column range varies between the worksheets.
How should I adapt this Macro to sort simply to the END of AB column??
' PWRII Macro
'
'
Cells.Select
[Code]....
I'm in the process of combining a lot of worksheets into one and found the code on the following site:
[url]
which almost does what I need. I understand how it works (i think!) but as a vba novice I can't work out how to adapt the code to copy more than one range from the source workbooks.
What I want to be able to do is copy say the following cells from each workbook:
A1
D1
d4:D7
I think what I need to do is call the getdata macro multiple times with different ranges, but I can't quite work out how to do that without simply creating multiple copies of the master file.
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
I have been working on some files in excel for several days, saving regularly, etc. but have run into a huge problem. I was marking cells with a background color to organize and track my work, but the files being saved were .csv's.
From my research, it seems that .csv's only save data, not formatting such as cell background colors.
However, while I was working on the files, they displayed the colors correctly etc.
I am hoping that there is some sort of temporary file created by excel that would allow me to re-open my work and have the coloring appear so I can save the work as an .xlsx file with formatting.
I've been having tremendous issues with my Excel file, mostly with it crashing or not responding. Would this be because my file has so much data in it? If so, is there anyway to make Excel process all the data without crashing so much?
View 6 Replies View RelatedI charge £45 per hour with a couple of conditions - minimum 8hr day and then 1.5T after 12hrs.
Is it possible to have a table where I put in my hours and then Excel tells me how much I should charge?
I have renamed some cells to custom names eg. renamed cell A1 to FLIGHT and B1 to FLIGHT2.
When I apply a filter to the columns and autofilter, the renamed cell now refers to a different cell! ie. FLIGHT no longer has the same text value in it
This also only happens when I autofilter by alphabetically or highest/lowest values
I have this excel file that is 17 sheets long and I made a table of contents page so it is easier for people to navigate. I know my hypertext links in my file are correct because it works when I click on the hypertexted cells, my excel file jumps to the respective sheet.
However, when I save the file as a pdf the hypertext links do not work. how to get my table of contexts to jump pages in a saved pdf file?
The for loop don't work in my excel 2010 macro. Probably the problem might be the Range("Fi") construct with the variable "i"?
Dim i As Integer
For i = 4 To 10004
Cells(i, 63).Select
Cells(i, 63) = Application.VLookup(Sheets(3).Range("Fi"), Sheets(4).Range("F:AY"), 45, False)
Next i
Is it possible to make excel template work only on 1 computer by giving any vba password (on lines of license number).
Aim is to ensure it works on only client computer, even if template is shared outside it becomes useless.(We often see clients audit team getting access to templates using it for thier purpose & distributing).
In same spirit what are best practices one can take like protecting formulas with password etc
i have a set of data in excel.. i want that, when i click a command button on an active worksheet, it will be transferred in ms word all the data found in excel.
View 14 Replies View RelatedI am using Excel 2007 Enterprise edition and want to use speak cells command (Text to speech in 2003).
I have added the command for these in quick access tool bar but when i press any of these buttons, all the buttons of this category disabled.
Using windows 7 Ultimate
I have an issue with the excel freeze in our organisation.
Our organisation works on Citrix and the office applications have been upgraded to 2010 very recently.We have both 2003 excel users and 2010 excel users.There is a user who has a problem working on excel 2010 the excel freezes.Excel 2010 works fine for some time and all at a sudden hangs,once its hung it freezes all the other applications.
The same file runs properly on the other 2010 systems.The Excel file which hangs are also not of huge in size.
I am unable to get to the rootcause of the issue why the excel freezes or hangs in the middle of the work getting to the root cause of the issue and an answer for the excel hanging.
I have created a macro that a couple of us can run at the end of the day that looks in a preset folder and has whatever .xlsx files in that append to each other creating one .txt file. It then saves that .txt file and runs a batch file that strips off the .txt file extension. This is the format we need the ending file to be in so we can FTP it to another agency.
Myself and another coworker can run it without any problem and it asks us if we want to save the .txt file before it closes it and runs the batch file stripping off the .txt extension. We say yes and it executes as designed. Another coworker runs it and it looks like the files are appending then the window closes. Never prompts him if he wants to save and the file is nowhere to be found. From what I have researched all his excel settings match ours. We are all running the same OS (XP) and version of MS Office (2007). The part of the macro that seems to just be ignored is below:
[Code] .......
Then the following batch file runs stripping the .txt file extension:
FOR /R "T:Cash ManagementUnsecured\_Team - DisbursAUTOMATIONInput" %%f IN (*.txt) DO REN "%%f" *.
I have tired removing the ActiveWorkbook.Save command thinking it would default to prompt him to save it but it doesn't.
How to Create a Work Breakdown Structure (WBS) in Excel automatically with given data through macros.
In the attached excel sheet
work_breakdown_structure_.xls
Is there any way to do it for data automatically?
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
View 6 Replies View RelatedI have created a userform template and is working fine, the problem is the users are not able to minimize the template nor open any other excel files.
How to allow the template to open any other excel files or macro enabled files and also to minimize the windows.
Excel 07
I inserted a hyperlink into a cell that goes to a web page. It works when the sheet is NOT protected, as soon as I protect the sheet it stops working. How do can I lock the workbook and that cell so nobody can change it but the hyperlink still work?
i have a macro which i have created to send outlook notification email through excel 2010 when the excel file is saved. I would like to attach the same excel file as an attachment in the outlook email so that everyone in the mailing list can view the attachment. however the macro which i created does not work. i would like to know what is the problem with the macro below. there's an error which says "file path does not exist" when i try running the macro but i have verified my filename is correct & the drive i have saved the excel file is in Z: drive. My file name is 'Tracking File.xls'
'Save Workbook
ActiveWorkbook.Save
'Criteria to send Email
[Code].....
I am having difficulty with a Vlookup in Excel 2003. Basically I have converted both fields to Text using the text function =TEXT(A2,"0") and have tried matching the values but just get an #N/A error and I have also converted both back to number format but still get the #N/A error....when I do a "=" operator function it returns a FALSE value but I cannot see why as both cell are just 4 or 5 digit number.
View 3 Replies View RelatedI am working in Excel 2010 (Windows 8) and am having real problems trying to find a function/formula that will work with both numbers and text. I am trying to identify if the contents of Col A cells and Col C cells on each row are duplicated within a worksheet, and if they are, to add a 'J'.
(Column A is a list of numerical company codes and Column C is a list of adviser codes using the letter A-G. If a company ID code has more than one of the same adviser codes referenced to it, it is termed 'Joint', hence the 'J').
The formula I've used is: =IF(A2:C2, FREQUENCY>1, "J")
But it tells me 'numbers only'.
I have this excel sheet which I want to export to a word document. The following code has been used:
[Code].....
It creates a word document but it only copies and pastes a picture of the excel sheet. I want for the user to edit the word document values that are pasted from the Excel sheet. I know I need to change somewhere in the:
[Code] .....
How can I paste values from excel to word that can be modified inside of word?
ALSO:
How can I change the layout of the word document, for example if I want to add a header and a footer?