Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Running macro code after export to Excel

    OK, here is the goal: After exporting data from MS Access to an Excel file, I need to create a default pivot table within the Excel file based upon the exported data.

    To do this, I exported my data and then recorded a macro while I created and formatted the pivot table. So I have a block of code to do what I need, and it is relativley short.

    My problem is, how can I execute this Excel code from within MS Access? Here is what I have so far:

    Code:
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, DataSetName, ExportFile, True
            Set xlApp = CreateObject("Excel.Application")
            Set xlBook = xlApp.Workbooks.Open(ExportFile)
            Set xlSheet = xlBook.worksheets(1)
            With xlApp
                .Application.Visible = True
                .ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Project_Spend_Report_export!R1C1:R1173C8").CreatePivotTable TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
                .ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
                .ActiveSheet.Cells(3, 1).Select
            End With
            Set xlSheet = Nothing
            Set xlBook = Nothing
            Set xlApp = Nothing
    But I am already running into glitches. MS Access VB chokes on the PivotCaches.Add command, because it does not recognize the constants referenced ("xlDatabase", and "xlPivotTableVersion10"). Now somewhere I ought to be able to find the actual values for these constants, though I haven't been able to so far. And I am afraid this is just the tip of the debugging iceberg.

    Am I doing this the best way? Is there a way to load the macro I recorded into a string variable and then just send the whole, unedited package to Excel and say "Here, YOU run this." Kind of like dynamic VB?

    I fear I could spend the next two weeks debugging this and end up with hack code. Any help is appreciated.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Blindman

    Few pointers:
    Looking at your code you are using Late Binding (i.e. the app has no idea what your code is going to do until it runs it). This is less efficient, slower, prevents use of intellisense and, as you have found, prevents use of constants.

    At the very minimum I would use early binding during development stage to make use of intellisnese and figure out your constants.

    Go to the VBA IDE Tools-> References scroll down to Microsoft Excel 1x.0 object library and tick it. You can then change your code to:
    Code:
    '....
    Dim xlApp AS Excel.Application
    Dim xlBook  AS Excel.Workbook
    '.....
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, DataSetName, ExportFile, True
    'Set xlApp = CreateObject("Excel.Application") Set xlApp AS NEW Excel.Application Set xlBook = xlApp.Workbooks.Open(ExportFile) Set xlSheet = xlBook.worksheets(1) With xlApp .Application.Visible = True .ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Project_Spend_Report_export!R1C1:R1173C8").CreatePivotTable TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 .ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) .ActiveSheet.Cells(3, 1).Select End With Set xlSheet = Nothing Set xlBook = Nothing Set xlApp = Nothing
    Also, with the reference set you can find out the constants by simply typing
    Code:
    ?xlDatabase
    in the immediate window and hit return. If you don't set a reference you can't use these constants. If you do set the reference then you don't need to know their value. You can also check using the same method in Excel.

    Is this related to the export of the Crosstab? You know that the code I provided would export a Crosstab into Excel? If you used that would you no longer need to pivot within Excel?

    HTH
    Last edited by pootle flump; 01-26-06 at 05:51.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Addition:
    Forgot to mention.
    Extending on the "you should early bind at least during development stage..."

    If your users will not use an earlier version of Excel than you develop in then there is no reason to remove the reference when done. If they will then you can develop with the reference set and use the following structure (I saw this on another site and I think it is a yummy idea):

    Code:
    Const IsDevVersion As Boolean = True
     
    Sub MySub()
    #If IsDevVersion Then
        Dim xlApp As Excel.Application
        Dim xlBook  As Excel.Workbook
        
        Set xlApp = New Excel.Application
        
    #Else
        Dim xlApp As Object
        Dim xlBook  As Object
        
        Set xlApp = CreateObject("Excel.Application")
        
    #End If
    'Your Code
    End Sub
    #If..Then...Else is evaluated at compile time not run time. So you can code your project like this and then change the value of IsDevVersion and remove the Excel reference before distribution. Remember also to use literal values not xl constants in your app.

    NB:- Please note - I have used #If..Then before and it worked great however I haven't tried the above specifically - the poster I was in contact with assured me it worked
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Am I doing this the best way? Is there a way to load the macro I recorded into a string variable and then just send the whole, unedited package to Excel and say "Here, YOU run this." Kind of like dynamic VB?
    Ho ho - me again

    I think you are doing it the right way - I prefer to keep all my code in the app and automate rather than have semi intelligent templates knocking around the place. Aprart from anything else, Murpheys Law states you will get the "um... I think I deleted\ irrevocably altered the template" phone call.

    I believe, however, that you can call the macro if it is within Excel - I can't remember how off the top of my head though

    I think most of your problems stem from late binding - I wouldn't fancy approaching a new object model without some help from intellisense.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Running an excel macro from another app.

    A quick check of Excel help shows you can pass params -

    Quote Originally Posted by Excel_Help
    expression.Run(Macro, Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

    mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)
    MsgBox "Macro result: " & mySum
    I still think your current appraoch is better but there is an alternative.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    ... and finally

    Lol - I am even boring myself - can you tell automation is a bit of a fave topic of mine?

    Last one (honest):

    If your app is an MDB rather than MDE then if you ask nicely Izy might show you some code that dynamically sets references - or you could search for it in recent posts (developed in conjunction with Wayne).

    This allows you to develop using a reference to a "higher" version of Excel than your users but your application dynamically adjusts its reference to the appropriate one for the client's installation.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    can you tell automation is a bit of a fave topic of mine?
    Lucky for me!
    I will take all your advice. Regardinging setting a reference to Excel, that one little tip will probably solve many of the issues I am getting. I suspected it was something very simple! blindman's law of code debugging: "Simple problems have complex solutions, and complex problems have simple solutions."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    blindman's law of code debugging: "Simple problems have complex solutions, and complex problems have simple solutions."
    Spooky - very similar to Poots' law of code debugging: "The complexity of the solution is often inversly proportional to the magnitude of the problem".

    I will work on making it snappier though

    Glad to be of help - I think of it as paying back for the help (wittingly and unwittingly) you've given me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, yours is much better than mine. I will start using yours now, and credit you for it too!

    Your solutions worked like a CHARM!

    I did run into one glitch with the transferspreadsheet action that I was unaware of. After Excel version 5.0, the TransferSpreadsheet action INSERTS data into an existing spreadsheet instead of overwriting the file, causing all sorts of problems if the pivot table already existed. I solved the problem by using OutputTo instead of TransferSpreadsheet. Damn tricky of the MS folks to change the behavior like that.

    Here is my final code. I'd much appreciate it if you gave it a glance over, particularly regarding my cleaning up and closing of objects at the end.

    Code:
    Sub ExportProjectSpendReport()
        Dim ExportFile As String
        Dim DefaultFile As String
        Dim DataSetName As String
        Dim xlApp As Object
        Dim xlBook As Object
        Dim xlSheet As Object
        DataSetName = "Project Spend Report_export"
        DefaultFile = GetSetting(AppName, "Application", DataSetName)
        ExportFile = SaveFileDialog(DefaultFile, "Export Destination", "*.xls", "XLS", False)
        If Len(ExportFile) > 0 Then
            If Right(ExportFile, 4) <> ".xls" Then ExportFile = ExportFile & ".xls"
            SaveSetting AppName, "Application", DataSetName, ExportFile
            DoCmd.OutputTo acOutputQuery, DataSetName, acSpreadsheetTypeExcel9, ExportFile
            Set xlApp = New Excel.Application
            Set xlBook = xlApp.Workbooks.Open(ExportFile)
            Set xlSheet = xlBook.worksheets(1)
            With xlApp
                .Application.Visible = True
                .Range("A1").Select
                .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
                .ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=.Selection).CreatePivotTable TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
                .ActiveSheet.PivotTableWizard TableDestination:=.ActiveSheet.Cells(3, 1)
                With .ActiveSheet.PivotTables("PivotTable1").PivotFields("CAR")
                    .Orientation = xlRowField
                    .Position = 1
                End With
                With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Type")
                    .Orientation = xlRowField
                    .Position = 2
                End With
                With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
                    .Orientation = xlColumnField
                    .Position = 1
                End With
                With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Fiscal Year")
                    .Orientation = xlColumnField
                    .Position = 1
                End With
                .ActiveSheet.PivotTables("PivotTable1").AddDataField .ActiveSheet.PivotTables("PivotTable1").PivotFields("Amount"), "Sum of Amount", xlSum
                With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Amount")
                    .NumberFormat = "$#,##0.00"
                End With
                .Range("C3").Select
                .Selection.ShowDetail = False
                .ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
                .ActiveSheet.PivotTables("PivotTable1").Format xlTable1
                .Sheets("Sheet1").Name = "Pivot Table"
                .ActiveWorkbook.Save
                .Application.Quit
            End With
            Set xlSheet = Nothing
            Set xlBook = Nothing
            Set xlApp = Nothing
        End If
    End Sub
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    I will start using yours now, and credit you for it too!
    Lol - has Rudy mentioned my little sulk when he didn't credit me for a solution he posted on another board or something?

    Your code looks pretty tight Just thrown in a few some small IMHO improvements

    Code:
    Sub ExportProjectSpendReport()
    'Error Trap - always advised with automation
    On Error Goto Whoopsie
     
        Dim ExportFile As String
        Dim DefaultFile As String
        Dim DataSetName As String
     
    'Might as well call them what they are so VB knows from the start
       Dim xlApp As Excel.Application
       Dim xlBook As Excel.Workbook
       Dim xlSheet As Excel.Worksheet
     
        DataSetName = "Project Spend Report_export"
        DefaultFile = GetSetting(AppName, "Application", DataSetName)
        ExportFile = SaveFileDialog(DefaultFile, "Export Destination", "*.xls", "XLS", False)
        If Len(ExportFile) > 0 Then
     
    'Nod to Teddy
           'If Right(ExportFile, 4) <> ".xls" Then ExportFile = ExportFile & ".xls"
           If Right$(ExportFile, 4) <> ".xls" Then ExportFile = ExportFile & ".xls"
     
            SaveSetting AppName, "Application", DataSetName, ExportFile
            DoCmd.OutputTo acOutputQuery, DataSetName, acSpreadsheetTypeExcel9, ExportFile
            Set xlApp = New Excel.Application
            Set xlBook = xlApp.Workbooks.Open(ExportFile)
            Set xlSheet = xlBook.worksheets(1)
            With xlApp
                .Application.Visible = True
     
    'If your app is visible while you format (I think it should be too for the Showboating Factor :D ) remember to set the below:
                .Interactive = False
    'Before you set the above, try clicking on the worksheet and changing tabs while the formatting code runs - it will probably error.
    'That's why I turn off user interaction while the code is running
     
                .Range("A1").Select
                .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
                .ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=.Selection).CreatePivotTable TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
                .ActiveSheet.PivotTableWizard TableDestination:=.ActiveSheet.Cells(3, 1)
                With .ActiveSheet.PivotTables("PivotTable1").PivotFields("CAR")
                    .Orientation = xlRowField
                    .Position = 1
                End With
                With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Type")
                    .Orientation = xlRowField
                    .Position = 2
                End With
                With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
                    .Orientation = xlColumnField
                    .Position = 1
                End With
                With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Fiscal Year")
                    .Orientation = xlColumnField
                    .Position = 1
                End With
                .ActiveSheet.PivotTables("PivotTable1").AddDataField .ActiveSheet.PivotTables("PivotTable1").PivotFields("Amount"), "Sum of Amount", xlSum
                With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Amount")
                    .NumberFormat = "$#,##0.00"
                End With
                .Range("C3").Select
                .Selection.ShowDetail = False
                .ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
                .ActiveSheet.PivotTables("PivotTable1").Format xlTable1
                .Sheets("Sheet1").Name = "Pivot Table"
                .ActiveWorkbook.Save
     
    'Now we have an error trap, stick in there.
               '.Application.Quit
     
            End With
     
        End If
     
    ExitSub:
    'We don't want an error in the trap otherwise we end up in an infinite loop
    On Error Resume Next
     
    'MUST be in the error trap
    xlApp.Interactive = True
     
    xlApp.Quit
     
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
     
    Exit Sub
     
    Whoopsie:
     
    'Most automation errors are singularly uninformatative but for good form
    MsgBox err.description
     
    Resume ExitSub
     
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    Also, with the reference set you can find out the constants by simply typing
    Code:
    ?xlDatabase

    smacks forhead

    BEST. TRICK. EVAAR!!

    I'm not going to divulge how many times I've gone diving into documentation looking for the long values of a given constant. Hey McFly, perhaps system constants are debugged JUST LIKE ANY OTHER CONSTANT. Oh yeah...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Too cool for school.

    The best thing about dbforums is that allows each of us to look like a genius in everybody else's area of expertise.

    Thanks for your help. One last thing: what does this get me?: "Right$(ExportFile, 4)" Couldn't find any reference for it in Help file.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Its just chopping the last 4 characters, similar to left & mid
    avoids have to mess around with string lengths to find extract the characters

    if exportfile was "blindman.xls"
    Right$(ExportFile, 4) return ".xls"

    functionally its the same as mid$(ExportFile,len(ExportFile,4))
    Last edited by healdem; 01-26-06 at 12:23.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Blimey - I will walking tall tonight - helped out two v. senior members in one thread

    Quote Originally Posted by blindman
    One last thing: what does this get me?: "Right$(ExportFile, 4)" Couldn't find any reference for it in Help file.
    I'll leave that one to Teddy - check out his sig - I think it is fair to say he has made this one his own
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    Its just chopping the last 4 characters, similar to left & mid
    avoids have to mess around with string lengths to find extract the characters

    if exportfile was "blindman.xls"
    Right$(ExportFile, 4) return ".xls"

    functionally its the same as mid$(ExportFile,len(ExportFile,4))
    I think he is asking why:

    Right<> Right$
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •