Solved

Error formatting Excel spreadsheet from VBA

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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 …

803 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