Solved

Error formatting Excel spreadsheet from VBA

Posted on 2009-07-15
5
200 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
  • 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

828 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