Can data/column be used as the reference as opposed to cell number (1 Viewer)

[Mike375]

I am trying do something in Excel that I do in Access and because of some people not having MS Office Professional and hence no Access.

Let's say we have 100 rows and each row has a person's name etc and one of the cells has the name of a Word.doc file that has a date/time stamp such as "2008-12-03_14-48-24 Wilson Letter 1". Is possible to place and OnClick event that will apply to all rows and use the data in the cell for reference as opposed to the full cell name.

At the moment I know how to make the event run if a cell is clicked on but I can only do it for an invidual cell and the way I am doing it would be extremely clumsy if there were 100s of rows. I use the code below. Apart from the clumsy nature of the code what would happen if the rows were sorted differently. Hence, is there a way to reference only the column and the row is reference by the data on the row.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Target

If .Count > 1 Then Exit Sub
If .Address(False, False) = "A1" Then
Call Macro4
End If
If .Address(False, False) = "A2" Then
Call Macro4
End If
If .Address(False, False) = "A3" Then
Call Macro4
End If
End With
End Sub

The above has obvious problems if 100s of rows are involved.

I also have a similar problem with the code that saves the file name in that I have to reference the full cell number as in

Range("G33") = ("Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss")) which puts

Viper 2008-12-07 00-28-43 in G33. Doing this in Access would only involve the field (column)as the reference as the row would be automatically determine because I would be clicking on a textbox or buttom that was part of each row. I am trying to dupicate this Excel.

 

[Bilbo_Baggins_Esq]

I'm having trouble feeling confident, following your description, that I understand what you want.

But this may be what you want, to gather the reference of a cell within an array which matches a certain value.


If this is not what you seek, it may be best to post a sample of what your data looks like so we can see what you see.

MATCH

Returns the relative position of an item in an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.

Syntax

MATCH(lookup_value,lookup_array,match_type)

Lookup_value is the value you use to find the value you want in a table.

* Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.

* Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

* If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

* If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

* If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

* If match_type is omitted, it is assumed to be 1.

Remarks
* MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.
* MATCH does not distinguish between uppercase and lowercase letters when matching text values.
* If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
* If match_type is 0 and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

EXAMPLE:

	A	B
1	Product	Count
2	Bananas	25
3	Oranges	38
4	Apples	40
5	Pears	41

=MATCH(39,B2:B5,1) Because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned. (2)
=MATCH(41,B2:B5,0) The position of 41 in the range B2:B5. (4)
=MATCH(40,B2:B5,-1) Returns an error because the range B2:B5 is not in descending order. (#N/A)

 

[Mike375]

A bit to look at there. Before I digest that I think I have a better way to explain things

On an Excel row would be something like

C1.......D1.............E1
Bill.......Wilson........2008-12-03_14-48-24 Wilson Letter 1

So lots of rows like that and of course the name of the Word.doc being different for each row.

In Excel I want to be able to click on a cell or button on a row and........give me what is in column H and for this row. The saving of the Word doc name would be similar......Insert "xyz" into column H for this row.

Would there be something that when I clicked on a cell in a row that it could nomintate that as the "active row" or something similar. In Access I have OnClick behind textboxes that open the Word doc and also save it as a file to disk and insert the name of the file to a textbox on the record. This clicking the texbox automatically selects the record or row.

 

[Mike375]

I am part of the way there. This runs macro1 no matter what cell I click on.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Target

With ActiveCell

Call Macro1

End With

End With

End Sub

Macro 1 is

Set appWD = CreateObject("Word.Application")
appWD.Visible = True

appWD.Documents.Open Filename:="C:/Letters/0Letter1Mike.doc"

I need that to be :="C:/Letters/the cell value clicked on + .doc"

 

[chergh]

Something like this maybe?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

call Macro1(activesheet.range("H" & target.row).value)

End Sub

sub macro1(strPath as string)

Set appWD = CreateObject("Word.Application")
appWD.Visible = True

appWD.Documents.Open Filename:= "c:\letters\" & strpath & ".doc"
end sub

 

[Mike375]

chergh,

I just tried yours but got debug with the first and could not find the word.doc on the next one. But I might have done wrong as I not too flash on this Excel stuff

I did the following an it worked. As a bonus by sticking the name of the Word.doc in to A1 the person can then see the last letter they selected. I suppose I could format that cell's background, bold type etc.

Range("A1") = ActiveCell.Value
Set appWD = CreateObject("Word.Application")
appWD.Visible = True

appWD.Documents.Open Filename:="C:/Letters/" & Format(Range("A1") + ".doc")

And I need to change the to a double click event

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Target

With ActiveCell

Call Macro1

End With

End With

End Sub

And then get this to work on active cell

Range("G33:G43") = ("Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

 

[chergh]

Did you make changes to you macro1 sub so that it expects a parameter i.e.

sub macro1(strPath as string)

 

[Mike375]

Did you make changes to you macro1 sub so that it expects a parameter i.e.

sub macro1(strPath as string)

No, I just tried the second as a macro and put the other on the Sheet event. I wasn't sure how to do it.

The other thing I have to work out is how to get cell values on a row (active row?) to have the Word doc open and then insert the cell values in Bookmarks.

 

[chergh]

Attacht he spreadsheet and I'll knock something together, going to be a lot easier than explaining how to do it.

 

[Bilbo_Baggins_Esq]

Attacht he spreadsheet and I'll knock something together, going to be a lot easier than explaining how to do it.

I agree, much easier to do it than to explain it.

 

[Mike375]

The spreadsheets (a couple I am playing with) only have macros and where I will type the name of a Word.doc file.

I will try and pull togther what I have as it is all over the place at the moment.

But so far I have all but one thing working from "activecell" The last one I just did

ActiveCell.Value = ("0AMPMike " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

Range("A2") = ActiveCell.Value
FileCopy "C:\Letters\0AMPMike.doc", "c:\Letters\" & Format(Range("A2")) + ".doc"

But the one I am habing trouble with is SaveAs


Whatever.SaveAs ("c:\StoreLetters\" & Format(Range("A2")) + ".doc")

I don't seem to be able to duplicate what I use in Access and I supsect the way I am opening the Word.doc is the problem as in

Range("A1") = ActiveCell.Value The active cell has the Wordoc name
Set appWD = CreateObject("Word.Application")
appWD.Visible = True

appWD.Documents.Open Filename:="C:/Letters/" & Format(Range("A1") + ".doc")

I have Microsoft Library Excel referenced

In Access I have

Dim WordObj As Word.Application
Dim WordDoc As Word.Document
Dim WordRange As Word.Range
Set WordObj = CreateObject("Word.Application")
Set WordDoc = WordObj.Documents.Open _
("C:\Letters\0Letter1Mike.doc")
WordObj.Visible = True

and then

WordDoc.SaveAs ("c:\StoreLetters\" & Format([Forms]![PrintAndClose]![LetterName]))

I will be heading to bed soon as about 11pm down here in Australia. Maybe I will weake up later and have SaveAs suprise

 

[Mike375]

The attached Excel is filled out with how I think it would look. I think it explains what I am trying do.

But if I get SaveAs under control then I am basically over the line except for the Excel data into Word bookmarks and the issue of storing many letters for the one person.

 

[chergh]

For your saveas thing you can use

activesheet.range("A2").value = ("0AMPMike " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

Whatever.SaveAs ("c:\StoreLetters\" & activesheet.Range("A2").value & ".doc")

First you need to decide on a format
Then you need to decide exactly what you want the spreadsheet to be able to do
Then you start coding.

Trying to figure out what you want from what you have typed above is more trouble than it's worth, especially as I suspect what you want is really quite simple.

Before you start trying to code something you really need to figure out exactly what it is you want.

 

[Mike375]

My problem with SaveAs is trying to get what goes before SaveAs as in Whatever.SaveAs

I have a model of what I want to and that is to duplicate as near as possible what I have in Access. As to the format of the SaveAs that would uplicate what I do in Access and that is 3 formats are saved at the same time. One with letter type at the start, one wiith last name at the start and the other with date at the start.

In Access the person's record is selected and the appropriate letter printed. In the process the open Word Template (with data in the bookmarks at that stage of the code) is saved with date/time stamp, a new record on a continuous Many form is opened and the Word file name is inserted into the new record.

On the Many form the code is in a texbox to open the Word doc on that record.

 

[chergh]

The document object goes before saveas, so in your case

worddoc.saveas

 

[Mike375]

This just worked.

Range("A1") = ActiveCell.Value
Set appWD = CreateObject("Word.Application")
appWD.Visible = True

appWD.Documents.Open Filename:="C:/Letters/" & Format(Range("A1") + ".doc")

appWD.ActiveDocument.SaveAs ("c:\TestExcel\" & Format(Range("A2")) + ".doc")

The "activecell" has the name of the word doc and the above is running from

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Target

With ActiveCell

Call Macro1

End With

End With

End Sub

 

[chergh]

it might work but it's ugly as sin, and has plenty of opportunities for things to go wrong.

You with statements don't actually do anything and if your on a different sheet to the one with data the macro is still going to fire.

Using activesheet, activecell and activedocument really should be avoided whenever possible as weird things can happen when you have multiple workbooks or documents open.

 

[Mike375]

activecell so far is the only success I have had. If I can get the whole thing working then I come back and refine it.

This one has me stumped and I had forgotten about it

This works

Range("A2") = ("xyz " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

But I can't get the second one to work. I just had the whole screen full of red writing

Range("A2") = (" & Format(Range("A1") " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

 

[Mike375]

Got it

Range("A2") = "" & Format(Range("A1")) + " " & Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss") + ".doc"

 

[Mike375]

Using activesheet, activecell and activedocument really should be avoided whenever possible as weird things can happen when you have multiple workbooks or documents open.

So would it be trouble free if the Excel file to do the letter stuff was the only Excel open. I am hoping this ActiveCell holds up because I can now do the lot with it.

I put a coupe of If/Then to stop it inadvertently working when clicking in a cell to type. For one of them a cell needs a 10 for the Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Target to work.

On Macro1 I put If ActiveCell.Value Like "0*" Then as the Word.doc file names have a 0 as the first character.