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




Cross-referencing Data In Multiple Excel Worksheets


I am very new to this, so please bear with me. I have two worksheets with an overlap of data between them. I want the second worksheet to update every time the first worksheet is changed, as well as have the updates appear in red bold. The problem is, the sheets aren't set up the same way, so I am trying to find a way to cross-reference columns and rows in each sheet until it finds matching column/row labels from sheet to sheet. Once found, I want it to replace the cell on sheet two with the information from the cell on sheet one. I hope I explained everything clearly. Please help! Thanks very much in advance.

Alexis




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Referencing Excel Worksheets
Hi there, I am having a brain meltdown, how do I reference a worksheet within VBA with a variable such as

Code:
Dim WksBrokers As Worksheet

Set WksBrokers = Sheets(listView1.ListItems(a))


where listview.ListItems(a) is the name of one of the sheets in the workbook



Edited by - Jrogers on 10/31/2006 2:54:47 AM

Vb, Excel And Multiple Worksheets
the workbook i've been using in my vb app has just been changed from having 1 worksheet to having many worksheets. the problem i have encountered is when i do my application.calculate not all worksheets get updated. is there a solution to this?

Working With Multiple Excel Worksheets
i have seven excel files in a folder.
i want to create an eight one, with first column from the first sheet, second from the second, and so on.....

how can i write a .vbs file to automatically do that.

thanks,
Imran Sajwani
Illinois Institute of Technology

Importing Excel Into Access (multiple Worksheets)
i have written code that will import multiple excel worksheets from multiple excel files. but i have to name the specific worksheet im wanting to import from.

is there a way to import multiple excel worksheets without having to name the specific worksheet?

i have excel files that may have 1 or more worksheets and they will have different names.

i tried searching and did not find anything that would help.

thanks

Reading Excel File W/multiple Worksheets
Is VB capable of reading in an excel file that contains multiple worksheets? I need to externalize alot of data, and I don't want to have 10 or more text files, so I would rather have 1 excel file with 10 worksheets.

Thanks.

Running An Excel Macro On Multiple Worksheets
I have the following macro:

Code:Sub PSAPerfBreak2Macro()
'
' PSAPerfBreak2Macro Macro
' Macro recorded 9/11/2003 by VJ Occhino
'
Dim rval As String
Dim nval As String
Dim rng1 As String
Dim rng2 As String
Dim finr As String

   nval = ActiveCell.Value
   rval = InputBox("Enter text to replace:")
   finr = "g1:g500,n1:n500"
    Range(finr).Select
   If rval = "" Then
    MsgBox ("Canceled")
   Else
   Selection.Replace What:=rval, Replacement:=nval, LookAt:=xlPart, _
   SearchOrder:=xlByColumns, MatchCase:=False
   End If
'
End Sub


Currently, it updates the two ranges on the ACTIVE worksheet that I define in my variable "Finr". However, I would like the same "find & replace" macro to work in another range in another worksheet in the same workbook. Is there a way to define my finr as not just"g1:g500,n1:n500" in the current worksheet but a range in another worksheet as well?

Thanks in advance!!!

Excel Macro - Formatting Multiple Worksheets
Hello~

I need help with a macro I am trying to write. I have a macro that takes data dumped into Excel from a server database, and then splits the data into multiple worksheets based on the number of categories in the table and formats the cells. At the end, I then need to format the page margins and header & footer text. Right now the macro does this individually to each worksheet as it creates it, but it is bogging down the program. I figured it may, possibly, work faster if it does all formatting at the end. However, I can't find a way to do this. Manually, you can select all the worksheets and change them all at once using the Page Setup dialog box. I've tried doing this:

Code:Worksheets.Select
With ActiveSheet.PageSetup
     (Insert Formatting Here)
End With

The "Worksheets.Select" line does indeed select all of the worksheets. However, when the macro is run, it only formats the first worksheet.

Any ideas as to why I can do this manually, but not via macro?



Edited by - trumpet326 on 7/17/2003 7:51:52 PM

Cross Sheet Referencing
Ho can i tell visual basic to get a value from one sheet and then put it into another??

Thanks
Ryan

Cross-forms Referencing
Moved to .NET Newbies...



Edited by - Celerator on 1/19/2005 10:00:21 AM

Plotting Chart With Data From Multiple Worksheets
I'm trying to plot a chart using data from more than one worksheet in Visual Basics using Excel language. When plotting, it ignores everything except the first worksheet, though, it will plot manually.

Can anyone solve this?

Help With Opening Multiple Txts Files In A Single Excel Files With Multiple Worksheets
Hi,
I am new to using visual basic code as I usually use it for making macros inside the Excel program.

I would like to to open numerous text/ascii files into a excel file. I would like to write VB code that I can point to a directory (and it's subdirectories) to identify text files in the folder and import the data of each file into one excel file. Each separate file would be opened into a new work sheet. File1 goes to excel file worksheet 1, file2 same excel file worksheet 2 etc. etc. What is the best way to do this? Is there a script out there that already does this?
What I want to avoid is manually open up and copy each of these fields to a new or manually use the import text wizard as we will a large number of separate files with data.

This forum thread has the code which seems to do what I need but I got it to work only once and then i get errors all time.
http://www.developerfusion.com/show/1897/
Does it need to run as VB project or as a macro in excel??

microsoft has a example of what i want to do but again not in enough detail for a novice programmer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrsamsamplespreadsheetwithtextpulledbyadonet.asp

http://www.lacher.com/examples/lacher02.htm is another example but i want to opem multiple files in one goal

After this code we have rearrange our using another excel marco and graph the data so we have a lot of work ahead of us
Thanks for your help in advance!

Frank

Cross Referencing Outlook Emails Using VB
Ideally I want to store a link in an Access or SQL database which, when clicked, will open an email stored in Outlook.

The aim is to store each customers emails with their other details

Appreciate any ideas on handling this problem

Transferring Data Between Excel Worksheets
I am creating a mini system where a user can highlight a number of cells and then on the click of a button the data in these cells is transferred to designated cells in another worksheet. Any ideas on how to code this?

Thanx

Copying Data Across Excel Worksheets (was: Help!!)
I need urgent help!
I want to select a row from a worksheet and paste it in another worksheet. this is simple. The difficult is to copy for example row 2,6 and 12 from worksheet1 and paste them in rows 1,2,3 in worksheet2.
Help plz..

Import Data From Excel Worksheets Into Access
Hello all,

I have to read data from an Excel workbook into Access table. This workbook has around 30 worksheets. I used the transferspreadsheet method and loaded data from 1 worksheet into Access. But how do I look thru each worksheets and read the data into access. Is there any other method besides transferspreadsheet method?

The columns for every worksheet is the same but the number of rows are different.


Thx

Reggie

Crystal - Multiple Cross-tab
Is there a way to create a simple report with 2 cross-tab in the report. My problem is that when the report is shown, the second cross-tab appears over the first one.

Thanks

Multiple Worksheets
I am trying to run through all worksheets in my workbook using the for loop. The following code isn't working and I can't figure out why.


Code:
Option Explicit

Public Function Merge_Date_Time()
Dim index As Long
Dim worksheet As Object

For Each worksheet In Workbooks(ThisWorkbook.Path).Sheets
Do While Range("a1").Cells(index, 1) <> 0
Range("e1").Cells(index, 1).Formula = "=concatenate(a1, b1)"
index = index + 1
Loop
Next worksheet
End Function

Vba And Multiple Worksheets
I am trying to write a macro to pull data from 1 of multiple worksheets and enter it in another sheet. The data is addresses up to 3 possible. I need to use the last entered data (row) from whichever sheet is chosen using a userform. Can anyone help me with a macro for that? Thank you!

Multiple Worksheets
I am putting data from a temporary file onto an excel worksheet. Is there any way to know if I have reached the maximum number of rows on a worksheet, tell my datafile to hold on, create a new worksheet, and tell my file to resume output. (I mainly need help with telling my datafile to pause and telling it to resume when I create a new worksheet). Any Suggestions would be greatly appreciated.

Heres the code that puts the data from the file to a worksheet (I created the "m_excel" object earlier in my code):


m_Excel.Workbooks.OpenText (filename:=DataFile,Origin:=xlWindows, DataType:=xlDelimited, Other:=True, OtherChar:=Delimiter, TextQualifier:=xlDoubleQuote)

Excel-VBA Q: Mill's Posting Excel 101: Filtering Data Using Multiple Combobox
Sub: Excel-VBA Q Mill's posting Excel 101: filtering data using multiple combobox - how to indirectly reference the

I joined this group yesterday. Thanks to all who are actively participating. I read Mill's posting 'Excel FAQ - Excel 101 ' ; thanks for the sample code.

I love Excel, and has been using it for 7 years - good at formula, macros etc, not programming with VBA. Now I want to venture into that area. The last time I used VB was in 1998 - VB5, worked just on one project. I am good at OOA, OOD, and have done C++ programming.

I have a question with reference to the code found in the Lesson 3 - Having items in one ComboBox filter items in another ComboBox.

In the FilterList() subroutine, could I indirectly specify the argument for the Case statement through a cell reference instead of directly specifying it in the subroutine.
e.g.
Select Case strCompany
Case $D$1

Select Case strCompany
Case "Apple"


Actual code from Mill's posting:

Sub FilterList()

Dim strList As String, strCompany As String, strEmployee As String

On Error GoTo FilterListError

strCompany = Range("B1").Text

Select Case strCompany
Case "Apple"
strList = "=$F$2:$F$6"
Case "IBM"
strList = "=$G$2:$G$4"
Case "Microsoft"
strList = "=$H$2:$H$4"
End Select

Regards & Thanks

Searching Across Multiple Worksheets Via VB6
I'm trying to figure out how to search for text across multiple worksheets
without knowing there names. I've tried kludge'ing it together from different
threads in the archives but they are all for VBA not VB6.

My attempt starts at the "Dim wb&" line and goes to the
"Set c = General.xlWkbS.End(xlDown)" line.

Can someone point me in the right direction?

Thanks in advance,
- Brad



Code:
Public Sub Survey()

Dim a As Range, b As Range, c As Range 'These ranges are for searching the Workbooks
Dim LastRow&, LastRow2&, LastRow3&, LastRow4& 'Var's used for End of Row calculations
Dim aa& 'Var used as a Survey request counter
'
General.xlSur.Columns("A:A").ColumnWidth = 100 'Resize the A column to Width 100
LastRow2& = General.xlSurv.Range("A65536").End(xlUp).Row 'Find the last used cell in the survey data
LastRow3& = General.xlFD.Range("B65536").End(xlUp).Row 'Find the last used cell in the Players Fleet & Defence worksheet
LastRow4& = General.xlGM.Range("A65536").End(xlUp).Row 'Finds the last A Column row number in the GM worksheet
'
For Each a In General.xlFD.Range("B1:B" & LastRow3&) 'Selects the used range in the player worksheet
If a.Value Like "Survey ###x###/*" Or a.Value Like "survey ###x###/*" Then 'Looks for the Survey request
' Set b = General.xlSurv.Range("A1:A" & LastRow2&).Find(Right(a.Value, 10)) 'Sets the range to the used A column and searches for the bb string var
'
Dim wb& '
For wb& = 1 To General.xlWkbS.Worksheets.Count '
Sheets(wb&).Select '
Set b = General.xlWkbS.UsedRange.Find(Right(a.Value, 10)) '
Set c = General.xlWkbS.End(xlDown) 'Lets do the search
General.xlSurv.Range(b.Address, c.Address).Copy 'Copy the Survey data from the Survey Worksheet
General.xlSur.Activate 'Switch to the Surveys worksheet
LastRow& = General.xlSur.Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).Row 'Find the last used cell in the Players Survey Worksheet
General.xlSur.Range("A" & LastRow&).Select 'Select the ff$ range (the survey request)
General.xlSur.Paste 'Paste the survey into the Players Worksheet
Next wb& '
'
aa& = aa& + 1 'Count how many Survey's were procesed
End If 'End of range
Next a 'Wash and Repeat
General.xlGM.Range("A" & LastRow4& + 2).Value = "Starting Survey Requests: Processed " & aa& & " Surveys"
General.xlGM.Range("A" & LastRow4& + 2).Font.ColorIndex = 10 'Make it green
'
End Sub

Concatenate Across Multiple Worksheets
Hello,

My function doesn't seem to work. I need to concatenate the text strings from a range of cells from all worksheets in the workbook. I can't figure out what I'm doing wrong here.


Code:
Function ConcatenateAllWS(InputRange As Range) As String

Dim ws As Worksheet, Temp1 As String, Temp2 As String
Application.Volatile True
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then
Temp1 = Application.WorksheetFunction.Concatenate(ws.Range(InputRange.Address))
End If
Temp2 = Application.WorksheetFunction.Concatenate(Temp2, Temp1)
Next ws
Set ws = Nothing
ConcatenateAllWS = Temp2
End Function
Also is there any way to add a comma in between each cell's text?

Thanks.

Help - Creating Multiple Worksheets
I am writing a macro to create multiple worksheets based on a column of values. While I can get the macro to create and name a single worksheet based on a specified cell I can't figure out how to do it iteratively down the complete list. I'm not sure if I'm suppose to use a Do...Loop with Offset ranges or what.
Any suggestions would be much appreciated. Thanks, Eric.

On Working With Multiple Worksheets.
I'm currently having issues with working with multiple spreadsheets. We've got a situation where our body of data has thousands of columns, so it has to be split across multiple worksheets; however, for many operations, it's necessary that it all be treated *as if* it were the same worksheet. For example, if a particular row on Sheet1 is hidden or deleted, the corresponding rows on Sheet2, Sheet 3, ... , Sheet N have to be hidden or deleted as well. Other functionality that's required includes performing sorts that affect multiple worksheets based upon sort keys that appear on only one of the worksheets, or even sorting multiple worksheets based upon multiple keys that each appear on a *different* worksheet. It's... getting pretty complicated, to say the least.

I'm afraid I don't have a specific question here, as I don't really know where to begin; I'm more looking for recommended reading on the subject. Does anyone have any resources they'd recommend? Books? Websites? Anything?

- David Prokopetz.

Search Through Multiple Worksheets
Hello people,

i'm not a programmer and i know only a few things about VB. My problem is this:

i wanted to create a macro where i would be able to perform the search action in all worksheets of an excel workbook. So, i created a "Find All" menu item and when it is clicked a search-like form is opened. The search is performed like this:

Private Sub FindCB_Click() 'This is the Find Next button in my form

Dim sh As Worksheet
Dim rng As Range, firstAddress As String


For Each sh In ThisWorkbook.Worksheets

Set rng = sh.Cells.Find(What:="*" & FindAllForm.TextBox1.Text & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Not rng Is Nothing Then

firstAddress = rng.Address

Do

If Not rng Is Nothing Then

Application.Goto rng, True


End If

Set rng = sh.Cells.FindNext(rng)

Loop Until (rng.Address = firstAddress)


End If

Next



So, when the code is executed it goes directly into the last string it finds, skipping all the intermediate strings. How can i make it stop in every string it finds? And then by clicking the button to go to the next one? Like the classic Find option in excel?

Thank you very much in advance!

Sum Same Cell In Multiple Worksheets
I need to sum the same cell in multiple worksheets into a total cell. The number of worksheets changes and I need to start the sum at the third sheet. So, sum cellXX in worksheet 3 through last worksheet. Ideas?

Arrays Made From Multiple Worksheets
Hello

I have been given significant assistance on this site before and am hoping for some more.

My problem is this I need to search 1 column in several different workbooks. I can navigate all the workbooks programatically just fine. I was hoping to use an array to speed things up. There is an enormous amount of inforamtion to sift through. I can build an array by going cell by cell though the column I need on each workbook, but this isn't much faster that how i am currently doing the process. Is there a way I can add to an array in big chunks. I can do the following on one worksheet, but can i add to it?

vb
dim INFO() as Variant, rng as range

set rng = range(cells(1,9), cells(Activeworksheet.usedrange.rows.count,9)
Info = rng


Can I continue the process on all my desired worksheets to make one huge array? If so how.


One Million thankyous

Deleting Comments On Multiple Worksheets
Hi Everybody

I have 3 worksheets in a workbook. Some cells in one or more worksheets may have comments attached to them

Is there a way to delete the comments from all the cells in all the worksheets in the workbook using a single command rather than scanning all the used cells on all the worksheets and then delete them as we find them.

I am looking for something like so :-


Code:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).select
selection.cells.clearcontents
or


Code:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).select
selection.cells.clearComments
or


Code:
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).select
selection.cells.interior.colorindex = xlNone
but don't seem to work.

Where am I going wrong?


Best regards


Deepak Agarwal

Hinding Rows In Multiple Worksheets
In order to hide a selection of rows in multiple (3) worksheets I used the following macro. It does not seem to function properly, and I can not figure out why. The only sheet in which the rows are properly hidden is the "MT" sheet, the hinding does not function in the two remaining sheets.

The rows which are selected for hiding, contain the word "obsolete". I know it ancient but it is for excel97.

Hopefully somebody has a suggestion.

Thanx!

Kraven

Sub Obsolete()
'
' Obsolete Macro
' Hiding obsolete studies in all sheets
'

Application.ScreenUpdating = False

Sheets("General Information").Select
Range("A3").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets("General Information").Select
Dim r As Range: Set r = Range("z2:z200")

r.EntireColumn.Insert
r.Offset(0, -1).FormulaR1C1 = "=RC[1]=""Obsolete"""
r.Rows().Hidden = r.Offset(0, -1).Value
r.Offset(0, -1).Delete


Sheets("Study Information").Select
Range("A3").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets("Study Information").Select
Dim s As Range: Set s = Range("z2:z200")

s.EntireColumn.Insert
s.Offset(0, -1).FormulaR1C1 = "=RC[1]=""Obsolete"""
s.Rows().Hidden = s.Offset(0, -1).Value
s.Offset(0, -1).Delete


Sheets("MT").Select
Range("A3").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets("MT").Select
Dim t As Range: Set t = Range("z2:z200")

t.EntireColumn.Insert
t.Offset(0, -1).FormulaR1C1 = "=RC[1]=""Obsolete"""
t.Rows().Hidden = t.Offset(0, -1).Value
t.Offset(0, -1).Delete

Application.ScreenUpdating = True


End Sub

Take One Worksheet &amp; Create Multiple Worksheets
Hello Everyone,

I have a worksheet that contains customer information (i.e. sales, returns, rank, etc.), where each customer will appear on four separate rows, one for each sales quarter. This format comes off of our mainframe, so adapting the original file is out of the question.

I want to take each of these customers (and all four rows of data) and create a new worksheet for EACH customer, with their information, along with naming the worksheet with the customer code (a 6-digit number).

Here's an example layout of the original:

A1 B1 C1 D1
Code Qtr Sales Returns
123456 1 50 -10
123456 2 60 -11
123456 3 30 -3
123456 4 20 -5
555111 1 55 -1
555111 2 23 -12

etc.

Can someone offer some guidance as to how to handle the VBA to search through Column A until a change in the Customer Code, create a new worksheet with that Customer Code, along with the four rows of data, then progress on to create a new worksheet for the next Customer Code in Column A?

I know some VBA, so just some pointers on where/how to start would be greatly appreciated.

Thanks,

Scarecrow

Opening Multiple .txt Files On Different Worksheets
Hi

I'm Using Excel 97.

I wonder if someone can help me or point me in the right direction.

I have a number of tab delimited .txt files in different directories. I need to open each one within the same workbook but with each on a different worksheet.

I found a thread that does the same as i need to do but i am not sure what should go instead of the Data_From_File part ? Also very annoyingly my help files do not seem to work so i cannot even check the correct syntax in there.

http://www.xtremevbtalk.com/show...xt+files+excel

any help appreciated

thanks
Paul

Searching Through Multiple Worksheets Within A Workbook
Hello all and Happy Memorial day to you all

I'm trying to figure out how I can search through multiple woksheets within my workbook.

I have the user updating their input through a userform(2) which then searchs column b and c for certain criteria.

That part works great.

I just cant do the multiple worksheet search, can anybody assist me

here is my code so far

Code:

Option Explicit

Private Sub cmdRJFH_Click()
Dim iMonth As Integer
Dim jobname As String
Dim SERVICEDESK As Integer
Dim temp As Range
Dim temp2 As Range
Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
Dim ws As Worksheet
Set ws = Worksheets("MAY2008")
  
 
'check for a job name!
If Trim(Me.txtRLJname.Value) = "" Then
Me.txtRLJname.SetFocus
  MsgBox "Please enter the Job name that has been requested to be taken off hold!"
  Exit Sub
End If

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'CHECK'S TO SEE IF JOB AND SERVICE DESK NUMBER ARE IN THE MONTH OF APRIL

Dim strVal1 As String
Dim strVal2 As String
Dim irow As Long
Dim m As Long


 'Get values of text boxes
strVal1 = Me.txtRLJname
strVal2 = Me.TextBox1

' Last used row
m = ws.Range("B" & Rows.Count).End(xlUp).Row
 
' Loop through the rows

For irow = 1 To m
 'Do we have a match?
    If ws.Range("B" & irow) = strVal1 And ws.Range("C" & irow) = strVal2 Then
   
   'If so, get out of loop
        Exit For
        End If
Next irow
  
  If ws.Range("B" & irow) = strVal1 And ws.Range("C" & irow) = strVal2 Then
       If Me.txtNop2.Value = "" Then
      Me.txtNop2.SetFocus
    MsgBox "Please enter the Requestor's Name!"
    End If
   ws.Cells(irow, 8).Value = Me.txtNop2.Value
  ws.Cells(irow, 9).Value = Date
 ws.Cells(irow, 10).Value = Time
ws.Cells(irow, 11).Value = Me.txtCOMments.Value
'clear the data
Me.txtRLJname.Value = ""
Me.txtNop2.Value = ""
Me.txtCOMments.Value = ""
Me.TextBox1.Value = ""
Me.txtRLJname.SetFocus
End If
If ws.Range("B" & irow) <> strVal1 And ws.Range("C" & irow) <> strVal2 Then GoTo ERRHNDLR
'************************************************************************************
ERRHNDLR:
    MsgBox "Hobname and or Service desk ticket are not within the month of april"
'clear the data
Me.txtRLJname.Value = ""
Me.txtNop2.Value = ""
Me.txtCOMments.Value = ""
Me.TextBox1.Value = ""
Me.txtRLJname.SetFocus

End Sub


thank you

Referencing Multiple Projects
If i have a project can i refer to all objects within another project without adding the forms/modules one at a time??

Printing Multiple Worksheets With Same Printer Settings
Hi everybody! I am banging my head against the wall with some printing issues. The scenario is that I have a collection of worksheets within one workbook. Each of these worksheets is unique in that they are different lengths and have different information in the header and footer.

My goal is to give the user the print dialog box so they can specify printer settings (duplex, black and white, etc) and then be able to print all the worksheets with the same settings. If you choose "Entire Workbook" in the print dialog box it only prints the first worksheet with the settings the user chose (because Excel dispatches each as a separate print job).

I tried two possible workarounds, neither of which I got fully working. The first was to make a custom box that allowed the user to tell me their print settings, capture those and then loop through and automate the printing of each worksheet individually. The problem is that I can't figure out how to change the printer settings with VBA (they don't get recorded in a macro, I've tried that). Second scenario is to copy and paste all the worksheets into one big worksheet and insert HPageBreaks between them. This worked wonderfully for formatting; all the printer settings get applied to everything. But here the header (worksheet name) and footer (page numbers) information is not maintained.
If anyone has any ideas on how I can fix my two scenarios, or a new one that I haven't thought of I would LOVE to hear about it. My head hurts from thinking about this Thanks in advance!

Apply Vb Macro To Ranges In Multiple Worksheets
I have the following macro:


Code:Sub PSAPerfBreak2Macro()
'
' PSAPerfBreak2Macro Macro
' Macro recorded 9/11/2003 by VJ Occhino
'
Dim rval As String
Dim nval As String
Dim rng1 As String
Dim rng2 As String
Dim finr As String

   nval = ActiveCell.Value
   rval = InputBox("Enter text to replace:")
   finr = "g1:g500,n1:n500"
    Range(finr).Select
   If rval = "" Then
    MsgBox ("Canceled")
   Else
   Selection.Replace What:=rval, Replacement:=nval, LookAt:=xlPart, _
   SearchOrder:=xlByColumns, MatchCase:=False
   End If
'
End Sub


Currently, it updates the two ranges on the ACTIVE worksheet that I define in my variable "Finr". However, I would like the same "find & replace" macro to work in another range in another worksheet in the same workbook. Is there a way to define my finr as not just"g1:g500,n1:n500" in the current worksheet but a range in another worksheet as well?

Thanks in advance!!!

Referencing To List Box With Multiple Fields
Hello, does anyone know how to reference to a certain part within a list box? I have populated a list box such as:


Code:
Do Until adoGuy.EOF = True
lstAll.AddItem adoGuy("STATUS") & " Tag: " & adoGuy("TAGNUMBER") & " Name: " & adoGuy("NAME") & " Badge: " & adoGuy("BADGE")

adoGuy.MoveNext
Loop

So now, when a user double-clicks on a line within the list box, how can I reference adoGuy("Name") to pull from the database?

Any help is appreciated

Thanks,

Dan

Referencing Multiple Instances Of Forms
Hi,

Say I have a form (A). This form has a button which creates a new instance of form (B) every time it is pressed, using
Code:Dim blah As New FormB
blah.show

Then I have another form (C). I want a button on form C to be able to change a field on an existing instance of form B. How can I do that? I tried to create something like an Object pointer to a particular instance of the form, but that didn't work. As I understand it, normally you'd just be able to reference the field as FormB.Text or whatever, but a specific instance created in this way can't be referenced like that.

Thanks!

Updating Multiple Workbook/worksheets/cells W/new Formulas
I would like to update update multiple workbooks/worksheets/cells with new formulas to replace existing formulas. I will have a spreadsheet with a path, workbook/file name, worksheet name, cell name/address, new formula and old formula. I would like to loop through all of the files/workbooks/worksheets and change those cell formulas that have new formulas. Any help would be appreciated. I've done quite a bit of VBA work in Excel and could probably figure this out, but if someone has done this already I would be very grateful.

Thanks

bdean60

Referencing A Form That Can Be Open Multiple Times
My co-worker has code to open a form like this...


Dim f As frmName
Set f = New frmName
f.Show


This code can be called multiple times, giving multiple instances of loaded frmName's on the screen. However, sometimes a different form pops up, which needs to reference the latest instance of a loaded frmName. How can this be done?

My only idea for her was to make a global "f" array of frmName that can grow, with a global form index counter, which she could then reference. However, I don't know if that's the best idea, and she is wondering if that will eat more memory.

I didn't know the answer to that. Any ideas or comments?

I Need To Create A Macro / Button That Would SEARCH Multiple Worksheets And REPLACE
Ok,

This is day one with trying the VB / Macro side of Excel, so please be patient.

I have a spreadsheet which contains approximately 7 Worksheets. I would like to be able to create a button (if possible) or macro that has a search built in(the search is predefined in the button) . Once clicked on, it would ask me for the replacement Data. It would search for the predefined information and change it to what is typed in when clicking the button or executing the macro.

So, my spread sheet is actually a template. Every time I open it, I am creating collection of data on a specific computer type, (say.. IBM ThinkPad). So throughout my spread sheet at the title blocks and graphs I placed text that says #MODELHERE (So I know this is where I want the model name to go). now I open the spreadsheet I want to press a button that will prompt me for the "MODEL NAME", I type in IBM THINKPAD. The code then goes out and searches for #MODELHERE and replaces it with IBM THINKPAD. I would like to do this not only for Model, but also for #NIC and #VER (for Network interface card and the other for NIC driver version).

So... now that I have bored you all.. it this possible?
Thanks so much!
Rich



Edited by - RBorgia on 11/7/2003 10:18:43 AM

Excel Add-ins - Making With Modules Only - Calling From Excel Worksheets Via Vba
I ave 2 questions;

Question ONe

How do I make a add-ins for excel with modules only. Excel says that I must have atealst one worksheet - can I make a add-ins which are not COM dlls without worksheets, ie simply se existing excel modules ?

Question Two

How do I can a function or sub procedure from a module contained in my add-in ?

thanks

adam

Program For Cross Database..cross Platform
Hi Folks,

I would like to know how to develop a program in Visual Basic which as access cross database at runtime. this program has a user interface..where user selects the database he wants...eg: oracle, sql server, sybase..etc..then he selects database..then tables..then fields...
all these information will be decided at runtime..

NEXT IS HOW CAN THIS PROGRAM BE ABLE TO RUN ON ALL PLAFORMS...

PLZ...LET ME KNOW....HOW TO MAKE IT RUN CROSS PLATFORM

THANKING..

REGARDS
SEKHAR

Excel Close Button ( Cross 'X' ) ???
Does anybody know how to disable the Excel Close, Maximize and Minimize Buttons ??? I need this to happen in my project which i am developing on Excel VBA 2000. And this should be code driven.

Appreciate all help. Thanks!

Excel Close Button ( Cross 'X' ) ???
Does anybody know how to disable the Excel Close, Maximize and Minimize Buttons ??? I need this to happen in my project which i am developing on Excel VBA 2000. And this should be code driven.

Appreciate all help. Thanks!

Writing Data In Multiple Sheet In Excel Using VB6.0
In VB6.0 i am writing data from SQL Server to an excel file. I ve an excel file template in which i have to write the data obtained from the SQL Server. In this tempalte file there are three sheets.

I was able to write data successfully in the first sheet. Now wen i start writting data in the another sheet. The first sheet is affected. Is there any way to save the sheet1 and add data to sheet2 alone.. I mean change the activesheet to the sheet2 or something like that.

Please help me out here.


Thank you,


With regards,
ram

Writing Data On Multiple Forms To Excel
Can anyone advise or provide any sample code on how to write user entered data tat is available on seperate forms into records in an excel spreadsheet.

Copy Excel Worksheets To New Excel Document
As the title suggests, I am trying to get one worksheet from an excel sheet and copy it into a new excel document.

I am a complete beginner with VB but have a good idea about using COM object and opening and entering data into excel, word etc. But I've never copied a who worksheet. Is it possible and where would one start?


is there a .copysheet or some kind of function I could use?

Any help appreciated.

Mark.

Cross-Process Data Extraction #3
=====================================


Warning any VB code that manipulates pointers
must be used with care!! Mistakes can, and
usually will, invoke the wrath of KABOOM

1. Always check your SendMessage interfaces with an equivalent control in your OWN program to ensure you have the right method, before making it cross-process compatible.

2. Make sure every buffer is big enough! Otherwise KABOOM is always nearby.

3. If you don't fully understand the above, you probably shouldn't even think about adapting this code.

=====================================



Cross-Process Data Extraction #3

This is my 3rd demo program on this subject.

I've posted code previously that shows how to copy data from ListView's and TreeView's in other applications.

This demo shows how to use the same technique to get information about all the buttons in a ToolBar in another process.

NB This demo has a slightly different service module to that used in the previous demo's. I think the new version makes the front-end coding much simpler.

The module drMemory makes the following functions available:

drMemoryAlloc Allocates a cross-process buffer
drMemoryWrite Fill in data to send
drMemoryRead Get data returned
drMemoryFree Releases a buffer

The new Alloc function takes a window handle as parameter and returns a buffer address that can be passed (by value!) to the target window as the SendMessage lParam argument

Example: TB_GETITEMRECT message

Code:
xpWindow& = <<target control window handle>>
xpBuffer& = drMemoryAlloc(xpWindow, len(bRect))

SendMessage xpWindow, TB_GETITEMRECT, bIndex, ByVal xpBuffer
drMemoryRead xpBuffer, VarPtr(bRect), len(bRect)

drMemoryFree xpBuffer


About the Demo

A question was posed in the forum about whether it was possible for a VB application that has an icon in the Taskbar status area (SysTray)
to know the exact screen co-ordinates of its icon at any given time.

The position is variable - other icons can come and go, the taskbar can be reconfigured, etc

The SysTray is a ToolBar, and it's easy to find a button's position if you know its ID.

Finding the ID, and getting the icon position from there, requires the use of TB_GETBUTTON and TB_GETITEMRECT messages, and these need cross-process buffering in order to work.


The demo program operates as follows:

1. On startup it appears as an icon in the SysTray.

2. Click on the icon and it find the SysTray window, read all its buttons, and display them in a ListView.

3. If you press the SPACEBAR, it will move the cursor to its own icon, thus making the Tooltip popup

4. Even if you force the icon position to change, by adding/removing other SysTray apps, it should always move to the right place!

5. The Refresh button simply redraws/updates the ListView


Note: The program works as planned for NT, 2000, XP platforms. On Win 95/98, the SysTray (for some unknown reason) is NOT a ToolBar. But the QuickLaunch icon tray is, so the program will list its buttons instead! So you can be sure the ToolBar scanning code really does working on Win 95/98!

Cross Join With Data I Dont Have
i hope someone can help me with my problem, I have 4 ms sql tables: Ins, Profile, Contacts, and Notes. They all have a column named CKey in common, the problem is in the Profile table the CKey column is 5 characters (could become 6 later) the first two arent used for this particular situation:
CODE
Table Profile:
-------------------------------
|CKey | Name | Address | City |
-------------------------------
|60123| Todd | 1 Main  | Place|
|60124| John | 2 Main  | Place|
|60125| Fred | 3 Main  | Place|
-------------------------------

Table Contacts:
---------------------------------
|CKey |CName |  Phone  | Fax    |
---------------------------------
| 123 | Joe  | 5551133 | 5551132|
| 125 | Tim  | 5552111 | 5552145|
| 124 | Jack | 5553114 | 5553124|
| 123 | Jill | 5551139 | 5551136|
---------------------------------

Table Notes:
----------------------------------
|CKey | Note                     |
----------------------------------
| 125 | a note                   |
| 123 | another note             |
| 125 | yet another one          |
----------------------------------

Table Ins:
-----------------------
| CKey| Amt | Exp     |
-----------------------
| 125 | 500 | 07/2005 |
| 124 | 600 | 07/2006 |
| 123 | 950 | 07/2007 |
-----------------------

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