Solved

Transfer Spreadsheet - call macro first

Posted on 2016-10-31
2
13 Views
Last Modified: 2016-10-31
Experts, I want to call an excel macro before transferring the spreadsheet.  How could I do this?

name of macro:  "Transpose"
Private Sub cmdImport_Click()

         Dim strName As String

            CurrentDb.Execute "delete * from [Import_FC]"
            strName = "Forecast"

   DoCmd.TransferSpreadsheet acImport, , "Import_FC", "C:\Users\johnsoat\Desktop\Import_FC.xlsm", True, strName & "!"

Open in new window



End Sub
0
Comment
Question by:pdvsa
2 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41866706
Hi,

pls try

Private Sub cmdImport_Click()

         Dim strName As String
         Dim xl As Object
            Set xl = CreateObject("Excel.Application")
            xl.Workbooks.Open ("C:\Users\johnsoat\Desktop\Import_FC.xlsm")
            xl.Run "MyMacro"
            xl.ActiveWorkbook.Close (True)
            xl.Quit
            Set xl = Nothing
            CurrentDb.Execute "delete * from [Import_FC]"
            strName = "Forecast"

   DoCmd.TransferSpreadsheet acImport, , "Import_FC", "C:\Users\johnsoat\Desktop\Import_FC.xlsm", True, strName & "!"

End Sub

Open in new window

Regards
0
 

Author Closing Comment

by:pdvsa
ID: 41866751
Perfect.  thank you!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

708 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

13 Experts available now in Live!

Get 1:1 Help Now