Excel Automation

[aziz rasul]

I have the following code.

Private Sub Command0_Click()

Dim dbs As Database
Dim obxls As Object

Set dbs = CurrentDb
Set obxls = CreateObject("Excel.Application")

obxls.Visible = True
obxls.Workbooks.Open Filename:="D:\NTL\Phase II Data Comparison\Output Files\Data Comparisons", ReadOnly:=False

obxls.Sheets(1).Name = "NTL Data Comparisons"

Code to change individual cell values

obxls.Quit
Set obxls = Nothing

End Sub

The task is to change some of the values of the xls file and then save it automatically. However I am prompted to save and I have to od it manually. What code can I add at the end to do this. Also if I wanted to save the xls file to a different filename.

 

[Travis]

See Microsoft Knowledge base Article:ACC2000: Using Automation to Create and Manipulate an Excel Workbook - 210148

 

[durdle]

Hi Aziz rasul,

I used a little of you code because I wanted to know how to open an excel file. Hope you don't mind! The code works great but after 25 seconds passes I get the following message
'Activity.xls' is now available for editing.
Choose Read-Write to open it for editing.

Any Idea why this is happening?

Dim dbs As Database
Dim obxls As Object

Set dbs = CurrentDb
Set obxls = CreateObject("Excel.Application")

obxls.Visible = True
obxls.Workbooks.Open Filename:="C:\Activity"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblstjastats", "C:\ACTIVITY.xls", True

obxls.Quit
Set obxls = Nothing
________
marijuana dispensaries

 

[aziz rasul]

Try

obxls.Workbooks.Open Filename:="C:\Activity", ReadOnly:=False

if that doesn't work, try

obxls.Workbooks.Open Filename:="C:\Activity.xls", ReadOnly:=False

I have never come across the message that you get.

 

[bretch]

try:
obxls.Workbooks.saveas ("D:\NTL\Phase II Data Comparison\Output Files\Data Comparisons\" & filename ".xls")

to save file automatically..

 

[aziz rasul]

Still prompts me by asking me whether I want to overwrite the file.

I am going to try and detect whether the file of the chosen name exists. If so delete it before the command to save.

 

[Fuga]

Do you think you can use displayalerts false in excel?

Fuga.

 

[aziz rasul]

Tried it and guess what, it WORKED. Many thanks.

 

[durdle]

Hi aziz rasul,

I tried both of your options and they didn't work. Basically all I want to do is after the data has been copied in the access, I want to close the excel file.

durdle
________
wholesale vaporizer

 

[aziz rasul]

Can you post me the code that you've got so far.

 

[aziz rasul]

I can change the font size\color of an individual cell, like so: -

xls.Cells(5, 8).Font.Size = 13.5
xls.Cells(5, 8).Font.Color = 255

How can I do the same thing but change the cell shading?

 

[Steve5]

Output Excel with Date in file name

Below I posted one of my favorite codes.
It places a time stamp on the excel file name so that you cannot overwrite a previous file you may have sent - great for history and archiving.

Also,
aziz look at the setwarnings code before an after. this will stop the prompts.



DoCmd.SetWarnings False
'DoCmd.OutputTo acOutputReport, "your report name", acFormatXLS, "C: your folder location here\your report name" & Format(Now, "dd-mmm hh.nn.ss") & ".xls"

DoCmd.SetWarnings True

 

[stefanrobin]

I have the following code.

Private Sub Command0_Click()

Dim dbs As Database
Dim obxls As Object

Set dbs = CurrentDb
Set obxls = CreateObject("Excel.Application")

obxls.Visible = True
obxls.Workbooks.Open Filename:="D:\NTL\Phase II Data Comparison\Output Files\Data Comparisons", ReadOnly:=False

obxls.Sheets(1).Name = "NTL Data Comparisons"

Code to change individual cell values

obxls.Quit
Set obxls = Nothing

End Sub

The task is to change some of the values of the xls file and then save it automatically. However I am prompted to save and I have to od it manually. What code can I add at the end to do this. Also if I wanted to save the xls file to a different filename.

Hi,

I have a workflow where at some point I have a loop (Group loop) working only with a part of an initial DB data.
In the middle of this loop, I would like to write in an automatic way data which I consider “mistakes” (I want to apply a filter on a FLAG calculated before) and these mistakes should be written into an excel sheet.
Every loop cycle the Excel writer should write the data in a new Excel sheet.

The cycles are 110 and it is in the middle of the workflow, so it is obvious that I can’t do it manually.
Is this possible to do it automatically?

 

[Pat Hartman]

@stefanrobin It is better to start a new thread than to revive one that is 20 years old. If there is something relevant in the old thread, include a link.

Your question is quite vague. I'm assuming you are doing something in Access and that is where your "loop" is. Please start a new thread with a little more context and be clear regarding whether you want to insert a new sheet so that everything from the "loop" ends up in the same workbook.

The answer to your question as written is "yes" but I don't think that will help you much. I don't know what the limit for sheets is for a workbook. Are you sure you want a new sheet rather than just a row in the same sheet?

 

[The_Doc_Man]

The answer to "sheets in a workbook" is "limited to memory."

Excel specifications and limits - Microsoft Support

In Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384 columns. In this article, find all workbook, worksheet, and feature specifications and limits.

support.microsoft.com

The cycles are 110 and it is in the middle of the workflow, so it is obvious that I can’t do it manually.

In the absence of a better explanation, it isn't obvious.