Thomas Brunt's OutFront
     Home    Register     Search      Help      Login    

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

 

excel macro help

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> Microsoft FrontPage Help >> excel macro help 		Page: [1]
 
barking mad

 

Posts: 286
Joined: 12/6/2002
From: Out in the sticks, UK
Status: offline

  		excel macro help - 5/8/2003 4:27:06   
I have created a database with lots of products and inherited a file structure which my client doesn' t want to change.

I need to create a file location from the part number given eg

part number " ac-34"
(all part numbers are in this standard 5 digit format)

I need to show

" images/photos/a/ac/34.jpg"

How do I create a macro that would turn my part number into a location ref
Once the formula has generated the location, I then need to save as a line of text as opposed to a formula

I don' t want to do this manually as I have 50,000 items to create!

any help/guidance would be appreciated.

thanks


_____________________________

The more I ask, the more I learn, the more I' m confused, therefore the more I ask ...
john40004

 

Posts: 1346
From: Bardstown KY USA
Status: offline

  		RE: excel macro help - 5/8/2003 20:39:19   
Try a post here: MrExcel Forums

_____________________________

If you lead a good life and say your prayers at night, when you die you' ll find yourself in Kentucky. The home of fast women and beautiful horses!

(in reply to barking mad)
wattle

 

Posts: 254
Joined: 3/31/2003
From: Australia
Status: offline

  		RE: excel macro help - 5/8/2003 20:40:00   
This does sound like quite a chore. The below may be useful, or be a start anyway. I hope I have not misinterpreted your question. Have assumed that the part no' s are in columns.

Split the part number into three columns (Data > Text to Columns > Fixed Width > Create two line breaks before and after the " -" ). Send the split data, ie: the three new columns, to the far right of your spreadsheet. You can always move it later.

Create a column near the split part numbers. It the top cell type " images/photos/a" or whatever the path is. Create another Column to contain " jpg"

You will now have five new columns (the column containing the " -" from the part numbers could probably be deleted).

To the right of this create another column to combine your new columns (will look something like the below). Assuming cell A1 is the part no letters, B1 is the numbers, C1 is the image location, D1 is the picture format, the formula for cell E1 would be =C1&" /" &A1&" /" &B1&" ." &D1

You can copy the formula down. When you want to alter it back to text go to Copy > Paste Special > Values

All the best with this.

Wattle

:)

Attachment (1)

(in reply to barking mad)
wattle

 

Posts: 254
Joined: 3/31/2003
From: Australia
Status: offline

  		RE: excel macro help - 5/8/2003 20:41:04   
John, are you sure you have a slow connection? :)

Wattle

(in reply to barking mad)
wattle

 

Posts: 254
Joined: 3/31/2003
From: Australia
Status: offline

  		RE: excel macro help - 5/9/2003 0:52:46   
Hello again, I should have added this to the above. If you want the location to be a hyperlink the following formula can be used in place of the one I posted above. If this is used ' Paste Special' ' Values' will give you the hyperlink text without actually being a hyperlink. If left as is it will be a hyperlink but contain the formula. :) That is probably clear as mud.... 

=HYPERLINK(" http://www.webaddress/" &C1&" /" &A1&" /" &B1&" ." &D1," http://www.webaddress/" &C1&" /" &A1&" /" &B1&" ." &D1)


If you do use ' Paste Special' with something like this and want to convert it to a hyperlink later the following macro will work: 

Sub MakeHyperLink()
  ActiveCell.Hyperlinks.Add ActiveCell, ActiveCell.Value
End Sub


Wattle

< Message edited by wattle -- 5/9/2003 1:20 AM >

(in reply to barking mad)
Page:   [1]

OutFront Discoveries


All Forums >> Web Development >> Microsoft FrontPage Help >> excel macro help 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Copyright © 2009 Thomas Brunt's OutFront Inc. All Rights Reserved.