Solved

Run time error 1004

Posted on 2011-09-10
31
545 Views
Last Modified: 2012-08-14
I can't figure out what causes this error to show up most of the time, but not all of the time. Here's the code.
Public Function Exporting() As String
    Dim xlObj As Object
    Dim strPath As String
    Dim NewFileName As String
    Dim NewFileWithPath As String
    
    strPath = CurrentProject.Path & "\FinancialExportTemplate.xlsx"
    NewFileName = "\FinancialExport_" & Format(Date, "yyyymmdd") & Format(Now, "hhmmss") & ".xlsx"
    NewFileWithPath = CurrentProject.Path & NewFileName
    
    FileCopy strPath, NewFileWithPath

    'Sends query information to separate worksheets in the 'temp' Excel spreadsheet
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportSales", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportCOGS", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportFreight", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportRebates", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportReplants", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportCropLoss", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportRoyltiesPaid", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportSSFIncome", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportExpenses", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportObsoleteInventory", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportInventoryServicing", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportInterest", NewFileWithPath
    
    '===================================================================
    '== Creates the Excel Spreadsheet and performs all the formatting ==
    '===================================================================
  
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open NewFileWithPath
    xlObj.Application.CalculateFull
    
   ' Tones of formatting.
  
        'Saving & Closing
        
      xlObj.ActiveWorkbook.Save
      xlObj.ActiveWorkbook.Close
        
        'Opens the spreadsheet for viewing
        
       FollowHyperlink NewFileWithPath
        
End Function

Open in new window

0
Comment
Question by:dlogan7
  • 12
  • 10
  • 4
  • +3
31 Comments
 

Author Comment

by:dlogan7
Comment Utility
Forgot to include an image showing the details of the error message. Error
0
 
LVL 16

Expert Comment

by:Sheils
Comment Utility
When you click debug, which line in your code is highlighted
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
I don't see Sheets in the code you posted - is it in one of the called functions?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
change this part

acSpreadsheetTypeExcel9

with


acSpreadsheetTypeExcel12Xml
0
 

Author Comment

by:dlogan7
Comment Utility
The error is occurring on the first line of code in the section where I am applying formatting to the Excel file. The line is:

Sheets("Sales").Select

Cap, I made the change you suggested and an getting the same behavior.

Maybe this will make sense to you folks. I only get the message when I run the export function a second time without first closing the Access file. In other words, as long as I close the Access file after each export I don't get the message. It seems like it has something to do with not closing some of the Excel statements or maybe something needs to be set to null after each export. I don't know enough about VBA to understand what's going on.

Another odd thing, running the function a third time without closing Access does not return the error. However, when I close the current Excel file, another Excel file is then visible. Trying to close that one I am asked if I want to save the file. It seems like this is the file that was created from the second running of the function. Since I am naming each export with a time stamp in the name, I am pretty sure about this being the second file created.

Confused? I am.

Thanks, Dale
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<Confused? I am. > so are we,

you did not post the codes where you are getting the error..

try this codes

      xlObj.ActiveWorkbook.Save
                   ' xlObj.ActiveWorkbook.Close
     
      xlObj.quit
      set xlObj=Nothing
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
<<Sheets("Sales").Select>>

Also, assuming that this code is in your Access VBA, I believe you need to prefix it somthing like this:

xlObj.ActiveWorkbook.Sheets("Sales").Select

Or if you have a workbook object defined somewhere, like this:

xlWB.Sheets("Sales").Select

(where xlWB is the name of your workbook object)

0
 

Author Comment

by:dlogan7
Comment Utility
My lack of VBA experience is obviously what's causing the problem. For the formatting section of my code I recorded a macro in Excel and simply pasted that code into my Access code. Here's a section of the code for one of the sheets:

I have no idea which lines need to be preceded with references to the workbook or sheet.

Sheets("Sales").Select
     Columns("A:X").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
        Columns("E:E").Select
    Selection.Style = "Currency"
    Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
    Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    Range("A4").Select

0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Don't use Select or Selection, especially if you are automating Excel from Access.

You should also avoid using ActiveWorkbook - create a reference to the workbook you are opening and use that.

So the part of the code that opens/saves the workbook should look like this.
    Set xlObj = CreateObject("Excel.Application")
    Set xlWB = xlObj.Workbooks.Open(NewFileWithPath)
    
    xlObj.CalculateAll
    
    ' code not posted
  
        'Saving & Closing
     xlWB.Save
     xlWB.Close True
     
     xlObj.Quit
     
     FollowHyperlink NewFileWithPath[code]

Open in new window

Does the error actually occur in the code you've not posted which is something like the last code you posted?

If it is we really need to see that code.

This is an example of how you could (should) do the formatting.
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object

        Set xlApp = CreateObject("Excel.Application")
        
        Set xlWB = xlApp.Workbooks.Open("C:\RandomWorkbook.xlsx")    ' change workbook name as needed
        
        Set xlWS = xlWB.Worksheets("Sales")
        
        With xlWS.Columns("A:X").Font
            .Name = "Calibri"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        
        With xlWS.Columns("E:E")
        
            .Style = "Currency"
            .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
            
        End With

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Up at the top of your code (the code you posted first in this thread - original post), add the bolded changes:

    Dim xlObj As Object
    Dim xlWB as Object   '<---- Add
    Dim strPath As String
    Dim NewFileName As String
    Dim NewFileWithPath As String

Further down, modify the code like this:

    '===================================================================
    '== Creates the Excel Spreadsheet and performs all the formatting ==
    '===================================================================
 
    Set xlObj = CreateObject("Excel.Application")
    Set xlWB = xlObj.Workbooks.Open NewFileWithPath   '<---- change this line as shown
    xlObj.Application.CalculateFull


In your formatting section:

      ' Sheets("Sales").Select   <--- try taking this out...
     xlWB.Sheets("Sales").Columns("A:X").Select  '<--- and using this instead
    With xlObj.Selection.Font   '<---- add prefix
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
        xlWB.Sheets("Sales").Columns("E:E").Select  '<-- Prefix
    xlObj.Selection.Style = "Currency"
    xlObj.Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
    xlObj.Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    xlObj.Range("A4").Select


You *Can* successfully convert an Excel Macro to an Acces function using the appropriate prefixes:

xlObj before workbooks, selections, ranges
xlWB before .Sheets

there should be a prefix before every Excel statement so that it is clear which application/workbook/worksheet is being used.

All that said, however, you will find that converted Macros are much less efficient, and harder to follow than simply writing your own automation code in Access.



0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
You really don't need to use Select/Selection for this.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
I can understand why you dont want to post the entire code in a comment -- a converted excel macro working over multiple sheets like that is undoubtably very verbose (unnecessarily so).

You can, however post a sample DB as an attachment (just ensure that there is no sensitive data in it).
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
<You really don't need to use Select/Selection for this. >

Agreed, and there is probably plenty else that is unneeded too (that goes with the territory when using converted macros). Although converting a Macro can be a good way to start as a beginner (getting things up and running fairly quickly),  you will get better code by writing this stuff independently.
0
 

Author Comment

by:dlogan7
Comment Utility
Here's the file. Actually, it's multiple files. There's a front end, back end, and Excel template. It's not really a template because I never could figure that out. In order for things to work, all 3 must be in the same folder. I have modified the AutoExec to only open the necessary forms to allow for exporting. To export to Excel click on the Excel icon.

In my latest attempts to modify the code I have changed something that ends up giving you an opened Excel without any visible data. Not sure what's going on there. To get the error message, you will need to do a second export before closing the file. Any help would be greatly appreciated.

I'm sure you may find ways to improve what I am doing. That would be nice, but I am really trying to get this out the door to the client and the only piece really needing to be fixed is the export.

For-EE-Upload.zip
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
Comment Utility
I've only had a quick look at the code for exporting to Excel and it definitely looks like it can be tidied up a fair bit.

It looks like some of the formatting you are applying is the same for each worksheet.

That can be done in a loop
    Set xlObj = CreateObject("Excel.Application")
    Set xlWB = xlObj.Workbooks.Open(NewFileWithPath)
    xlObj.Application.CalculateFull
    xlObj.Visible = True

    For Each xlWS In xlWB.Worksheets

        With xlWS.Columns("A:X")

            .Name = "Calibri"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
    Next xlWS

Open in new window


The formatting specific to particular worksheets can be added after this.

Here's some rough, untested, code, hopefully it shows what I mean.
Public Function Exporting() As String
Const xlAutomatic = -4105
Const xlThemeFontMinor = 2

Dim xlObj As Object
Dim xlWB As Object
Dim xlWS As Object

Dim strPath As String
Dim NewFileName As String
Dim NewFileWithPath As String

    strPath = CurrentProject.Path & "\FinancialExportTemplate.xlsx"
    NewFileName = "\FinancialExport_" & Format(Date, "yyyymmdd") & Format(Now, "hhmmss") & ".xlsx"
    NewFileWithPath = CurrentProject.Path & NewFileName

    FileCopy strPath, NewFileWithPath

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportSales", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportCOGS", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportFreight", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportRebates", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportReplants", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportCropLoss", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportRoyltiesPaid", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportSSFIncome", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportExpenses", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportObsoleteInventory", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportInventoryServicing", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportInterest", NewFileWithPath

    '===================================================================
    '== Creates the Excel Spreadsheet and performs all the formatting ==
    '===================================================================

    Set xlObj = CreateObject("Excel.Application")
    Set xlWB = xlObj.Workbooks.Open(NewFileWithPath)
    xlObj.Application.CalculateFull
    xlObj.Visible = True

    For Each xlWS In xlWB.Worksheets

        With xlWS.Columns("A:X")

            .Name = "Calibri"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With

    Next xlWS

    Set xlWS = xlWB.Worksheets("Sales")

    With xlWS.Columns("E:E")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    Set xlWS = xlWB.Sheets("COGS")

    xlWS.Columns("B:B").Style = "Currency"
    With xlWS.Columns("C:D")
        .Style = "Comma"

        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("E:E")
        .Style = "Currency"

        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    Set xlWS = xlWB.Sheets("Freight")

    With xlWS.Columns("B:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    Set xlWS = xlWB.Sheets("Rebates")
    

    ' rest of formatting


    xlObj.Goto xlWB.Range("C5"), True

    'Saving & Closing
    'Sends query information to separate worksheets in the 'temp' Excel spreadsheet

    xlWB.Save
    xlWB.Close
    xlObj.Quit

    Set xlObj = Nothing

    'Opens the spreadsheet for viewing

    FollowHyperlink NewFileWithPath

    '  Shell "NewFileWithPath", vbNormalFocus

End Function

Open in new window

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:dlogan7
Comment Utility
Well, first of all, thank you very much for offering to help. I did my best to modify the code using your suggestion as an example. However, I must be doing something wrong. The attached Excel file is the result of the modified code. The code is also attached.

In order to get it to complete, I needed to comment out some of the lines. In the loop section I was only able to use the line that sets the font. The other lines were producing an error.

Then, in the section that formats each sheet, the only error was coming when I tried to make the upper left corner the active cell. So, I commented those lines out too.

All in all, I can't see where any of the formatting is working at all.
 FinancialExport-20110911160338.xlsx
Public Function Exporting() As String
Const xlAutomatic = -4105
Const xlThemeFontMinor = 2

Dim xlObj As Object
Dim xlWB As Object
Dim xlWS As Object

Dim strPath As String
Dim NewFileName As String
Dim NewFileWithPath As String
    
    strPath = CurrentProject.Path & "\FinancialExportTemplate.xlsx"
    NewFileName = "\FinancialExport_" & Format(Date, "yyyymmdd") & Format(Now, "hhmmss") & ".xlsx"
    NewFileWithPath = CurrentProject.Path & NewFileName
    
    FileCopy strPath, NewFileWithPath
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportSales", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportCOGS", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportFreight", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportRebates", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportReplants", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportCropLoss", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportRoyltiesPaid", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportSSFIncome", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportExpenses", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportObsoleteInventory", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportInventoryServicing", NewFileWithPath
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportInterest", NewFileWithPath
    
    '===================================================================
    '== Creates the Excel Spreadsheet and performs all the formatting ==
    '===================================================================
  
    Set xlObj = CreateObject("Excel.Application")
    Set xlWB = xlObj.Workbooks.Open(NewFileWithPath)
    xlObj.Application.CalculateFull
    xlObj.Visible = True
    
    For Each xlWS In xlWB.Worksheets

        With xlWS.Columns("A:X")

            .Name = "Calibri"
     '       .Size = 10
     '       .Strikethrough = False
     '       .Superscript = False
     '       .Subscript = False
     '       .OutlineFont = False
     '       .Shadow = False
     '       .ColorIndex = xlAutomatic
     '       .TintAndShade = 0
     '       .ThemeFont = xlThemeFontMinor
        End With
    Next xlWS
    
' ***Sales worksheet ***********
    
Set xlWS = xlWB.Worksheets("Sales")

    With xlWS.Columns("C:C")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("D:D")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("E:E")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
 '   xlObj.Goto xlWb.Range("A4"), True
    
    
' ***COGS worksheet ***********
    
Set xlWS = xlWB.Worksheets("COGS")

    With xlWS.Columns("B:B")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("C:D")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("E:E")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
 '   xlObj.Goto xlWb.Range("A4"), True

' ***Freight worksheet ***********

Set xlWS = xlWB.Worksheets("Freight")

    With xlWS.Columns("B:B")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("C:C")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("D:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
 '   xlObj.Goto xlWb.Range("A4"), True
    
' ***Rebates worksheet ***********
    
Set xlWS = xlWB.Worksheets("Rebates")

    With xlWS.Columns("E:M")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("N:N")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("O:O")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
 '   xlObj.Goto xlWb.Range("A4"), True
    
' ***Replants worksheet ***********
    
Set xlWS = xlWB.Worksheets("Replants")

    With xlWS.Columns("E:M")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("N:N")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("O:O")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
 '   xlObj.Goto xlWb.Range("A4"), True
    
' ***CropLoss worksheet ***********
    
Set xlWS = xlWB.Worksheets("CropLoss")

    With xlWS.Columns("E:M")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("N:N")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("O:O")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
 '   xlObj.Goto xlWb.Range("A4"), True
    
' ***Royalties Paid worksheet ***********
    
Set xlWS = xlWB.Worksheets("Royalties Paid")

    With xlWS.Columns("C:C")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("D:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("E:E")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("F:F")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
    
 '   xlObj.Goto xlWb.Range("A4"), True
    
' ***SSF Income worksheet ***********
    
Set xlWS = xlWB.Worksheets("SSF Income")

    With xlWS.Columns("C:C")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("D:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("E:H")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("I:I")
        .Style = "Comma"
    End With
    
    With xlWS.Columns("J:K")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("L:L")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("M:M")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("N:O")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("P:P")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("Q:Q")
        .Style = "Percent"
    End With
 '   xlObj.Goto xlWb.Range("A4"), True
    
' ***Expenses worksheet ***********
    
Set xlWS = xlWB.Worksheets("Expenses")

    With xlWS.Columns("C:C")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
 '   xlObj.Goto xlWb.Range("A4"), True
    
' ***Obsolete Inventory worksheet ***********
    
Set xlWS = xlWB.Worksheets("Obsolete Inventory")

    With xlWS.Columns("B:B")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("C:D")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("E:E")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
 '   xlObj.Goto xlWb.Range("A4"), True
    
' ***Inventory Servicing worksheet ***********
    
Set xlWS = xlWB.Worksheets("Inventory Servicing")

    With xlWS.Columns("B:B")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("E:F")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("I:J")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("M:M")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("O:O")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("C:C")
        .Style = "Currency"
    End With
    With xlWS.Columns("G:G")
        .Style = "Currency"
    End With
    With xlWS.Columns("K:K")
        .Style = "Currency"
    End With
    With xlWS.Columns("N:N")
        .Style = "Currency"
    End With
    
    With xlWS.Columns("D:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("H:H")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("L:L")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("P:Q")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
'    xlObj.Goto xlWb.Range("A4"), True
    
' ***Interest worksheet ***********
    
Set xlWS = xlWB.Worksheets("Interest")

    With xlWS.Columns("B:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("G:G")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
    
    With xlWS.Columns("E:F")
        .Style = "Currency"
    End With
 '   xlObj.Goto xlWb.Range("A4"), True
    
Set xlWS = xlWB.Worksheets("Summary")
    
    'Saving & Closing
    'Sends query information to separate worksheets in the 'temp' Excel spreadsheet
        
    xlWB.Save
    xlWB.Close
    xlObj.Quit

    Set xlObj = Nothing
          
    'Opens the spreadsheet for viewing
        
    FollowHyperlink NewFileWithPath
       
End Function

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Told you it was rough.:)

This:

With ws.Columns("A:X")

should be this:

ws.Columns("A:X").Font

Also instead of this:

Application.Goto xlWB.Range("A4"), True

should be this:

Application.Goto xlWS.Range("A4"), True

Apart from that are you sure absolutely no formatting is applied?

I can't quite see why that is but perhaps it's connected to the code applying a style, then a number format  and then different format to a lot of the columns.

PS The file you attached does seem to have some formatting, I assume that's how you want the format rather than the result of the code.
0
 

Author Comment

by:dlogan7
Comment Utility
Actually, the non-formatted portion of the Excel file would be for any new records on each sheet that extends beyond the rows that already had data in the "template". So, on sheet COGS, the last 2 rows. I will modify based on your comments and let you know how it goes. Thanks.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Do you mean that only new records are being exported to the template and it's those that need formatting?

I can't quite see how that would work with the export code you posted.

As far as I can see it creates new worksheets and puts the data on them rather than appending to existing data.

PS I tested the code, with the changes I mentioned and it worked without error.

Whether it applied the formatting correctly I don't know.
0
 

Author Comment

by:dlogan7
Comment Utility
Well, I'll be a monkey's uncle. That takes care of the formatting issue. The only thing I still can't figure out is how to change the active cell for each sheet. Right now they are all selecting the column that got the last formatting. I'd like each of them to be in cell A4. I was trying to do it like so:

xlObj.Goto xlWS.Range("A4"), True

that does nothing.

Your code above:

Application.Goto xlWS.Range("A4"), True

actually receives an error. Method or data member not found.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Well it should be xlObj, I changed it to Application when I checked the code in Excel.

Don't know why it's not working though.

In Excel VBA the syntax is basically something like this:

Application.GoTo Reference:=<reference>, Scroll:=<True/False>

Where <reference> is, well, a reference to the range you want to goto and Scroll tells Excel/VBA to scroll so the reference is in the top-left.

The only thing I can think of, and it's a very long shot, is that True in Access isn't the same as True in Excel.
0
 

Author Comment

by:dlogan7
Comment Utility
OK, I found something in another question that looked promising:

xlObj.Range("A1").Select

That doesn't do anything, but at least there are no errors. So much for that.

Also, something weird is going on now. The Excel file opens, I can see it making the format changes. It closes, and then reopens, but nothing is visible. There are actually 2 formula bars too. I am attaching a screenshot.

 screen
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
I really don't know why Goto doesn't work and it's usually best to avoid using Select or Selection.

The only other possibilities I can think of are that the worksheets are locked, there are frozen panes, there's code that reverses what this code does, and this is probably way off, you've opened the wrong file.

I've actually seen that last one happen by the way, not often though.

As for the double formula bar etc that could be an error with Excel itself or perhaps the fie has become slightly corrupted.

Also, why do you open the file with a hyperlink at the end of the code?

PS Have you checked Task Manager to see if there are any 'ghost' instances of Excel running after the code has finished?

Why not save the file but don't close it and quit Excel?
0
 

Author Comment

by:dlogan7
Comment Utility
Just so you know, every bit of this code (except for the copied macro) has come from folks on EE trying to help me accomplish something I've never done before. I'm way over my head.

<Also, why do you open the file with a hyperlink at the end of the code?>
I told someone I wanted to open the file after exporting. Is there a better option?

<Why not save the file but don't close it and quit Excel?>
Sounds good to me. How do I do that?

The worksheets are not locked. I do have frozen panes on each sheet. In other words, I am freezing the column rows while scrolling.

Lying in bed last night I remembered another conversation from several weeks ago about this process. It was suggested that I use a real Excel template. That one had problems because my data was coming from a parametrized query. An alternative would be to dump the data from the query to a temporary table. Man I am getting lost now.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Using a hyperlink might be what's causing the strange behaviour in Excel, ie the double formula bar.

I've certainly seen weird stuff when I've opened or downloaded a file via a hyperlink.

It's easy to not do something, just remove, or comment out,  the code that does it.

This is the code that closes the workbook and quits Excel:

xlWB.Close
xlObj.Quit

So if you comment out that then Excel should remain open with the workbook loaded (and saved).

Freezing panes can cause problems with Goto and probably Select, but it might only happen if the worksheet(s) are frozen when the code is run.

Also if the the reference you use is not within the non-frozen area of the worksheet it might not work.

I'm not sure I quite understand the suggestions.

What was meant by a 'real' template for and how wouldn't it work with parameterized queries.

Also why a temporary table, was that to get round the parameter problem?

I think if I was going to do this, and I've done this sort of thing before I would try one of 2 options, one of which you seem to be doing already.

That's exporting all the data to Excel and then automating from Access VBA to do all the formatting etc.

The other option, would be to export the data to Excel using something like ADO/DAO.

That would allow the use of a template because you can specify the range as well as the worksheet you want the data to go to.

There's is another option - don't do this in Access.

That would basically 'pull' the data to Excel from Access and then do all the formatting in Excel too.

I don't know if there's any advantage or disadvantage in doing it that way though.

Sorry for the lengthy post and I hope I've not made things more confusing.
0
 

Author Comment

by:dlogan7
Comment Utility
I think it's to the point where I am going to sign off on it. I removed the frozen panes, commented out the hyperlink line, and everything seems to be working. The only thing I wish we could have figured out is to move the active cell on each sheet to A1 instead of each one of them having a column completely highlighted.

Thank you so much for staying with me on this one. This has been a thorn in my side for a while.

Thanks, Dale
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Dale

Glad you've got a reasonable solution.

Still curious about why the Goto isn't working.

What are you trying to set the focus to?

A single cell or an entire column?
0
 

Author Comment

by:dlogan7
Comment Utility
A single cell. Sorry for not making that clear all along. After applying formats by column, each sheet ends up having an entire column with the focus. It just looks bad when you first go to each sheet.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
to select a cell on the active sheet, use

 xlObj.range("A1").select

0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Dale

Nothing gets selected anywhere in the last code you posted, so nothing should be selected at the end unless something was already selected before the code was run.

Try adding this after all the formatting.

For Each xlWS In xlWB.Worksheets

          xlApp.Goto xlWS.Range("A1"), Scroll:=True

Next xlWS

Remember and change the variable names if needed, I think I've got them right but you never know.

I've just opened the last file you attached and the last column with data is selected on each worksheet (last 2 on the Inventory Switching sheet.

When I run the code above the original selection(s) are gone and the cursor is in A1 on each worksheet.

Right, added that code to the bottom of your code and it's doing the same.

Here's all the code, but please remember commented out a few things and replaced them because I tested in Excel - you should just be able to uncomment your bits and comment the my bits.

PS I know testing in Access would be better but I've not had a chance to set things up properly for that.
Option Explicit

Public Sub Exporting()
'Const xlAutomatic = -4105
'Const xlThemeFontMinor = 2
'
'Dim xlObj As Object
Dim xlWB As Workbook
Dim xlWS As Worksheet
    '
    'Dim strPath As String
    'Dim NewFileName As String
    'Dim NewFileWithPath As String

    '    strPath = CurrentProject.Path & "\FinancialExportTemplate.xlsx"
    '    NewFileName = "\FinancialExport_" & Format(Date, "yyyymmdd") & Format(Now, "hhmmss") & ".xlsx"
    '    NewFileWithPath = CurrentProject.Path & NewFileName
    '
    '    FileCopy strPath, NewFileWithPath

    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportSales", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportCOGS", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportFreight", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportRebates", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportReplants", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportCropLoss", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportRoyltiesPaid", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportSSFIncome", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportExpenses", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportObsoleteInventory", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportInventoryServicing", NewFileWithPath
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExportInterest", NewFileWithPath

    '===================================================================
    '== Creates the Excel Spreadsheet and performs all the formatting ==
    '===================================================================

    'Set xlObj = CreateObject("Excel.application")
    'Set xlWB = xlObj.Workbooks.Open(NewFileWithPath)
    'xlObj.Visible = true
    
    Set xlWB = ThisWorkbook

    For Each xlWS In xlWB.Worksheets

        With xlWS.Columns("A:X").Font

            .Name = "Calibri"
                   .Size = 10
                   .Strikethrough = False
                   .Superscript = False
                   .Subscript = False
                   .OutlineFont = False
                   .Shadow = False
                   .ColorIndex = xlAutomatic
                   .TintAndShade = 0
                   .ThemeFont = xlThemeFontMinor
        End With
    Next xlWS

    ' ***Sales worksheet ***********

    Set xlWS = xlWB.Worksheets("Sales")

    With xlWS.Columns("C:C")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("D:D")
        .Style = "Currency"
    End With

    With xlWS.Columns("E:E")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
  


    ' ***COGS worksheet ***********

    Set xlWS = xlWB.Worksheets("COGS")

    With xlWS.Columns("B:B")
        .Style = "Currency"
    End With

    With xlWS.Columns("C:D")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("E:E")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
  

    ' ***Freight worksheet ***********

    Set xlWS = xlWB.Worksheets("Freight")

    With xlWS.Columns("B:B")
        .Style = "Currency"
    End With

    With xlWS.Columns("C:C")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("D:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
  

    ' ***Rebates worksheet ***********

    Set xlWS = xlWB.Worksheets("Rebates")

    With xlWS.Columns("E:M")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("N:N")
        .Style = "Currency"
    End With

    With xlWS.Columns("O:O")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
  

    ' ***Replants worksheet ***********

    Set xlWS = xlWB.Worksheets("Replants")

    With xlWS.Columns("E:M")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("N:N")
        .Style = "Currency"
    End With

    With xlWS.Columns("O:O")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
  

    ' ***CropLoss worksheet ***********

    Set xlWS = xlWB.Worksheets("CropLoss")

    With xlWS.Columns("E:M")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("N:N")
        .Style = "Currency"
    End With

    With xlWS.Columns("O:O")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
  

    ' ***Royalties Paid worksheet ***********

    Set xlWS = xlWB.Worksheets("Royalties Paid")

    With xlWS.Columns("C:C")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("D:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("E:E")
        .Style = "Currency"
    End With

    With xlWS.Columns("F:F")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

  

    ' ***SSF Income worksheet ***********

    Set xlWS = xlWB.Worksheets("SSF Income")

    With xlWS.Columns("C:C")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("D:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("E:H")
        .Style = "Currency"
    End With

    With xlWS.Columns("I:I")
        .Style = "Comma"
    End With

    With xlWS.Columns("J:K")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("L:L")
        .Style = "Currency"
    End With

    With xlWS.Columns("M:M")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("N:O")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("P:P")
        .Style = "Currency"
    End With

    With xlWS.Columns("Q:Q")
        .Style = "Percent"
    End With
  

    ' ***Expenses worksheet ***********

    Set xlWS = xlWB.Worksheets("Expenses")

    With xlWS.Columns("C:C")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
  

    ' ***Obsolete Inventory worksheet ***********

    Set xlWS = xlWB.Worksheets("Obsolete Inventory")

    With xlWS.Columns("B:B")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("C:D")
        .Style = "Currency"
    End With

    With xlWS.Columns("E:E")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
  

    ' ***Inventory Servicing worksheet ***********

    Set xlWS = xlWB.Worksheets("Inventory Servicing")

    With xlWS.Columns("B:B")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("E:F")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("I:J")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("M:M")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("O:O")
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("C:C")
        .Style = "Currency"
    End With
    With xlWS.Columns("G:G")
        .Style = "Currency"
    End With
    With xlWS.Columns("K:K")
        .Style = "Currency"
    End With
    With xlWS.Columns("N:N")
        .Style = "Currency"
    End With

    With xlWS.Columns("D:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("H:H")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("L:L")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("P:Q")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
    '    xlObj.Goto xlWb.Range("A4"), True

    ' ***Interest worksheet ***********

    Set xlWS = xlWB.Worksheets("Interest")

    With xlWS.Columns("B:D")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("G:G")
        .Style = "Currency"
        .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    With xlWS.Columns("E:F")
        .Style = "Currency"
    End With
  
    For Each xlWS In xlWB.Worksheets
        ' xlObj.Goto xlWs.Range("A1"), True
        Application.Goto xlWS.Range("A1"), True
    Next xlWS
    
  
End Sub

Open in new window

0
 

Author Comment

by:dlogan7
Comment Utility
Sounds interesting. I will be able to give this a try later today and let you know how it goes.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now