access vba 2

[JoanaSantos]

Hi,

I have this code:

Private Sub Command286_Click()

Dim newExcelApp As Excel.Application
Dim newWbk As Excel.Workbook
Dim newWkSheet As Excel.Worksheet

Set newExcelApp = Excel.Application

Set newWbk = newExcelApp.Workbooks.Add
Set newWkSheet = newWbk.Worksheets(1)

Dim i As Integer

For i = 1 To 2

newWbk.SaveAs "G:\OrcControlo\SCC\Joana\OLA" & i & ".xlsx"

Sheets(1).Name = "F01Incos0" & i
Sheets("Sheet2").Delete
Sheets("Sheet3").Delete

newWbk.Close

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"F01Incos0" & i, "G:\OrcControlo\SCC\Joana\OLA" & i & ".xlsx", True

Next i

End Sub


Each Time the code run this " newWbk.Close " my code block and i need to go to the file " HELLO .. " and open it. And Then appears the message " do you want to save the file ? "

when i click "yes " the code continues ..

can you help me ? i want to run the code without it stops.

 

[SkipVought]

Then remove the statement that closes the workbook!

But your logic has a big problem.

You ADD a workbook ONE TIME, yet you SAVE with the counter as part of the name in the loop.

You need to...
[pre]
For
Add workbook
Do stuff in workbook
SaveAs workbook
Close workbook

Next
[/pre]

 

[JoanaSantos]

Yes you are right. i miss that point! thanks

my code works, i add "newWbk.save" before close the workbook

thanks

 

[combo]

You may consider adding workbook with single sheet:
[tt]Set newWbk = newExcelApp.Workbooks.Add(xlWBATWorksheet)[/tt]
After tis two lines:
[tt]Sheets("Sheet2").Delete
Sheets("Sheet3").Delete[/tt]
can be removed, they can also cause problems if default workbook has less than three sheets.

combo

 

[JoanaSantos]

yeees i understand! thank yoou so much for your help