Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Excel - Returning A Cell Address


I am using some Hlookups to find some text in a datasheet, and i would also like to get the cell position (preferably in the form "A1", but if its in R1C1 i can convert that myself) of the cell which the Hlookup finds so i can then copy that specific cell (rather than referencing it via a HLookup) using VB code. If anyone can help with the code to get this to work i would be most grateful as i have been trying to work this out for days now!

Thanks

Robert




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Returning A Cell Coordinate In Excel To Visual Basic
i want to find out how to return a cells coordinate to visual basic. i have wrote functions to find how many rows there are in a table in excel and how many columns there are in a table in excel.

Eg if my functions return the value 6,8. then i want to know how to let my program know that it means cell f8. i cant use the cell(6,8) function.

Converting Excel Cell.Value To A Range.Address
.Would these poweful programing minds kindly enlighten this NewBie :
I have concatenated a column number and a row number using two InputBox
apps and placed the text in a cell way down the sheet . Now I need Excel to read
that value as a range value. Both InputBox variables have been declared as String variables . I can rename that combined text as a new variable and then use another input box and have the new combined variable as the "default:=" value and further Type:=8 , making it a cell address and yes the cursor moves to that position ; but I havent figured out an easier way to just programatically read the Cell.Value as A Range . Is there a way? What am I missing?
 All help more
than appreciated.
Thanks a million



Edited by - goldtuth on 1/27/2004 9:56:27 PM

Help Converting An Excel Cell.Value To Range.Address
Would these poweful programing minds kindly enlighten this NewBie :
I have concatenated a column number and a row number using two InputBox
apps and placed the text in a cell way down the sheet . Now I need Excel to read
that value as a range value. Both InputBox variables have been declared as String variables . I can rename that combined text as a new variable and then use another input box and have the new combined variable as the "default:=" value and further Type:=8 , making it a cell address and yes the cursor moves to that position ; but I havent figured out an easier way to just programatically read the Cell.Value as A Range . Is there a way? What am I missing?
 All help more
than appreciated.
Thanks a million



Edited by - goldtuth on 1/27/2004 9:55:57 PM

Excel: I Have A String Of A Cell's Address, How Do I Return It's Value?
Hi All, for various reasons I have concatenated a cells address, let's call the return "Quotes!B31" by using this forumula,

Code:"=Quotes!B" &VLOOKUP(C4,Q$4:S$100,3,FALSE)

What i want to to be able to do is have this formula return the value that resides at "Quotes!B31" but it is simply returning the string "Quotes!B31"

How can I get the value that resides at my concatenated address?

Thank you. Gordon

Writing A Cell Address Of A Cell In 1 Worksheet To A Cell In Another In Same Workbook
Hi……..
Its an urgent requirement….
I have an excel sheet. I need to write the macros in VB for the following requirement:
There are three worksheets in a single workbook.. worksheet3 contains some values in every cell. whenever user writes in something in the cell in worksheet1, the column number and row number from worksheet3 must get written automatically in the adjucent cell in worksheet1 only.

For example:
A worksheet 3 contains PLZ A,B in row 5 and column 7. These rows and columns are user defined. If i write PLZ 31, 20 in F26 in worksheet 1 then automatically G26 must contain value 75, G27 must contain a value 31 and G28 must contain a value 20.

I can't say when this value should get populated. Either on the lost focus of F26 or i need to run a macro manually for F26.
 what would be the code in both the cases? Is the former case possible? If i select group of cells like F26 present in worksheet1 and altogether run a macro then would it give the result?

Thanks in advance for your reply.
Regards...
Priyadarshini

Returning The Address
i whouldnt know what to google to find the answer to this so ill ask on a fourm. i whould like to make a program that can copy itself to another directory. i know how to copy just fine, my problem is that, i dont know how to return the current address of the program. say someone downloads my progam and saves it were ever(c:/downloads/stuff/) for example. how do i make my program return (c:/downloads/stuff/myprogram.exe) as a string, so i can make it copy it and save it to another directory?

Returning The Address Of A Variable In VB
Yo yo yo

In C++ I can do this
Code:
//return value
cout << "x = " << x << endl;

//retun the address
cout << "&x = " << &x << endl;


The out put of this is
Quote:
x = 47
&x = 0012FF7C


Now what I need to do in vB is get the same out put.

Does anyone know how to return the address of a variable in vB?

Getting The Cell Address As Well As Cell Formula
i have an excel sheet. i'm looking for a code which will goto Cell A1, copies the formula which is bieng used in the current cell, NOT what is shown in the cell as a text format. for example if the cell contains some total of $400, the formual actually bieng used to get that total is =sum(b2:c2) . So i want the VB to get =sum(b2:c2) in this case and pastes it in new blank sheet, by telling me the 1. cell address, 2. cell formula. It should goto specific range of cells, e.g. (a1:b200) and get formulas and pastes the formula values into another blank worksheets column. any help.....? please. waiting...

Returning Cell Reference
I have run into a bit of trouble in writing a function that will identify the cell position of a certain bit of text. It should be passed a variable valled "pattern" and then compare it the text in a cell in Column C. If the cell is hidden it will ignore that cell. If it does not match it will go to the next row in the coluumn (ie c1 to c2). When it finds a match it should return the cell position.

I have a couple of questions about this.
1) do i need to set pattern = to anything when i am declaring (ie the line commented out)

2) Is ActiveCell = "c1" the method of setting the starting cell as c1?

3) Once i have found the row and column values do i need to concatanate them or is there already a function that does this that i can set 'StartPoint' to (the last line commented out)?



Code:
Function StartPoint(pattern)

Dim pattern As String
Dim text As String
Dim r As Integer
Dim c As Integer

'pattern = pattern
ActiveCell = "C1"

Do While Not text = pattern

If Cell.EntireRow.Hidden Then
ActiveCell.Offset(1, 0).Select 'ignore hidden cells
Else
ActiveCell.Offset(1, 0).Select
text = ActiveCell.Value
End If
Loop

c = ActiveCell.column 'returns column of active cell
r = ActiveCell.Row 'returns row of active cell
'StartPoint =


End Function

Sorry if these questions are a bit basic (or just fundamentaly flawed) but i am new to VBA and my programming ain't that hot

Cheers in advance,


nairnie

Exchange Server Returning Strange Address String
I work on developing a system in VB. We use an Exchange server as mail system, and every time an email is sent within the LAN, the senders email address comes up something like this: EX:/0=ACOS AS/OU=FIRST ADMINISTRATIVE GROUP.

As we would like to send a reply to the sender, I am looking for a way to get a hold of the readable address.

Returning DB Query Results Using A Cell Ref
I am trying to create a macro that will query from a DB based on the user's defined project number. My problem is that I want the user to be able to define the project number each time they run the query and if I either use an Excel cell refernce or a "?" (in the section colored red below) the macro will not refresh the data onto my spreadsheet. The code is as follows:

Range("A9").Select

With Selection.QueryTable.Connection = Array(Array( _

"ODBC;DSN=PEF;UID=;PWD=;SourceDB=P:PEFDATApef.dbc;SourceType=DBC;Ex clusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted" _), Array("=Yes;"))

.CommandText = Array( _

"SELECT milestones.projectnum, milestones.sequence, milestones.description, milestones.date" & Chr(13) & "" & Chr(10) & "FROM milestones milestones" & Chr(13) & "" & Chr(10) & "WHERE (milestones.projectnum=Database!$A$18)" _)

.Refresh BackgroundQuery:=False

End With

End Sub

Getting Address Of A Cell
Guuyz,

Currently, i know that to get the address of a cell..
we use the address keyword..

lets say after i search i know that the data is located at "a1"
How do i extract out the column?

it might be (a1) or (ab21)..
so, how do i extract only the alphabets(column)

thankx in advance

How To Get The Next Row Of The Cell Address
I have a code that gets the firstaddress of a cell using find. I want to get the next row of the returned value. Adding 1 does not seem to work.

The code is

Set c = .Find("Supplier", LookIn:=xlValues)
if not c is nothing then
firstaddress = c.Address


I want to get the next row after the firstaddress. How do I move to the next row?


Thanks

Cell Address
I am a new and excited user of VB. I just wrote a small macro for an excel file and would like to display cell addresses for selected cells in a message box I have created. How would I do this?

Cell Address
Hi,

I know how to find a value in the worksheet and to find the cell address.

But how can I go to the next row or colum from the finding address

thanks
Lilach

Using Variable In Cell Address
I have created a variable reflecting a particular row number and i would like to use that in a range.

ex:
Dim RowNumber
RowNumber = ActiveCell.Row
Range("A,RowNumber")

Find Cell Address
hi, does anyone knows the formula to find the cell location of a number from a range of cells? for example, 10 is in F2. i need to find the cell location of 10 from A1:E10. Thank You.

Passing Cell Address
I currently have a simple macro recorded to run Solver:

Quote:




Sub solvethis()
'
' solvethis Macro
' Macro recorded 3/1/2004 by Information Services
'
SolverOk SetCell:="$E$13", MaxMinVal:=3, ValueOf:="1", ByChange:="$B$13"
SolverSolve
End Sub




However, I would like to make this so that the user could simply input a function with two cells references as the arguements so they would not have to manually run solver everytime or click a button. A somewhat example would be:

Quote:




Function bp(Q, R)
SolverOk SetCell:="Q", MaxMinVal:=3, ValueOf:="1", ByChange:="R"
SolverSolve
End Function




With Q and R being the cell addresses. I cannot figure out how to do this. Any help would be appreciated. Thank you.

Getting A Cell Address From A Range
I am new to VBA and am looking for a way to get a cell's address for use in a program.

I'm doing this on MS Office 97.

Here's what I'm after:

I have an Excel file that is data from a mainframe system. The size of the data is variable so I don't know what the last column data appears in. I need to find a way to get the cell address (i.e. B1, C4, etc) from a range after I've used the CurrentRegion.Select code. The Select code works fine but I can't get it to give me the address of the last cell in the selected range. Any ideas?

Thanks!

From Textfile To Ms Excel Format (example: Address.txt To Address.xls)
Hello there,

I have to do a report in ms excel fromat. The data comes from a text file(example: address.txt) and the fields are not yet separated by a delimiter ("/" "," " " etc.). I delimit it with ms access and saving the table to "address.mdb" file then make queries . Then export it into an excel format.

My problem is that the user might not follow my instructions.

I need visual basic 6 to do all this. from textfile to ms excel format.

I need help on this, and does anyone can send me a vb program related to this?

Thanks,

Karl

Finding The Address Of The Targeted Cell
Hi there. I got the following vba code to search for the targeted value in a specified column. However, apart from finding the value, I also want to identify the address of the targeted cell so that some calculation can be done.


Code:
column1 column2 column3
No. of days leaveMin leaveMax
8 12/6/2004 20/6/2004
5 18/7/2004 23/7/2004
3 1/8/2004 4/8/2004


Dim LB, leaveMin, leaveMax, nextLeaveMin As Date
Dim cnt, cntMax, colIndex As Integer

Worksheets("Sheet1").Activate
LB = CDate(TextBox1.Text)
colIndex = 2 'targeted column no.
cntMax = Cells(Rows.Count, colIndex).End(xlUp).Row 'find the no. of rows
with values
For cnt = 2 To cntMax
leaveMin = Cells(cnt, colIndex).Value
leaveMax = Cells(cnt, colIndex + 1).Value
nextLeaveMin = Cells(cnt + 1, colIndex).Value
If LB => leaveMin And LB <= leaveMax Then
'here, find the adress of the cell in columa A next to the cell of
column B meeting the criteria
'for instance, if LB = 20/7/2004, then the address of the cell in
column A is A3 (with value=5) and the sum will be 8+5=13.
Set sumRange = Worksheets("Sheet1").Range("a2:a" & address of
the cell in column A
day_deduct = Application.WorksheetFunction.sum(sumRange)

ElseIf LB > leaveMax And LB <= nextLeaveMin Then
'here, find the adress of the cell in columa A next to the cell of
column B meeting the criteria
Set sumRange = Worksheets("Sheet1").Range("a2:a" & address of
the cell in column A
day_deduct = Application.WorksheetFunction.sum(sumRange)
End If
MsgBox day_deduct
Next

Can anyone please advise?

Cheers.

How To Get The Cell Address From The Selected Region?
Hi all,
My requirement is like this. The user might select a region on excel worksheet across cells. I need to get the all cell values from the user selected region. How do I get the cell addresses of the selected region's start and end addresses?

Any help is welcome
Shivaraj

Mouse Pointer Cell Address
Hi,

Is there a way to determine the address of the (inactive) cell over which the mouse pointer is hovering? I'm using Excel '97 VBA.

Thanks,
Kral

Help Converting A Cell Value To Range Address
Would these poweful programing minds kindly enlighten this NewBie :
I have concatenated a column number and a row number using two InputBox
apps and placed the text in a cell way down the sheet . Now I need Excel to read
that value as a range value. Both InputBox variables have been declared as String variables . I can rename that combined text as a new variable and then use another input box and have the new combined variable as the "default:=" value and further Type:=8 , making it a cell address and yes the cursor moves to that position ; but I havent figured out an easier way to just programatically read the Cell.Value as A Range . Is there a way? What am I missing?
 All help more
than appreciated.
Thanks a million


Edited by - goldtuth on 1/27/2004 11:55:57 PM

Live Life Fast Cause Time Slows Down And We Get More Of It!!!!!!!!! Do Get Have Now

Excel - Making A Cell Equal To The Value Of The Formula In The Cell
I thought I'd seen something on this already, but I coudln't find it. My appoligies if I'm creating a duplicate thread.

I'm writing a macro in excel to fill in data in a database. Unfortunately, my titles for each entry have to be taken from multiple cells and put into one cell. The code to place the title in the title cell is:

Code:Dim title As String
    title = inputname & "!$B$3, " & inputname & "!$C$3, " & inputname & "!$D$3, " & inputname & "!$E$3, " & inputname & "!$F$3, " & inputname & "!$G$3, " & inputname & "!$H$3, " & inputname & "!$I$3"
    title = "=CONCATENATE(" & title & ")"
    ActiveCell.Value = title

It works fine. The problem I'm having is that every time I save and reopen my database excel pops up with this "The workbook you opened contains automatic links to information in other workbooks..." It's getting very annoying. And it also gives me problems if anybody touches the already filled title cells. I was thinking that there might be a way to make the formula equal to the value of the formula. For example making a cell have the value 4 instead of the forumula "=2+2". Thanks for any help.

How To Link An Excel Cell To An Access Table Cell
I am trying to import an populated excel form into access using just specific cells (where the data is located) only. I am able to open excel and get the data from the cells but how can i assign them back to my access table? This is like importing a populated excel form into access database.


I used these to get my excel info

Set excel_app = CreateObject("Excel.Application")
excel_app.Visible = False
excel_app.Workbooks.Open FileName:=mypathname & myfilename
Set excel_sheet = excel_app
x= excel_sheet.cells(10, 4).Value 'To get the excel cell value'
y= excel_sheet.cells(12,4).Value

Now I want x to be assigned to table "AppendTable" in column "AppColumn1"
and y in column "AppColumn2"

help!!!

Identify Cell Address Of The Last Entry In Range
I have a workbook where data is added to it each night, the following morning or at some other frequency, I need to know what the cell address of the last row that was added is. The data always starts in cell A13 and can go up to A49. How can I determine what the last cell that has text or data in it...? On any given day...?

Associate A Target Address With A Particular Cell Named
Hi all,

My worksheet have allot of cells named. When the user RightClick on a cell, I want to perfom some code.
But I need to know the name that I gave to this target. Here' my code. It doesn't work.

Do someone know how to do that?

Code:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

dim current_cell as string
current_cell = Range(Target.Address).name

end sub


Thanks.

Fred

Problem In Tracing The Address Of The Last Typed Cell
Dear Friends,

            I am writing a macro of which one part of it is to trace the address of the last typed cell. I have given the following command to accomplish this task:

    sqcadd=ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address

    The problem is, whenever I delete the contents of the cell, the address of the deleted cells is still being shown as the last typed cell.

    For example, in simple terms, say if I have contents in cell A1:C5, when I execute the above statement for the first time the address of the content of the last typed cell is C5. And if I delete the contents in cell A1:C5 and if I execute the above command for second time it is still showing C5. But my requirement is to have address as A1 as the last typed cell since the contents(A1:C5) are deleted.

    Requesting your kind help in this regard.

Regards,
S. Sivakumar





     

Determine Cell Address (RxCy Or Ay) Of Command Button?
Perhaps this is a stupid question, but I am trying to use a set of command buttons to sort my datasheet based on the column in which the command button is located.

At present, I have this code:

Private Sub CommandButton6_Click()

bankData As Range
Set bankData = Range("Market_Risk")
Selection.Sort Key1:=Range("g16"), Order1:=xlAscending
Range("g16").Select
End Sub

As shown, the code sorts the data in Ascending order by column G. On the worksheet, CommandButton6 is located in Column G. Unfortunately, I have about fifty columns of data (and fifty command buttons), so I would like to be able to create a single macro that dynamically determines the cell address of the command button that was just clicked.

I've tried this:

Private Sub CommandButton6_Click()
Dim firstSort As String, bankData As Range

firstSort = ActiveCell.Address
bankData As Range
Set bankData = Range("Market_Risk")
Selection.Sort Key1:=Range(firstSort), Order1:=xlAscending
Range(firstSort).Select
End Sub

Unfortunately, when the user clicks on the command button, the underlying cell ("G16") is not activated. How can I either 1) determine the cell address of the command button or 2) determine the cell address of the most-recent mouse event?

Any help would be greatly appreciated!

Dynamically Get The Cell(access2000) Address That Called The Function..
Hello, I'm just wondering is there anyway in a VBA function to get the cell address that called the function?

Here is an example.

Say, I have a function named GetResult(Name as string)

And lets say cell value in cell A1 is "=GetResult("Bob")" and cell value in cell A2 is "=GetResult("Jones")"

Is there anyway that when GetResult in cell A1 is called, it knows that it is A1 that called it, and when GetResult with argument "Jones" in A2 is called, it knows that it is A2 that called.

Thanks

Closing Access From Excel And Returning Focus To Excel...
I'm using a combination of excel and access functions and in the process of automating this.

I have a button in Excel that runs the following:
__________________________________________
Worksheets.Application.ActiveWorkbook.FollowHyperlink ("C:FILE.mdb")
__________________________________________

FILE.mdb contains an auto exec macro that performs the required functions, including closing access (quit) when finished.

However, it only closes if FILE.mdb is opened manually, when FILE.mdb is called by the excel VBA the process problems occur.

How can I get Access to close once finished and then get the next line in the excel VBA to run?

please help...

cheers.

Storing The Cell Address Of Subtotals In Order To Generate Grand Total Figure
Hi,

With the help of you guys, I was recently able to enable a Sub to generate subtotals in a range of data (without the use of EXCEL worksheet function, 'SUBTOTAL').

My next dilemma is generating a row beneath all of the subtotalled data, which is a GRAND TOTAL of each individual SUBTOTAL figure.

The code I have to genereate the subtotals is as follows:

Code:Sub stotal()
Dim iRow As Integer
Worksheets("Reconciliation").Activate
Range("A1").Select
iRow = 1 'Start at row 1
'add a new line in sheet at each change in value for Col A.
'this will allow room for subtotals to be added
Do Until IsEmpty(ActiveCell)
    If ActiveCell.Value <> ActiveCell.Offset(1, 0).Value Then
        ActiveCell.Offset(1, 0).Select
        Selection.EntireRow.Select
        Selection.Insert Shift:=xlDown
        ActiveCell.Offset(0, 2).Range("A1:P1").Select
        ActiveCell.Offset(1, -2).Select
        Call AddUp(iRow) 'Calculate and fill in the subtotals
        iRow = ActiveCell.Row 'Save the starting point of the next series of numbers to be added up
    Else
        ActiveCell.Offset(1, 0).Select
    End If
Loop
End Sub

Private Sub AddUp(X As Integer)
Dim iCol As Integer
Dim tblone As Range
Dim tblcount As Integer

iCol = 3

'set the current region for tblone, to determine the number of lines to be summed
'in subtotal
Set tblone = Worksheets("Reconciliation").Cells(X, 1).CurrentRegion

tblcount = tblone.Rows.Count

Cells(tblcount + 1, iCol).FormulaR1C1 = "=SUM(R" & X & "C" & iCol & ":R" & tblcount & "C" & iCol & ")"

End Sub


Now, my thinking at the moment, is that each time a new line is inserted for a subtotal, the address of this cell is stored. At the completion of all subtotal rows being inserted, the GRANDTOTAL cell is the sum of each stored address, displayed something like:

Code:ActiveCell.FormulaR1C1 = "=SUM("addressone"+"addresstwo"+"addressthree"+" . . . etc. ")"

Now, the question is: How do I get each subtotalled address to be stored and then totalled as a GRANDTOTAL.

I am assuming that this is the best way to do this task, but someone may know an easier/quicker way.

I have attached the Worksheet that I am working with, just to give an idea of the data.

Thanks in advance,

Xav6

Returning SQL To Excel
I've got a lengthy piece of SQL which I need to be able to run and return to excel. With this sort of thing I normally cheat and use the record function to create a background query which I can then add my variables into etc but because this SQL statement is so long it's just not playing ball

I think this is due to the sql statement being formed in an array in VB which is limited to certain parameters(?)

Can anyone offer any advice on how this can be done?

Any advice much appreciated...

How To Read An Excel Spreadsheet Column, That Is, Read From The Top Cell To Bottom Cell (like File R
How to read an excel spreadsheet column, that is, read from the top cell to bottom cell (like file read to eof)? how can I know which cell is the last cell?

thanks

Returning Data From Excel
I'm working with a patient level markov simulation model in excel which is executed from within a VB6 module. The model has 10000 patients & takes several hours to run. I would like to pass the current patient number from the excel application to the VB form to give an indication of how far the model has got left to run. I can do it within excel with the statusbar function but now that i'm using VB to run the program excel is invisible. Does anyone know how to pass information from within an excel macro to the VB program?? Many thanks!

Excel VBA, Returning The Column
Hi,

In Excel VBA the columns and column properties can be used to return the numeric equivalent of the column. Is there a way to actually return the column letter.

I know you could create an array to hold A,B,C etc.. but I was just wondering whether there was an easier and better way. I have tried the address and addresslocal properties and they return the whole range i.e. "A:A" or "A3:A67" what I would like is something to just return A

Is there an easy way to do this...

Cheers
Steve

Returning No. Of Used Rows In A MS Excel File
Hi everyone

im creating a Vb6 application.
It's like this,

->I will have to count the no of rows containing info in the excel and use
it as sl. no in the application.
->The excel file is used as a database.

thanks,
Rajesh.M

Excel RecordSet Returning A NULL Value
Hi,

I am reading from an Excel spreadsheet and inserting into a database.
Everything is working well, except for this: When I get the data from Excel, some values in one of the Excel columns are returned as NULL, even though they have data in them in Excel.
After some debugging, I found that the RecordSet field type for that value is an adVarWChar. In the spreadsheet, those fields returning a NULL are stored as numbers, while those fields returning the correct value are stored in Excel as text.

My question is: How can I make the RecordSet treat the numbers as text so that a NULL is not returned (ie, treat all the values in a column as text)? Is there anything wrong in the way I create and open the RecordSet, or anything else I should do or add?

Thank you for your help,

Regards,

Ziad


Here is the code that gets the Excel data.
Please also note that I am using VB6 on a Windows XP machine.
Code:
  strExcelConnection = "Driver={Microsoft Excel Driver (*.xls)}; Dbq=" & strFileName & "; DefaultDir=" & ExtractFilePath(strFileName) & ";"
  Set objExcelConn = New ADODB.Connection
  Set objExcelRS = New ADODB.Recordset
  objExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
  objExcelConn.ConnectionString = "Data Source=" & strFileName & ";Extended Properties=""Excel 8.0;HDR=YES;"""
  objExcelConn.Open
  strSQL = "SELECT * FROM [Sheet1$]"
  objExcelRS.Open strSQL, objExcelConn, adOpenStatic, adLockOptimistic





Edited by - ziad123 on 3/4/2004 8:11:10 AM

I Am Exporting An Access Query To Excel, How Can I Define The Excel Cell Size/format
I am using TransferSpreadsheet to Export a Query to Excel with a button from Access.

How can I define in Excel when I export it, the size of the cells, the type of letter (Arial, Bold), The Background color.

Private Sub Impacto_Click()
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:="Impact", _
Filename:="C:Impact.xls", _
hasfieldnames:=True
End Sub

Returning Control (Focus) To Word From Excel
Hello. I have an "application" that I am creating that involves both Word and Excel. Most of the automation is in Word, including data storage between application sessions.

From Word, a Userform is loaded to collect information. On a button click event, the Word macro launches an Excel instance, loading a specific file. Then, the contents of the Word Userform are placed into cells in the excel document. At this point, the Word document still has the Userform loaded, but Excel has the focus. The user works with the Excel workbook and then hits a custom menu button that calls an Excel macro. The Excel macro then attempts to return control to Word with the dialog box still active.

The problem is this: when the Excel macro runs, it puts excel into a "pending" state, with the hourglass displayed, but it does not re-activate the Word instance. I can manually select the Word instance on the taskbar, and finish the processing I need to complete, but I cannot get Excel to return control/successfully activate the Word instance.

The code I am using to call Word back up from Excel is as follows:

Dim appWord As Word.Application
Dim doc As Word.Document
Set appWord = GetObject(, "Word.Application")
appWord.Visible = True
appWord.Activate
Set doc = appWord.ActiveDocument
doc.Windows(1).Activate

I have tried several variants on the last line, including doc.Activate, doc.ActiveWindow.Activate, etc.

Any ideas are greatly appreciated! :-)

Excel Returning Wrong Number Of Sheets
I'm using code to determine the number of sheets in an excel file, so I can scroll through it and retrieve information.

However, when I pull back XLwb.Sheets.Count, I get back 10.

The Excel worksheet contains only 8 sheets (its being generated by another application). In Excel, I've tried show hidden, and there is one hidden column.

At run time, if I try and loop through all 10 sheets, when it hits number 10, I get an ActiveX automation error, saying it is out of range.

On other Excel sheets I've tried, its actually returning the correct value, but not others, so I can't even manually adjust it by -1.

Any one see this before?

Returning The Path Of An Empty Folder Via Excel Vba
Returning the path of an empty folder via Excel vba--can this be done? I want to be able to find a folder and return the path structure to a cell--e.g.,

MyPath = "C:My DocumentsMy Sub Folder"
Range("A2").value = MyPath

Is there a way to pull up something similar to the GetOpenFilename method - like a GetOpenPath method? I've searched for this, and even came up what appears to look like a "winner" (at a Japanese website for Access ). But I don't know Japanese, and don't know much about Access either.

I can do it if I've got at least one-file in the folder using the getfilename method, but what if you've got an empty folder?

Suggestions?



Edited by - MarkFrrl on 12/1/2006 6:45:44 AM

Find Location Of A Cell In Excel-import From Excel Into Access
Hi
I want to import some columns from an excel spreadsheet into an access table using vb6.

I am going abt this by finding the location of the cell but am having problems this way


Code:
Private Sub ImportExcelAttend()
Dim imA As New ADODB.Recordset 'Recordest for temp table
Dim ex As New Excel.Application
Dim I, J, a As Integer
Dim TheFile As String

imA.Open "select * from tbl_tempAttend", MyConn, , adLockOptimistic

ex.Workbooks.Open txtLocation.Text
ex.Cells(I, J) = "%att"


however i cannot get it to work. any help this way would be appreciated.

OR if you know another way to import these few colums with unknown location of cells into access i would be very grateful. Location will always change every time.

Regards
JohnnyH

How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,


suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.



Code:

VB Code:
Private Sub cmdExport_Click() Dim MDur As Integer Dim obj1 As New Excel.Application Dim wsheet As Worksheet Dim wbook As Workbook  Screen.MousePointer = vbHourglass  Set wbook = obj1.Workbooks.Add Set wsheet = obj1.Sheets(1) Dim i% Dim j% Dim Ce%  For i = 0 To MSHFlexGrid1.Rows - 1     For j = 0 To MSHFlexGrid1.Cols - 1                     If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then             MDur = MSHFlexGrid1.TextMatrix(i, 55)             MDur = MDur / 30             wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd")             wsheet.Cells(i + 2, j + 1).Font.Bold = True             obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed             obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow             For Ce = 0 To MDur                              Next         ElseIf j > 7 And j < 55 Then             wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)             wsheet.Cells(i + 2, j + 1).Font.Bold = True         Else             wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)         End If     Next Next  For i = 0 To 1     For j = 0 To MSHFlexGrid1.Cols - 1         wsheet.Cells(i + 2, j + 1).Font.Bold = True         wsheet.Cells(i + 2, j + 1).Font.Color = &H800000     Next Next Application.DisplayAlerts = False With obj1.Sheets(1).Range("H2:S2")     .Select     .Merge End With  With obj1.Sheets(1).Range("T2:AE2")     .Select     .Merge End With  With obj1.Sheets(1).Range("AF2:AQ2")     .Select     .Merge End With  With obj1.Sheets(1).Range("AR2:BC2")     .Select     .Merge End With obj1.Rows(2).HorizontalAlignment = Excel.xlCenter obj1.Columns.AutoFit Screen.MousePointer = vbNormal obj1.Application.Visible = True End Sub




also attached another excel file call Sheet, this type of shading is required here as in the Book1 which is attached here,

so i have can have the copy of the same picture in 4 different Width,

1st picture width will be of 24 and
2nd will be 50,
3rd will be 75
4th will be 100

so with this 4 different images i can cover the area in the cell,

the only code now needed is

1. how to place the Picture in the ExcelSheet through VB.6

2. how to specify the starting part (or X axis or Cell Range in Excel Sheet) of the picture to be placed

i hope, if i have the code for the above two, then it will be solved. Kindly check this and reply me.

Thankyou,
Chock.

How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

 I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,


suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.


Code:Private Sub cmdExport_Click()
Dim MDur As Integer
Dim obj1 As New Excel.Application
Dim wsheet As Worksheet
Dim wbook As Workbook

Screen.MousePointer = vbHourglass

Set wbook = obj1.Workbooks.Add
Set wsheet = obj1.Sheets(1)
Dim i%
Dim j%
Dim Ce%

For i = 0 To MSHFlexGrid1.Rows - 1
    For j = 0 To MSHFlexGrid1.Cols - 1
           
        If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then
            MDur = MSHFlexGrid1.TextMatrix(i, 55)
            MDur = MDur / 30
            wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd")
            wsheet.Cells(i + 2, j + 1).Font.Bold = True
            obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed
            obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow
            For Ce = 0 To MDur
                
            Next
        ElseIf j > 7 And j < 55 Then
            wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)
            wsheet.Cells(i + 2, j + 1).Font.Bold = True
        Else
            wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)
        End If
    Next
Next

For i = 0 To 1
    For j = 0 To MSHFlexGrid1.Cols - 1
        wsheet.Cells(i + 2, j + 1).Font.Bold = True
        wsheet.Cells(i + 2, j + 1).Font.Color = &H800000
    Next
Next
Application.DisplayAlerts = False
With obj1.Sheets(1).Range("H2:S2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("T2:AE2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("AF2:AQ2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("AR2:BC2")
    .Select
    .Merge
End With
obj1.Rows(2).HorizontalAlignment = Excel.xlCenter
obj1.Columns.AutoFit
Screen.MousePointer = vbNormal
obj1.Application.Visible = True
End Sub

Thankyou,
Chock.

Returning The Final Line Of A Text File To Excel..
Hi All,

I'm a newbie to VB and am trying to write a script that searches a directory and returns (a) Filename (b) Row count and (c) the final Line of the text file (which in all cases concened is 6 characters)

So far I've got:

Sub SCP2005()
'
Dim SourceFile As String
Dim LFile As Integer
Dim strBuffer As String

LFile = FreeFile

Set fs = Application.FileSearch
With fs

.LookIn = "C:2005"
.Filename = "*.sls" 'Was "*.txt"
.SearchSubFolders = True

If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
SourceFile = .FoundFiles(i)

Open SourceFile For Input As LFile

Do While Not EOF(LFile)
Input #LFile, strBuffer
intlinesReadCount = intlinesReadCount + 1
Loop

Worksheets("SCP 2005").Cells(i + 5, 1) = .FoundFiles(i)
Worksheets("SCP 2005").Cells(i + 5, 2) = intlinesReadCount - (2 * i)

Close LFile
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub

Now this returns the correct filenames and row counts, but I'm stuck as to how to return the final line of the file into "Worksheets("SCP 2005").Cells(i + 5, 3)" now.....

Any ideas?

One more thing I'm very lost on (I've not got very far with string manipulation yet)...

each row of the data within the files contains a variable value in characters 88&89, either "00" or "79" - how can I count how many lines have the 79 variable in them and return this too?

Any help would be much appreciated!!!

Cliff

How To Assign A Cell/Range Address In A Variant Type Variable To Another Variable
Sir,


I have the following piece of code which looks up a certain date in several wrksheets and aims to return the value in a cell located in the next two columns (same row) where my look up value is.

For i = 1 To 23
    Worksheets(strFilename(i)).Activate
    Range("C4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    

Dim strFilename(1 To 23) As String, strFilename2(1 To 23) As String, strPath As String
Dim dtActProg As Date
Dim rngSearch As Variant
Dim c As Variant, c1 As Variant

    Set rngSearch = ActiveCell.CurrentRegion
    
       
       
    With Worksheets(strFilename(i)).Range(rngSearch.Address)
        Set c = .Find(dtActProg, LookIn:=xlFormulas)
        If Not c Is Nothing Then
            
            Sheets("Sheet1").Select
            Cells(i, 2) = c.Value

' Returns the date I enter via an Inputbox in the privous section of my code

' In the remaining section of my code I need to assign the address of the cell located in the 2nd column next to where code looks up date value assing it to a variable and return the value on Sheet1 next to where code writes c.vale

For example if c.address="$A$2" then c1.address must ve "$A$4 which will be listed on Sheet1 together with my dates entered via ListBox


                End If
   End With
Next i

Thanks,

Fatih Can
Petroleum Eng., MSc.

Cell Format, Determining Type Of Cell Content (string/real/integer) And Cell Size
I am not sure whether I am at the right forum, but I am exporting Excel values in a Word document.

Unfortunately -my copy paste action doesn't take in account the cell_properties, so in some cases a value of 9,9999999999999999999999999999998E-4 is entered instead of the 9,9 E-4 or even 0,001 that is shown in the excel sheet.

I'm having trouble with finding the set format command (e.g. Tutbo Pascal: write(value:8:3) -or someting) and I also have strings in these cell -I am not sure if it gives any problems if one sets every zell to 2 decimals, if so -ho can I determin the type of cell content ...
And how to set the cell size to size of content

Could anyone help me, I 'm much obliged

Copyright 2005-08 www.BigResource.com, All rights reserved