Error formatting Excel spreadsheet from VBA

Hi!  I have a query in MSAccess that I am using TransferSpreadsheet to export to Excel.  I have a module to format the Excel file once it is exported.  Sometimes the file is exported and formatted correctly and other times I receive an error:  Subscript Out of Range.  
I attached the code from the module but it seems that the error is coming from Excel.  Is it possible that the data being exported is causing the problem?  It seems to happen more often when replacing an existing file with the same name rather than generating a new file.  
Could this be related to an existing RESUME.xlw file when trying to export another file?  

Sub ModifyExcelFile(vRptName As String, vHeader As String, vWorksheet As String, vTabName As String)
On Error GoTo Err_ModifyExcelFile
 
 
    Dim excelapp As Object
    Dim excelwb As Workbook
    Dim excelws As Worksheet
 
    'Open the XLS file with the Workbook from Application, not directly from Application.
    Set excelapp = New Excel.Application
    Set excelwb = excelapp.Workbooks.Open(vRptName)
    Set excelws = excelwb.Worksheets(vWorksheet)
                   
            excelws.Rows("1:1").Select
            excelws.Application.Selection.Font.Bold = True
            excelws.Columns("A:M").Select
            excelws.Application.Selection.Columns.AutoFit
            excelws.PageSetup.Orientation = xlLandscape
            excelws.PageSetup.CenterHeader = vHeader
            excelws.PageSetup.LeftHeader = "Generated on: " & Now()
            excelws.PageSetup.CenterFooter = "Page &P"
            excelws.Name = vTabName
            excelws.Cells.Select
            excelws.Application.Selection.Font.Name = "Tahoma"
            excelws.Application.Selection.Font.Size = 10
            excelws.Cells.Select
            excelws.Application.Selection.AutoFilter
 
    'Save the file so the prompt asking to save the file will not pop up
    excelws.Application.SaveWorkspace
   
    'Need to quit the application or the file will be kept opened in memory
    excelapp.Quit
    
    'Freeing the objects
    Set excelws = Nothing
    Set excelwb = Nothing
    Set excelapp = Nothing
    
   MsgBox "Report exported to " & vRptName
     
Exit_ModifyExcelFile:
    Exit Sub
 
Err_ModifyExcelFile:
    MsgBox Err.Description
    
    'Need to quit the application or the file will be kept opened in memory
    excelapp.Quit
    
    'Freeing the objects
    Set excelws = Nothing
    Set excelwb = Nothing
    Set excelapp = Nothing
 
    Resume Exit_ModifyExcelFile
    
End Sub

Open in new window

jkoneilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian WithunCommented:
My theory is that this line is causing it:

Set excelws = excelwb.Worksheets(vWorksheet)

There are very few statements which involve a "subscript".
It could be that vWorksheet has a value which does not correspond to an actual sheet name in Excel.
Step through this procedure in the debugger to confirm.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
first open the the excel file you used for the variable (vRptName)
then, look for the name of the sheet that you use for the variable (vWorksheet) if it exist in the Excel file.
0
jkoneilAuthor Commented:
I stepped through the procedure in MS Access and the file was exported successfully 2 times and one time it issued the error and it was on the Set excelws = excelwb.Worksheets(vWorksheet) line.    When I open the export that had the error, everything looks correct.

0
jkoneilAuthor Commented:
I tried replacing Set excelws = excelwb.Worksheets(vWorksheet) with Set excelws = excelwb.Worksheets(1) and that seems to have fixed the problem.
0
jkoneilAuthor Commented:
Thanks for pointing me in the right direction.  I couldn't figure out what was causing the error.  I thought it might be the data that was exported.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.