Opening, manipulate and close worksheets from a single worksheet

[newbie1983]

Any ideas as to how i could open a worksheet (.xls) so that i can manipulate it , and then close and then open the next one? Ideally from a worksheet interface (i.e a GO! button which will then trigger the program)

Kind Regards

Hinesh

 

[xlbo]

well, in VBA
workbooks(Workbookpath & workbookname).open

will open a workbook but your question is very vague

What do you need to happen ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[newbie1983]

well i have a worksheet which contains a macro for manipulating data on another worksheet. What i want to do is that when the macro runs, it opens up a worksheet which resides on disk (c drive) and manipulates the data and records the results on the original worksheet.
yeh i have tried the workbooks.open statement which works correctly however if i want to switch between workbooks how can this be achieved?
sorry for the wordy question its quite hard to explain

kind regards
hinesh

 

[xlbo]

You can refer to the workbook in which the code resides by using
Thisworkbook
This will ALWAYS refer to the workbook which holds the code
when you open a workbook, it automatically beomes the ACTIVE workbook so you can use
Activeworkbook

I usually set them as variables if I am working between 2 workbooks eg

Dim tWB as workbook, mWB as workbook
set tWB = thisworkbook
workbooks(workbookpath & workbookname).open
set mWB = activeworkbook

then you can refer to the 2 workbooks like

With tWB
.sheets("Sheet1&quot.range("A1&quot.value = "This is the source workbook"
end with

with mWB
.sheets("Sheet1&quot.range("A1&quot.value = "This is the opened workbook"
end with

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[newbie1983]

Geoff, ive incorporated ur code and seems to have made a difference but when i get to line 22 i get a "run-time error '430' class doesnt support automation" ne ideas?

1ublic ThisBook As New Workbook
2ublic myBook As New Workbook
3ublic mySheet As New Worksheet
4:
5ublic Sub checkForRepeats()
6:
7: Set ThisBook = ThisWorkbook
8: 'Set mySheet = ThisBook.ActiveSheet
9: Dim c, r, c2, r2, Count, Injections As Integer, 10:currentData, compareData As String
11: Dim added As Boolean
12:
13: '15/09/03
14: Workbooks.Open FileName:="\\ukz447\hpp31453\excelproj\channel Info.xls"
15: Set myBook = ActiveWorkbook
'

16: Injections = 0

17: c = 4
18: r2 = 3

19: Count = 0
20: added = False
21: For r = 1 To 115922:
22: currentData = mySheet.Cells(r, c).Value
23: compareData = mySheet.Cells(r + 1, c).Value

 

[xlbo]

not sure why you'd get that error but
currentData = mySheet.Cells(r, c).Value
shouldn't work as currentdata is dimmed as STRING
Use TEXT with strings

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244