Q210684 - Need more info on suggested work around section

A

awong

I posted this on June 9th. But I haven't received any response yet. Could
anyone help?

Thanks a lot.


Original message below.

---------------------------------------------------------------------------
Hi all,



In KB article Q210684, it mentions a work around. (I attached it at the
bottom of this email).

I followed the work around suggestion and created the follow code (I only
extract part of the whole procedure) but it doesn't work neither.

Dim objExcel As Excel.Application

Set objExcel = GetObject(, "Excel.Application")

Dim wkbInvoice As Excel.Workbook

Set wkbInvoice = objExcel.Workbooks.Add(strTemplateLocation)

Dim intSheetCount as Integer

intSheetCount = 200

For IntI = 1 To intSheetCount

Dim intCount As Integer

intCount = wkbInvoice.Worksheets.Count

wkbInvoice.Worksheets("TemplateDetailSheet").Copy
Before:=wkbInvoice.Worksheets (intCount)

Next IntI

I can also repro this problem on EXCEL 2003.

Could someone help? Thanks a lot.

Abel
 
T

Tom Ogilvy

the workaround said to close the workbook

Dim objExcel As Excel.Application

Set objExcel = GetObject(, "Excel.Application")

Dim wkbInvoice As Excel.Workbook

Set wkbInvoice = objExcel.Workbooks.Add(strTemplateLocation)
wkbInvoice.SaveAs "C:\MyFile.xls"

Dim intSheetCount as Integer

intSheetCount = 200

For IntI = 1 To intSheetCount

Dim intCount As Integer


wkbInvoice.Worksheets("TemplateDetailSheet").Copy _
Before:=wkbInvoice.Worksheets (intCount)


if wkbInvoice.Worksheets.Count Mod 10 = 0 then
wkbInvoice.Close SaveChanges:=True
set wkbInvoice = Workbooks.Open _
filename:="C:\Myfile.xls"
End if

Next IntI

End Sub
 
P

Peter Huang

Hi awong,

Did TOM's suggestion help you?
If you still have any concern on this issue, please feel free to post here.

As for the issue that you did not get reply about your original post from
MSFT, it may be caused by that you did not register no spam email alias as
the link below.
http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.asp
&SD=msdn

So I recommend that you register the no spam email alias as the link above,
if you have any problem about registering, you may contact
[email protected].

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
A

awong

The solution you provided is similar to the code under the KB RESOLUTION section. I actually tried and it works.

However, the KB also provided a workaround under the WORKAROUND section.

It uses EXCEL template and didn’t say anything about save, close, and reopen after every 100 iterations.

That’s why I thought by using the workaround EXCEL template, I don't need to save, close and reopen the worksheet. I tried and it doesn’t work.

If both solutions under RESOLUTION and WORKAROUND requires save, close and reopen after every 100 iterations, that is fine. I just want to confirm this is the case.

Thanks.

Abel
 
A

awong

The solution you provided is similar to the code under the KB RESOLUTION section. I actually tried and it works.

However, the KB also provided a workaround under the WORKAROUND section.

It uses EXCEL template and didn’t say anything about save, close, and reopen after every 100 iterations.

That’s why I thought by using the workaround EXCEL template, I don't need to save, close and reopen the worksheet. I tried and it doesn’t work.

If both solutions under RESOLUTION and WORKAROUND requires save, close and reopen after every 100 iterations, that is fine. I just want to confirm this is the case.

Thanks.

Abel
 
P

Peter Huang

Hi Abel,

Based on my experience, the WORKAROUND section in the KB means we do not
copy the worksheet but save the worksheet we need to copy as a template.
Then we insert a worksheet based on the template we created just now, so
that we avoid to do the copy action.

To work around this problem, insert a new worksheet from a template instead
of copying an existing worksheet.

If you still have any concern on this issue, please feel free to let me
know.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
A

awong

I understand the template part.

I guess both copy worksheet and insert template DO REQUIRE save, close and reopen after every 100 iterations.
 
P

Peter Huang

Hi Abel,

Yes if we wants to use the approach as below
oBook.Worksheets(1).Copy
we need to reopen the workbook every 100 times.

But if we use the template approach method, I think we can avoid to do that.
e.g.
Create a new workbook and add some content to the workbook
A1:1
A2:2
A3:3
We can save the workbook as a template by saving it as the *.xlt file.(e.g.
c:\book1.xlt)

Now we close the excel and create a new workbook and run the code below.
Sheets.Add Type:="c:\book1.xlt"
After running the code we will find that there are another three sheet1
added into the workbook, and one of the sheet has the data as below
A1:1
A2:2
A3:3

So in this way we find that we do not need to do the copy action but he
sheet data has been added to the new created sheet in the new workbook.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Y

Yan-Hong Huang[MSFT]

Hello Abel,

I was reviewing this newsgroup thread. Do you have any more concerns on this issue? If there is any more question, please feel free to post
and we will follow up.

Also, I noticed that you didn't get response when you posted it earlier. Please make sure to use MSDN subscriber registered no spam email
alias [email protected] when posting. So we can response to you on time.

Thanks very much.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Top