?
Solved

Error formatting Excel spreadsheet from VBA

Posted on 2009-07-15
5
Medium Priority
?
204 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 2000 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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