copy sheet again again

A

Alvin Hansen

I have read some off whar has been writing here in this forum
and find theis code from
http://www.rondebruin.nl/ado.htm

the code I use is
Sub GetData_Example1()
'This Copy the range to the ActiveCell
GetData ThisWorkbook.Path & "\test.xls", "Sheet1", "A1:C5", ActiveCell,
False

'This Copy the range to the Sheet/Range you want
'GetData ThisWorkbook.Path & "\test.xls", "Sheet1", "A1:C5",
Sheets("Sheet2").Range("A1"), False

End Sub

but get an error
its stop with Getdata and the error is:

compile error
Sub or function not defined.
Can some one help

Alvin
 
R

Ron de Bruin

Hi Alvin

Have you copy the functions also??

Download the example Zip file on that page to test it
 
A

Alvin Hansen

Hi Ron
Thank you I can see the light
I downloadet example And its working in the file
ADOTEST
but if i copy the module into another file to use it here
I get this error

Compile error
Userdefined Type
not Defined

It stop in the line: Dim rsdata as adodb.recordset

What do i do wrong -- its working in the ADOTEST file I don't uderstand this

Alvin



"Ron de Bruin" skrev:
 
A

Alvin Hansen

Sorry
I have found out what i did wrong, well I can just say that its a good thing
to read all before trying anything. It was just the reference to the
Microsoft ActiveX Data Objects 2.5 library I didn't have

Now I have this macro there can copy the sheet from a closed workbook, how
can i get this macro to run every time i open the workbook

best Regards and many many thanks for the help

I give a beer if you ever come to Denamrk

Alvin


"Ron de Bruin" skrev:
 
R

Ron de Bruin

Hi Alvin

Good morning

Sub Auto_open()
'macro name or code
End Sub

You must copy this macro in a normal module
The macro will not run if you open the file with a macro

Or use a event

Private Sub Workbook_Open()
'macro name or code
End Sub

Right click on the Excel icon next to File in the menubar
And choose View code

You are now in the Thisworkbook module
Paste the Event in this place
Alt-Q to go back to Excel
Save and close the file

Read Chip Pearson's site about Events
http://www.cpearson.com/excel/events.htm
 
A

Alvin Hansen

Hallo ron
Well I have done what you write to me
If i run it manuel from Run in the menu bar is working
But not if i open the file
I have this:
In Thisworkbook
Left Dropdownmenu=workbook
Right Dropdownmenu = Open
And in this window i have

Private Sub Workbook_Open()
GetData_Example1
End Sub

I did whar you write right klik on the exel icon paste the event
And as i write when i use Run its working
Best regards
Alvin
Sorry i'm a little slow in this



"Ron de Bruin" skrev:
 
A

Alvin Hansen

Hi ron please see here:

This working:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
GetData_Example1
End Sub

This working:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
GetData_Example1
End Sub

Thsi not working:
Private Sub Workbook_Open()
GetData_Example1
End Sub


Best regards alvin

"Ron de Bruin" skrev:
 
R

Ron de Bruin

It is Working for me

Use the example workbook on my site and copy this

Private Sub Workbook_Open()
GetData_Example1
End Sub

In the Thisworkbook module

Save/Close and reopen the file
 
A

Alvin Hansen

Hi Ron
I have find out why it dosn't work
I open excel first and then open file
If i just open file whitout open excel first its working

Many thanks for your help
I beginng to understand something

Best regards alvin


"Ron de Bruin" skrev:
 
A

Alvin Hansen

Well you are right it dosn't work.

But now I have make a new workbook copy all from the other workbook and now
its working Why I don't know and well it dosn't matter for now its working in
the new workbook

So thanks to all for the help

Alvin


"Tom Ogilvy" skrev:
 
Top