Script to change excel sheet

[Miles Runner]

Hello,
Here, I operate a transfer Access data to an excel file, I am trying to write a script to change a excel sheet.

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?

[Macarenas]

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.

[Miles Runner]

Hello,
Thank you for your reply.

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?

[Gunner 1]

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.

[chroma]

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.

[Raine]

Hello,
I do not know if it can be done, you can have the number of non-empty cell with count, just see below

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.

Worksheets.Add