| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sheet again again
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy sheet again again
Hi Alvin
Have you copy the functions also?? Download the example Zip file on that page to test it -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy sheet again again
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: Hi Alvin Have you copy the functions also?? Download the example Zip file on that page to test it -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy sheet again again
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: Hi Alvin Have you copy the functions also?? Download the example Zip file on that page to test it -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy sheet again again
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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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: Hi Alvin Have you copy the functions also?? Download the example Zip file on that page to test it -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy sheet again again
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: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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: Hi Alvin Have you copy the functions also?? Download the example Zip file on that page to test it -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy sheet again again
Hi ron please see he
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: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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: Hi Alvin Have you copy the functions also?? Download the example Zip file on that page to test it -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy sheet again again
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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... Hi ron please see he 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: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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: Hi Alvin Have you copy the functions also?? Download the example Zip file on that page to test it -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy sheet again again
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: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... Hi ron please see he 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: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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: Hi Alvin Have you copy the functions also?? Download the example Zip file on that page to test it -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy sheet again again
Not sure what you understand, but workbook_open should work in either case.
-- Regards, Tom Ogilvy "Alvin Hansen" wrote in message ... 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: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... Hi ron please see he 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: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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: Hi Alvin Have you copy the functions also?? Download the example Zip file on that page to test it -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy sheet again again
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: Not sure what you understand, but workbook_open should work in either case. -- Regards, Tom Ogilvy "Alvin Hansen" wrote in message ... 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: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... Hi ron please see he 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: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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: Hi Alvin Have you copy the functions also?? Download the example Zip file on that page to test it -- Regards Ron de Bruin http://www.rondebruin.nl "Alvin Hansen" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
| Copy Sheet to new Sheet and clear cells on original sheets | Excel Discussion (Misc queries) | |||
| Auto Copy/autofill Text from sheet to sheet if meets criteria | Excel Discussion (Misc queries) | |||
| Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
| relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
![Alvin Hansen[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
