Solved

Error formatting Excel spreadsheet from VBA

Posted on 2009-07-15
5
201 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:jkoneil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 13

Accepted Solution

by:
Brian Withun earned 500 total points
ID: 24862385
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24862529
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
 

Author Comment

by:jkoneil
ID: 24862803
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
 

Author Comment

by:jkoneil
ID: 24896454
I tried replacing Set excelws = excelwb.Worksheets(vWorksheet) with Set excelws = excelwb.Worksheets(1) and that seems to have fixed the problem.
0
 

Author Closing Comment

by:jkoneil
ID: 31603884
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

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question