• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. I

    Error during inserting a new row in Data Table

    Hello There, I have the below Sub which inserts a new row at the end of the table. In the sub, there is On Error Resume Next. When this line of codeis enabled all goes okay But when I disable On Error Resume Next, it pops up with an error: 'Runtime error '438' Object doesn't support this...
  2. I

    Worksheet: Renaming ws to it's default names & delete unwanted worksheet

    Thank you Marc
  3. I

    How can I close the workbook using a button and via the normal way

    Hello there, I am trying to save and close a workbook using a button and not the normal way. It doesn't seem to do anything. Private Sub Workbook_BeforeClose(Cancel As Boolean) Sub CloseWB () With ActiveWorkbook .....some code .Close SaveChanges:=True End With End Sub...
  4. I

    Reading & writing data to a closed workbook using ADO

    Hello There, I have a small VBA application which uses ADO for selecting records from a closed workbook and displaying it in the workbook. I was wondering using the same method is it possible to write data as well to a closed workbook. Look forward to hearing from you. Regards, Don
  5. I

    Hello, How I can extract unique data from table based on a criteria from another sheet and put the result on the same sheet where the criteria

    Hello Rahaf, See if this helps you: https://chandoo.org/forum/threads/conditionally-extract-datatable-from-one-workbook-to-another-using-ado-sql.41488/#post-247529 It is using the DAO method (SELECT statement). In Sub Sub RS2WS, it rearranges the columns. This is based on external workbook...
  6. I

    Worksheet: Renaming ws to it's default names & delete unwanted worksheet

    Hello There, I have a workbook with 3 worksheets. Each worksheet has it's own defined User Name (eg. Invoices, Journals, Setup, etc.) I would like to do the following in a single Sub: 1. Define the Worksheet Code and the Worksheet Name in an Array ....(Sheet1 = Invoices, Sheet8 = Journals...
  7. I

    Extract data from website

    Thank you Stevie for coming back. If the user runs this code multiple times, then there are multiple IE proccesses running (as seen in task manager). How can the specific IE process opened for this purpose be removed? Regards, Don
  8. I

    Extract data from website

    Thank you Marc for taking the time to test
  9. I

    Data Table inserting a new blank row and write values

    Hi Hui, Thank you for your solution and also for your recommendations about variable declaration. It is an eye opening. Now I know why there is more memory usgae. I will surely change it in the workbook. :awesome::) Regards, Don
  10. I

    Data Table inserting a new blank row and write values

    Thank you Chihiro :awesome:
  11. I

    Data Table inserting a new blank row and write values

    Hello There, I have a VBA code which inserts a new row at the end of a data table. It inserts a blank new row at the end of the table but it overwrites the previous row values. I would like it to write the values to the newly inserted blank row which it created Please find attached a sample...
  12. I

    Conditionally extract datatable from one workbook to another using ADO SQL

    Thank you Chihiro.
  13. I

    Extract data from website

    Hi Marc, Thank you for your reply. A new container number for your reference MEDU2834999 Look forward to hear from you. Regards, Don
  14. I

    Extract data from website

    Hi Marc, Thank you for your response. I didn't understood your comment: "....but the issue is the request's URL … " regards, Don
  15. I

    Extract data from website

    Thank you for the reference links. I will go through the same. Regards, Don
  16. I

    Conditionally extract datatable from one workbook to another using ADO SQL

    Thank you Chihiro. On Error and at the end, I am setting the connection and recordset object variables to Nothing. This should work, right? Set cn = Nothing Set rs = Nothing I will use the ADO method. Regards, Don
  17. I

    Conditionally extract datatable from one workbook to another using ADO SQL

    I did thought about making a file copy (with ADO method) and after the job is complete delete the file copy. Would the problem of memory leakage (using ADO) still be there? Regards, Don
  18. I

    Extract data from website

    Thank you Chihiro for your recommendation. How to recognise, the code uses an API? Regards, Don
  19. I

    Conditionally extract datatable from one workbook to another using ADO SQL

    Thank you Chihiro. That made me search further for the solution. I came across a solution using DAO (which works when the Master File is open). Please let me know if the below use of DAO, resolves the memory leak you mentioned. Herewith, the below code for your reference...
  20. I

    Conditionally extract datatable from one workbook to another using ADO SQL

    Hi Chihiro, I added the objects to be cleared. When I keep the 'Master File.xlsx' open, I receive this error "No value given for one or more parameters" But when the 'Master File.xlsx' is closed, the records are fetched. in the actual situation the 'Master File.xlsx', is always open. How can...
  21. I

    Conditionally extract datatable from one workbook to another using ADO SQL

    Hi Chihiro, Yes you are right, I realized it a bit later. The first 2 files will always be open (MstWB and EntWB). For the last 2 workbooks, I thought of doing it this way: 1. If the number of rows returned by the SQL is greater than 1, then make a copy of "Report Template File.xlsx"...
  22. I

    Conditionally extract datatable from one workbook to another using ADO SQL

    Thank you Chihiro for your help. I will apply your solution and will update you tomorrow. Thanks again and regards, Don
  23. I

    Conditionally extract datatable from one workbook to another using ADO SQL

    Hi Chihiro, Thank you for very much for your response and appreciate your recommendation. In the actual master file all the worksheets contain Data Tables. There is only 1 data table in a worksheet. All the Data Table Headers Start from B3 and Data body range from B4. Getting away with the...
  24. I

    Conditionally extract datatable from one workbook to another using ADO SQL

    Hello There, I would like to use the ADODB.Connection (SQL) to fetch the data from other workbook. I have 3 excel workbooks as follows. : 1. Master File.xlsx This contains all Master Data Tables: Customer, Vendor, Currency and Transaction. The Customer, Vendor and Currency has 2...
  25. I

    Extract data from website

    Hi Stevie, Thank you for your solution. It did work as expected. However, I would like the results to be displayed in the worksheet without the browser being opened. I came across a solution which does a data extract without opening the browser. It uses XML, something like March L had...
Back
Top