help with macro required

teamsimpson

New Member
Joined
Sep 20, 2002
Messages
22
I could'nt get the code you wrote to work.Could you look at the code i nearley got to work, my problem is, the macro reads an outside excel file and puts it in an embeded object,it sizes the shape and renames it from new to data1.my problem is, the next time i run the macro, it can't handle the names, i think i need a loop or if else statement, see below
Sheets(1).OLEObjects.Add(Filename:="file.xls", Link:=False).Name = "new"
With Sheets(1).OLEObjects("new")
.Left = 100
.Top = 100
.Width = 50
.Height = 30
ActiveSheet.Shape("new").Name = "data1"
End With
Sheets(1).[a1].Active
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I have a exel, this requires data stored in a worksheet as an embedded object, i have wrote a macro which reads the data from the outside excel, it names it to "new", it resizes it into a small rectangle, positions it on the worksheet, and colours it blue, finally it renames the object to "data 1".
The problem is the next time i run my macro, it fails because there is already an object called "data 1". All together i have to run the macro 5 times to load up 5, which is why i wondered if maybe i should use a loop, or if else statements, either way my excel knoledge is too basic, and would apreciate some help, regards Mike
 
Upvote 0
A loop wrapped around the code you have provided will just insert the same file 5 times, although this may be what you want?.

If not, and if you can put the workbooks in the same folder and rename them (for example), data1, data2, data3 etc, it will be quite simple. If you can't rename them, could you give the names and filepaths of the workbooks you want inserting and we can take it from there.
 
Upvote 0
What i ned to do is on 5 seperate occasions use the code i wrote above, i agree that a loop probably is not what i need. I have an idea but i dont know if it will work, what do you think:-
at the point were i rename the object as data 1, is there a method of renaming the object according to the time of day (ie. the name would be 10:30), that way when the macro is run susequent times the names will not conflict. if this can be done could someone give me a hand with the code please.
 
Upvote 0
Try the following: -<pre>
Public Sub ShapeNames()

Sheets(1).OLEObjects.Add(Filename:="file.xls", Link:=False).Name = "new"
With Sheets(1).OLEObjects("new")
.Left = 100
.Top = 100
.Width = 50
.Height = 30
.Name = "data" & Time
End With
Sheets(1).Activate

End Sub</pre><pre></pre>

Seems to work OK, even though I thought you'd get problems with having colons in the shape name.
This message was edited by NateO on 2002-10-08 22:37
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top