Excel Macro To Insert Formula For Variable File First Sheet
I built a macro that loops through all the file names in a folder.
I wanted to then check in cell c7 of each file to see what the content was with out opening the file. I was unable to accomplish this, so I went to plan 2. I want to place a formula on a spread sheet that will bring in the number from cell c7 on the first worksheet. The reason I went this rout is time. When you open and close files the macro takes too much time to run. My formula below works as long as the first sheet in each workbook is sheet 1 the problem is most are not they vary in name. How can I replace sheet1 in my code to pull any 1st worksheet name?
Formula1 = "='H:Mo[" & MyFile & "]Sheet1'!C7"
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Excel VBA Macro With Variable Used In SEARCH Formula
I have two columns of names. Column A has full names (first and last; example: Mary Thomas); Column E has partial names (Mary or mas). There are many rows of text in both columns. If the name in column A contains the partial name shown in column E, I want to copy the code from column F into column B (the corresponding row numbers probably differ). If I type the following formulas into cells, and copy them into the other rows, I get the results I want for the first partial name:
Cell B1: =SEARCH(E$1,A1)
Cell C1: = IF(B1>0,F$1,"")
Since I have multiple rows in column E that I want to use to compare, I am trying to write a macro. I've tried the following code, which does not work (iRowPartCount and iRowFullCount have been previously defined):
Code:
For iRowPart = 1 To iRowPartCount
For iRowFull = 1 To iRowFullCount
If Cells(iRowPart, 2).Value < 1 Then
Range("B" & iRowFull).Select
ActiveCell.Formula = "=SEARCH(E$" & iRowPart & "$, _
A$" & iRowFull & ")"
End If
If Cells(iRowFull, 2).Value > 0 Then
Cells(iRowFull, 3).Value = Cells(iRowPart, 6)
End If
Next iRowFull
Next iRowPart
Can anyone suggest something I could try? I do appreciate the help.
Simple Copy Sheet With No Formula Macro
Trying to copy a worksheet to another workbook (got that far)
However, I only want the values and format of the original worksheet to be copied accross. No formulas.
Is this doable - I have searched for the last 2 hours on it and can not find any reference.
Need VB Code To Insert A Row For A Subscripted Line In Excel & Code To Search For Amounts On One Sheet And Apply Them To Certain Area On Another Sheet
I am in the process of developing an Excel spreadsheet and need assistance
in adding visual basic code to automate it.
The first thing I am trying to do is to create a macro where a user can
click on a certain button, and the program will insert a row at a specific
row and will copy all formulas associated with the row above it. For
instance, if you have a spreadsheet that has 10 rows and 3 columns in it,
you can insert a subscript line below row 5 and call it 5.1. When the
subscripted row "5.1" is added, it will include the same formulas as in row
5, which would add columns 1 and 2 in column 3.
The second thing I am trying to do is have some code that if a user enters
information on Sheet B that needs to also be included on Sheet A, the code
will search Sheet B to see it an entry made on Sheet B should be carried to
a particular row in Sheet A.
I would greatly appreciate any feedback anyone can give me regarding this.
Thanks in advance.
VBA Macro / Excel Formula
I need to create a macro to run a monthly task. I am pulling data from one and input into the other. One workbook we will call my constant/template and the other my variable/data will contain my data.
My variable/data workbook will consist of the following example data...
Column B Column F
Row 1 203456 123,456
Row 2 213456 45,123
Row 3 223456 23,456
Row 4 233456 123,000
My constant / template workbook will contain description fields. for example
Column A Column B Column C
111 Account A Sum (See Explanation Below...)
112 Account B
113 Account C
In column c of my constant I need to
1) define the range by input (203456,213456) because these numbers may change or need to be added.
2) sum of the numbers in column F of my data/variable sheet that are defined by the range specified above. (203456,213456)
3) so the value entered into my constant / template should be 168579 which is the sum of row 1 and 2 becuase I have defined those numbers to be the specified range to sum.
So here is my best attempt to explain in english if someone can convert this to an excel formula and / or VBA code...
If Column B contains the numbers 203456,213456 then enter the sum of the numbers for those rows under Column F into my constant/data sheet.
Many thanks in advance for any assistance in completing this task.
Insert Formula In Excel With VBA
I want to use VBA to insert a formula in a cell. It depends on the value of another cell wether or not to formula should be inserted.
The formula works just fine when i manually insert it in the cell., but i always get an error when i try it with VBA.
My code is something like this:
Do while cells(2,i).value <> ""
cells(1,i).value = "=VERSCHUIVING($A$1;VERGELIJKEN((D3);$A$2:$A$1000; 0);1)"
i=i+1
if i>100
then exit do
else
end if
loop
When i use a simple formula like =sum() then i get no error.
It's just with more complex formulas that i receive this error.
Any help is welcome
Reading The Contents(formula) Of An Excel Cell And Writing It To A Second Sheet
hello
is it possible to read the contents of an excel cell(the contents being a formula) e.g. reading the cell A30 which has the value
value a30 =c30*e30
and then storing it to a variable say cellstore in VB and then writing a contents of cellstore (which should be =c30*e30) to the second sheet in the workbook
is it possible to use say cell.formula if there is such a thing?
Excel Formula To Work In A Macro
I need help rewriting this to work in a macro:
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-
LEN(SUBSTITUTE(A2," ","")))))
Its is a formula I use in excel to return a surname. I want the macro to do it instead. any ideas?
How To Execute A Macro In An Excel Formula
I am trying to execute a macro via an excel formula. I have recorded a macro to insert a page break (name = page, hot key ctrlB). I would like it have a formula which would look like " If(a2=a1,"",run macro ctrlB). any help would be greatly appreciated. Thanks in advance
Mike
Help In Copying Formula In Excel Macro
Hey guys and guru's
I need help!
I need a macro to copy a formula if it met the following condition:
if its C8:C300 is equal to 1/15/2004 and if its D8300 is equal to "AVE" then it must copy/paste this formula =IF(D9>$D$4,D9-$D$4,0). If it copies the formula does it also automatically adjust like when you copy and paste it manually like the D9 will automatically adjust to D10?
This is making me crazy.. I hope somebody help me.
Conditional Formula In A Excel Macro
Hy Everybody
I am trying to add two columns in a macro, but some cells contains text and I receive an error message, what I need to do is create the formula in the result column, and when any of the two cells involved in the result are Text, put a zero on the result column. I tried this, but did not work.
If ActiveCell.SpecialCells(xlCellTypeConstants, xlTextValues) Then
ActiveCell.Offset(0, 1) = "0"
Else: ActiveCell.Offset(0, 1) = "=RC[-3]+RC[-1]"
End If
I will appreciate your help.
Excel Macro And Formula Replacement
I am having a problem with creating an Excel spreadsheet that uses DDE to put information from another program into specific cells in Excel.
I am creating buttons and assigning the macro to it, so when the button is clicked, the macro is executed.
The macro is along the lines of this:
Sub GetMyItem()
Sheets("Sheet1").Cells(27,7).Formula = "=MyProgram|MyTopic!'MyItem'"
End Sub
This works fine, in that I will execute the macro and the desired item information will go directly into cell 27,7. But the item in MyProgram will change, and I want to put each item in the SAME Excel spreadsheet. So then I create other macros with the cells and the name different, but the rest the same since it is the same Item:
Sub GetMyItem2()
Sheets("Sheet1").Cells(29,7).Formula = "=MyProgram|MyTopic!'MyItem'"
End Sub
and on and on...
But since this basically puts a formula in the cell, which is updated any time I press the button, ALL of the Items are updated to the Current Item in MyProgram. That is, if The item in MyProgram was 12, and I call GetMyItem, it puts 12 in cell 27,7. But then the item changes to 23, and when I call GetMyItem2, BOTH cell 27,7 AND 29,7 will change to 23. I believe this is normal for Excel, but not what I want.
I think what I need is for each macro to retrieve the value from MyProgram|MyTopic!'MyItem' and store it in a variable X. Then put X in the prescribed cell. How do I do this??
Urgent help is needed.
Insert Excel Fn/formula Containing Quotes
Hello all, vb6,vba
I am trying to parse the data in an excell cell using the VBinside excel . I have tried using the help files, the FAQ's from several online forums and a bunch of experimentation, all to no avail.
The problem is: cannot put the find function into the cell such that it operates correctly.
Example: I try to parse: "PACKAGE" 0805
to look like: "PACKAGE" in one cell and 0805 in another.
I can type the fn in the cell and have it work, but it doesn't work when I try inserting it through VB.
Any Ideas?
Thanks in advance.
Insert Formula In Excel.Application
I'v an error 1004 when I try to insert an IF formula in a cells().value.
Other string, as simple addiction (es. "=A1+A3") not gives my any problem.
If i try cells([...]).value = "= IF(1 = 0; 0; 1)" i've an error 1004...
There's another way to insert formula in excel.application.worksheet instead of the simple way .value??
Thx
Weo
Word Macro Insert File/ Append Table Macro Issue.
Hello,
I was wondering if someone could help me with this problem...
When I use the following code to insert a table (from an external file), the table that is inserted does not append to the table in first document (which is what I want it to do)...
Sub Macro1()
Selection.Collapse Direction:=wdCollapseEnd
Selection.InsertFile FileName:="m:cat.DOC", Link:=True
End Sub
Would anyone know how I can make the contents of this file append to the table in the first document?
Thanks so much in advance,
Anne
Variable Row Definition In An Excel Formula
Hey ya'll,
I have data that is being put into an excel sheet but the data is variable based on the inputs. I'd like to add and AVERAGE row at the end of the rows. I think I have the end-row reference figured out (count the rows and reference that count number) but I need to figure out the formula for the average calculation. Can you help me out?
Code:
Sheets("Product Reporting").Range("Z1").FormulaR1C1 = "=COUNT(R[25]C[-23]:R[10000]C[-23])"
Sheets("Product Reporting").Cells(i, 2).Value = "AVG"
i = 26 + Range("Z1").Value
k = Range("Z1").Value - i
For j = 3 To 21
Sheets("Product Reporting").Cells(i, j).FormulaR1C1 = "=ROUND(AVERAGE(R['&k&']C['&j&']:R[-'&i&']C['&j&'] ),2)"
Next j
VB6.0: Find Excel Sheet With Macro
I am trying to build a small Visual Basic application (VB6.0) that will loop through a folder and pick out all the Excel files.
My question is--How can I programmatically tell if a particular Excel file contain Macro?
Please help me out.
Thanks in advance.
Open Another Excel Sheet In A Macro
Is it possible to open another excel sheet in a macro?
I want to copy data from one excel sheet to another
I tried this in abc.xls but it errors out
Code:
Private Sub comp()
Dim xlTmp As Excel.Application
Set xlTmp = New Excel.Application
xlTmp.Workbooks.Open "xyz.xls"
End Sub
Please help
Insert Column In Excel Sheet (VBA)
I know how to insert a row in an excel sheet via vba as shown below:
Code:
Sub InsertColumn()
Worksheets("brutto").Rows(1).Insert
End Sub
But what is the equivalent of inserting a column ? I tried :
Code:
Worksheets("brutto").cols(1).Insert
but that ain't working. Anyone ?
A VB turned PHP geek
Insert Object On Excel Sheet
Actually, it is a VBA question. When I tried to create a control (such as, commonDialog) on Excel sheet and I got “ Cannot insert object" error.
Can you help?
Thanks in advance.
Jian
Excel Formula Using A Variable Workbook Location
Hi I’ve been trying to find an answer to this problem and was hoping someone here could help.
I have a spreadsheet which requires certain data from a 2nd workbook. The 2nd workbook changes everyday sometimes on hourly bases.
Currently I have to locate the 2nd workbook open it then use a vlookup in the 1st workbook to obtain the data. The first workbook never changes and the formula used never changes (the same sheet and column from workbook 2 is used every time, the only thing to change is the location of workbook 2)
The formula used is =vlookup(a1, (unknown location)sheet1!C:C, 1, false)
I’m looking to write a macro which prompts the user to open workbook 2, the location of the file is then used to update the formula and perform the calculation automatically.
I maybe tackling this incorrectly, however I know how to have the user open the 2nd workbook and have its location stored in a variable (wb_loc) however updating the formula with the workbook location is a stumbling block.
Any suggestions?
Thanks.
VBA: Excel Macro: Copy Formula Down In Sheet2 Until Cell In Sheet1 Is Empty
I am trying to modify a soon to be recorded macro in Excel. I receive a file every month with X number of rows. I need to copy a formula that references a cell in Sheet1 down Sheet2 until there are no more rows to copy in Sheet1 (until the row to be copied is empty).
I found Do ... Loop Until, but I can only get it to work by testing a cell on the same sheet b/c the example I found uses ActiveCell.
Code:
Do
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
How can I test the cell on another sheet that's in the same row as ActiveCell?
I'm not asking for a handout, but they're always appreciated
Vb Macro Needed To Split Excel Sheet
--------------------------------------------------------------------------------
Hi All,
I Need A Vb Macro Script To Make Multiple Copies Of Microsoft Excel Sheet After 65 Rows Are Exceeded. Since Am Using Excel It Limits Onl;y 65k Rows.
Can U Please Help Me With Code.
Vb Macro To Break Excel Sheet After It Exceeds 65k Rows
Million Thanks In Advance
Edit by Moderator:
Please post Excel questions, in the Excel forum.
Thank you.
Excel Sheet With Macro Type Mismatch
Dear All,
I open a sheet which actually has macro and also filename.xls and filename.xla
Now when i say
Msgbox excel.Application.Version 'it does it fine
Msgbox excel.activesheet.name 'it works fine
But when i say
Set Oworksheet = excel.activesheet ' it gives me typemismatch error #13
also the same error for the following
For Each sht In excel.ActiveWorkbook.Sheets ' gives error on this line
MsgBox sht.Name
Next sht
Any inputs?
Thanks a lot
snehal
Macro To Retrieve Set Of Data From Excel Sheet
I had a requirement :
There are some column values in excel sheet containing some date columns and other data running .But the requirement is to retrieve the whole set of column values or data which lies within the latest week,ie,if today is 26th july then i need to get all the data from 19th july to 26th july.The present date must be taken from the system date. And there is one more constraint ,the date column values are in the form of 'dd month-name yyyy' eg:'15 march 2006'.Am in urgent need of the script, as am newbie to vb
Here is the example:
Theres a sheet with values in columns A,B,C where C col contains date values like '15 march 2006','06 august 2007' and so on.I need a macro to retrieve only week days data from present day.With respect to the example mentioned above the data that i require is the A,B,C column values for '06 august 2007' and any other lying within the week day values.I was just stuck up in creating the macro as am new to vb. I think this suffices you.Please help me in forming the macro ...its really very urgent .
The main prob where i was stuck is:
getting the latest week's data , ie, system date-7 days
and also the conversion of date format from the source.the complete script is more helpful.
Excel Macro Copy/paste To New Sheet, Next Row
A little background, I've got an excel spreadsheet that gets updated everyday. If there are no problems at the facility, there is nothing to paste to the other worksheet. I just need to record problems at the facility on to another worksheet. I could do it by date, but I would have big blank areas on the sheet because we only have problems a couple times a month. This spreadsheet captures a years worth of operational data.
here's what I've got so far, it may not be pretty but it works (except for the part in bold that I need to paste to the next row)
Quote:
Msg = "Comments are necessary if there was Planned or Unplanned Downtime" ' Define message.
Style = vbOKCancel + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes' button.
MyString = "Yes" ' Perform some action.
Sheets("Time").Select
Range("l21:z21").Select
Selection.Copy
Range("l22").Select
Selection.Offset(Range("k21"), 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
Sheets("Sheet2").Select
Range("I13:K17").Select
If Range("J37") > "0" Then
Msg = "Do you wish to submit these downtime hours?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Response = MsgBox(Msg, Style, Title)
Else: End If
If Response = vbYes Then ' User chose Yes' button.
MyString = "Yes" ' Perform some action.
Sheets("Sheet2").Select
Range("M13").Select
If Range("M13") = "" Then
Msg = "You must explain the reason for the downtime the Comments area!" ' Define message.
Style = vbOKCancel + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Response = MsgBox(Msg, Style, Title)
Else: Sheets("Sheet2").Select
Range("M13").Select
Selection.Copy
Sheets("Results").Select
Range("B40").Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sheet2").Select
Range("L2").Select
Selection.Copy
Sheets("Results").Select
Range("A40").Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, SkipBlanks:= _
False, Transpose:=False
Sheets("Sheet2").Select
If Response = vbOK Then ' User chose OK' button.
MyString = "Yes" ' Perform some action.
Sheets("Sheet2").Select
I need to start @ B40 and each time there is something in M13 (and sum J37 > 0) it needs to past to B41, then the next time B42.
I hope this makes sense!
thanks!
...Brady
Excel VB To Insert VB Code In Sheet At Runtime
So having created a fancy pivot table I want to stop any user from changing the width of a key column by reseting the width to a fixed size - anytime they change the pivot table selction or page fields of the pivot
So far so good - using this one liner.......
Sub Worksheet_Change(ByVal Target As Range)
Columns("H:H").ColumnWidth = 25
End Sub
But problem is that I need to attach or insert this code durig runtime of another parent VB macro.....as this parent processes a load of data and then dynamically creates the new worksheet/workbook and saves it standalone.
How can I get this VB code into the new worksheet so that whenever a user views the pivottable worksheet = the columnwidth stays the same.
can VB code be added / inserted into a child process/worksheet during run time ?
Insert Into A New Excel Sheet Data From A VB6 Form
Hi,
I am trying to put the data from a table into an Excel spreadsheet. People I work with said that they have done something like the following, but are not quite sure of the systex. THey said this should create the spreadsheet and insert the data. However, no matteres how I do this it comes back with either "incomplete query cluase" Or " Syntex error. I think I can do this with loops, but if I can get this statement to work it would be better.
Here is the code I have:
SQLStr = "SELECT * INTO '[Excel 8.0; Database = G:APPOMerlsS2.XLS].[Sheet1]' FROM tblFinalOut"
CN.Execute SQLStr
Any ideas would be greatly appreciated.
Thank You
Edited by - itmasterw on 2/18/2005 10:08:02 AM
Excel: Writing From Macro Form To Text Box On Sheet
There are two boxes that seem like they would work for me. I just need to automatically fill in a text box from a form. There is a label command available on the Forms toolbar and a Text Box command on the Drawing toolbar. From my limited experience with VB, it seems like I somehow need a label for these boxes. I need to have a label so I can send a value to the right place. Can someone help me out? I couldn't find any help by searching this forum.
There's also an Edit Box command that's not enabled on my toolbar.
Help With Macro To Copy Excel Sheet W/o Blank Lines
Ok, I'm working with Excel and tryiny to creat a macro to copy all data from one sheet, but skip blank spaces between them. I'm trying this formula, but its not working... any suggestions?
Private Sub CommandButton1_Click()
With Sheets("Sheet10")
.Cells.ClearContents
.Cells(1, 1).Value = "LocationID"
.Cells(1, 2).Value = "Provider"
.Cells(1, 3).Value = "Amount"
.Cells(1, 4).Value = "Pin"
.Cells(1, 5).Value = "Radiant CardID"
.Cells(1, 6).Value = "BatchID"
.Cells(1, 7).Value = "Date Imported"
.Cells(1, 8).Value = "Date Sold"
.Cells(1, 9).Value = "Comments"
.Cells(1, 10).Value = "When used"
.Cells(1, 11).Value = "Location Percentage"
.Cells(1, 12).Value = "Credit Amount"
.Cells(1, 13).Value = "CardID"
.Cells(1, 14).Value = "LocationID"
.Cells(1, 15).Value = "POSCardTypeID"
.Cells(1, 16).Value = "ProviderID"
.Cells(1, 17).Value = "RegionID"
.Cells(1, 18).Value = "Pin"
.Cells(1, 19).Value = "Denomination"
.Cells(1, 20).Value = "DateAdded"
For Each c In Sheets("New pins convert").Columns("A:A").SpecialCells(xlCellTypeConstants, 3)
If NotFound(c.Value, "New pins convert") Then
NxRw = .Cells(65536, 1).End(xlUp).Row + 1
.Cells(NxRw, 1).Value = c.Value
.Cells(NxRw, 2).Value = c.Value
.Cells(NxRw, 3).Value = c.Value
.Cells(NxRw, 4).Value = c.Value
.Cells(NxRw, 5).Value = c.Value
.Cells(NxRw, 6).Value = c.Value
.Cells(NxRw, 7).Value = c.Value
.Cells(NxRw, 8).Value = c.Value
.Cells(NxRw, 9).Value = c.Value
.Cells(NxRw, 10).Value = c.Value
.Cells(NxRw, 11).Value = c.Value
.Cells(NxRw, 12).Value = c.Value
.Cells(NxRw, 13).Value = c.Value
.Cells(NxRw, 14).Value = c.Value
.Cells(NxRw, 15).Value = c.Value
.Cells(NxRw, 16).Value = c.Value
.Cells(NxRw, 17).Value = c.Value
.Cells(NxRw, 18).Value = c.Value
.Cells(NxRw, 19).Value = c.Value
.Cells(NxRw, 20).Value = c.Value
End If
Next c
End With
End Sub
Public Function NotFound(CellValue, SheetToSearch) As Boolean
With Worksheets(SheetToSearch).Range("A:A")
Set c = .Find(CellValue, LookIn:=xlValues)
If Not c Is Nothing Then
NotFound = False
Else
NotFound = True
End If
End With
End Function
Insert A Selected EXCEL Sheet Into An Active Word
Hello!
I would like to insert a selected EXCEL sheet into an active Word document (trough a Macro started in Excel). The insertion point is already selected, but I dont know how can I give orders (paste, insert) to word from my Excel code.
Can anyone help?
Thanks in advance.
Jejo
Array Variable In Excel Sheet
Hey guys,
i have the following code but it doesnt seem 2 work properly: -
Code:
WalArray(3).LecturerSheet = 1
With Workbooks(1).Sheets(WalArray(3).LecturerSheet)
.Cells(WalArray(3).Wal_Row + PlusThisRow, 9).Value = .Cells(WalArray(3).Wal_Row + PlusThisRow, 9).Value + 1
End With
The WalArray(3).LecturerSheet variable doesnt seem to be able to be read..Pls advise..Thanks!
Adrian
Macro W/ Embedded Excel Sheet: Error Script Out Of Range
Due to my worksheet's columns being aligned to where I don't have enough space to code in some new information I had to do the following:
MS Word: InsertObjectMS Excel WorksheetCopy it over to Excel and use Past SpecialExcel Worksheet Object
Next, after inputting the =sumif formulas, I created a Forms Button to have it execute the vb script. What needs to happen is for the formulas in the embedded excel sheet to update I have to open & close the embedded sheet. What I want is for the button to run that procedure automatically. When I execute that script it gives me the error "subscript out of range". The script is the following and in debug mode it highlights the 2nd row:
ActiveSheet.Shapes("Object 14").Select
Windows("Worksheet in Service Count Sheet").Visible = True
Selection.Verb Verb:=xlPrimary
ActiveWindow.Close
What can I do to resolve this? I have included a copy of the test sheet.
Thanks.
Troy
Insert Macro In Excel From Vb
Hi all
Can anyone give some ideas or tutorials on how to insert a macro into excell from vb without making the excel app visible.
I want to build a report with excell using data from vb. I want the macro to format the excel worksheet to look like the report i want to print.
Any ideas or links to tutorial would be grteatly appreciated.
Thanks in advance
Dirk
Insert An Excel Macro From VB
Hi all !!
I´m creating a new xls file. I need to insert a macro on it from vb before saving and closing the file.
Here´s how I create the object:
Code:
Dim X as Object
Set X = CreateObject("Excel.Sheet")
HELP !!!
Any ideas???
Insert A Selected EXCEL Sheet Into An Active Word Document
Hello!
I would like to insert a selected EXCEL sheet into an active Word document (trough a Macro started in Excel). The insertion point is already selected, but I dont know how can I give orders (paste, insert) to word from my Excel code.
Can anyone help?
Thanks in advance.
Sercrusa
Insert A Macro From Vb6 Into Word Or Excel
hi there,
i would like to know if i can insert a piece of vba code ,which is in my vb6 app, into Word and Excel into a new marco or something and if that is possible, how i've got to do that.
Arno
Writing A Macro In VB To Insert A Chart In Excel
I am working on an excel spreadsheet (attached) and am having a problem with the "Insert Chart" Macro I am trying to create. I'm not really too sure how to approach this but if you look at the code I've written so far you may be able to tell me if I'm on the right track or not. The other macros in the sheet all work OK. When I run "insert new mechanic" and add data I need the chart to reflect this. Also I don't need the data in the second column ("B" ) to be included in the chart. As you can see I've fiddled around with the coding a fair bit but as yet have not come up with a solution. I would appreciate any help. Please see attachment.
Excel Macro: Resize A Picture And Insert Into Spreadsheet
Hi, I'm new to excel macro programming and would like a macros that can iterate through a directory with large jpegs such as:
c:photos
and create a thumbnail of each large image and save it to
c: humbs
The most important is the resized thumbnail MUST BE IN PROPORTION with the origianl image, so "keep aspect ratio".
Any help would be appreciated, thank you!!
Run Excel Macro From Word Pass Variable
As this is my first post i would just like to say this forum is wonderfull i have been able to find so much help on it. Usually i can find the answeres i seek with out posting just by searching a bit but this time it seems nothing came up or more likely i missed it.
what i'm trying to do is call a macro passing two arguments to excel from word and pass a result variable back from execel.
when excel opens it loads a macro. This all has to happen behind the sceens (why it is not visible).
word:
Sub cmdGetNumber()
Dim XL As Object
On Error GoTo OLE_ERROR
Set XL = CreateObject("Excel.Application")
'Open Excel document
XL.Workbooks.Open "C:Documents and SettingsSCummingsDesktopECR_NUMBERS.xls"
XL.Visible = False
XL.Workbooks(1).Activate
**************************
' call fuction here... something like:
ActiveDocument.FormFields("Text1").Result = cmdReturnNumber(strOriginator, strDate)
**************************
'XL.Workbooks(1).Save
XL.Application.Quit
Set XL = Nothing
'Unload Profile Form
Unload Me
OLE_ERROR:
MsgBox Error$(Err)
If Not (XL Is Nothing) Then
Set XL = Nothing
End If
Unload Me
End Sub
excel:
Function cmdReturnNumber(strName, strDate)
cmdNewNumber_Click
txtPerson.Text = strName
txtRequestDate.Text = strDate
txtRequestedBy.Text = strName
cmdReturnNumber = txtNumber.Text
cmdSubmit_Click
End Function
any help would be very much appriciated, and if you need me to clarifiy something let me know. i'm not sure if i explained myself well enough.
thank you,
shane
Visual Basic Macro - Create Email Insert Data From Excel
Hi,
I am looking for a solution to do the following (and have searched and searched and have not found an answer.)
I need to have a macro that generates an outlook email from data listed in an excel spreadsheet. Not only will this macro need to read data from a column containing email addresses (a list of recipients), it will need to insert excel data listed in different workbooks of a single spreadsheet. The data that needs to be inserted will vary from receipient to recipient - i.e. will not be the same length or content for each email.
I need to know if this will be possible - I do have a simple, generic macro listed below that will create a form letter - but I am not certain if this can be changed to allow a. different excel content and b. email support. Any help would be very much appreciated.
Thanks!
Sub Do_It_All_Form_Letter()
Dim word As Object
Set word = CreateObject("word.basic") 'creates the word object
With word
.AppShow ' Makes Word Visible
'Sends keystrokes to the Open Worksheet dialog box
SendKeys "{TAB}{TAB}{TAB}{ENTER}"
'Opens the Microsoft Excel file
.FileOpen Name:="c: estdata.xls"
'Saves the file as a Microsoft Word document
.FileSaveAs Name:="c: estdata.doc", Format:=0
.FileClose 'Closes the file
.FileNewDefault ' Opens up blank Word document
'Makes the active window a main document
.MailMergeMainDocumentType 0
'Open the data source
.MailMergeOpenDataSource Name:="c: estdata.doc", _
LinkToSource:=0
'Activates the mail merge main document
.MailMergeEditMainDocument
'The following Insert commands place text into the Word
'document. You could change these commands to place any text in
'the document. This example uses a typical business letter
'format.
.Insert "3454 Blindside St." 'Inserts a string
.InsertPara 'Inserts a carriage return
.Insert "Columbia, GA 23287"
.InsertPara
.InsertPara
'the mergefields are the same as the column headings in the Excel
'workbook
.InsertMergeField MergeField:="NAME"
.InsertPara
.InsertMergeField MergeField:="ADDRESS"
.InsertPara
.InsertMergeField MergeField:="CITY"
.Insert ", "
.InsertMergeField MergeField:="STATE"
.Insert " "
.InsertMergeField MergeField:="ZIP_CODE"
.InsertPara
.InsertPara
.InsertPara
.Insert "Dear "
.InsertMergeField MergeField:="NAME"
.Insert ","
.InsertPara
.InsertPara
.Insert "Thank You For Your Support."
.InsertPara
.InsertPara
.InsertPara
.Insert "Sincerely,"
.InsertPara
.InsertPara
.InsertPara
.Insert "John M. Doe"
.MailMergeToDoc 'Merges data records with the main document
' Saves the active document with the specified name
.FileSaveAs Name:="c:letters.doc"
End With
Set word = Nothing 'clears the object variable
End Sub
Insert HTML File In Word Via Macro/VBA
I have thousands of html files I need to insert into Word documents. I can insert only a few files at a time using the Word > Insert menu. Is there a way, by macro or some other code, to insert these html files into Word?
Thank you in advance for any replies.
Word Macro Insert File Method Question
Hello,
I was wondering if someone could help me with this problem...
When I use the following code to insert a table (from an external file), the table that is inserted does not append to the table in first document (which is what I want it to do)...
Sub Macro1()
Selection.Collapse Direction:=wdCollapseEnd
Selection.InsertFile FileName:="m:cat.DOC", Link:=True
End Sub
Would anyone know how I can make the contents of this file append to the table in the first document?
Thanks so much in advance,
Anne
|
TRACKER
