Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 21-01-2010
Member
 
Join Date: Dec 2009
Posts: 292
Script to change excel sheet

Hello,
Here, I operate a transfer Access data to an excel file, I am trying to write a script to change a excel sheet.
Quote:
Sun XLS as object

set XLS = new excel.application
XLS.workbooks.add

XLS.ActiveWorkbook.ActiveSheet.range("A1").value = <DATA>
etc. ...
I have not figured out how to tell excel that if it happens at the end of the sheet (at line 65,000 approx) it passes on to sheet 2. Do you guys have any idea about it?
Reply With Quote
  #2  
Old 21-01-2010
Member
 
Join Date: May 2008
Posts: 4,829
Re: Script to change excel sheet

Hi,
I do not know if there is a better approach, but in your place I will seek to determine the number of lines upstream and cutting your data batch of 65,000. For my part, in such cases, I prefer to export to Excel (TransferSpreadSheet) then - if necessary - to open the file from Access to the format. I do not know really why are you doing it, that why do you want to write such program which is of no use.
Reply With Quote
  #3  
Old 21-01-2010
Member
 
Join Date: Dec 2009
Posts: 292
Re: Script to change excel sheet

Hello,
Thank you for your reply.
Quote:
I do not know really why are you doing it, that why do you want to write such program which is of no use.
It is just for curiosity and nothing else, I am doing it because I want to see if it is possible or not. And i strongly think this can be done, but I do not have the proper ideas to think about it. For cons, I get to cutting my data set of 65000, but I have not figured out how to tell it to leave on the second sheet of the workbook?
Reply With Quote
  #4  
Old 21-01-2010
Member
 
Join Date: Dec 2009
Posts: 296
Re: Script to change excel sheet

Hello,
Not possible with your program, You do not use the same excel file, the user transfers data in the file he wants by naming the excel as he wants. You have done everything from code vba to access because you are not leaving an existing Excel file. I do not think this can be done within excel, that I mean to say you are trying to program it in excel, but I think you will require a programming language.
Reply With Quote
  #5  
Old 21-01-2010
Member
 
Join Date: Apr 2008
Posts: 2,139
Re: Script to change excel sheet

Hello,
I recommend to make a simulation in Excel by turning on the macro recorder, just to quickly find a syntax to do what you want. Otherwise, I guess you have to move to the next sheet by browsing collections Worksheet. As said above if you know any programming language, it is possible to do. With the coding in excel you would not be able to achieve the results as you will in any other programming language.
Reply With Quote
  #6  
Old 21-01-2010
Member
 
Join Date: May 2008
Posts: 3,313
Re: Script to change excel sheet

Hello,
I do not know if it can be done, you can have the number of non-empty cell with count, just see below
Code:
j = Application.WorksheetFunction.Count(Range(R: R "))
As in this example it gives you the number of non-empty cell in column A if there is no empty cell in your column you have the number of store (your 6500)
and once reached your day you add a sheet and you work on it.
Quote:
Worksheets.Add
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Script to change excel sheet"
Thread Thread Starter Forum Replies Last Post
How to use Mail Merge from one Excel Sheet to another Excel Sheet Abélard MS Office Support 2 23-02-2012 02:59 PM
can't delete image in excel sheet Amitesh Windows Software 6 31-01-2012 12:25 AM
What is the use of time sheet in excel Chini mao MS Office Support 1 08-01-2012 04:35 PM
Send unlimited SMS from Your Excel Sheet Maq.H Tips & Tweaks 2 25-07-2009 09:09 PM
Fax an Excel sheet From a Computer Addis Tips & Tweaks 1 24-10-2008 01:03 PM


All times are GMT +5.5. The time now is 11:49 AM.