Import Macro 2

[davjr]

I am having a problem trying to run a macro to transpose an Exel worksheet then import into MS-Access.

I have compiled an Exel macro which transposes the data to suit MS-Access this works fine.
I can then import the spreadsheet using MS-Access macro action "TransferSpreadsheet" this also works fine.

This requires 2 actions

The aim is to click on a command button in MS-Access,transpose the data in Exel then import worksheet into MS-Access in 1 action. Is this possible.

Thanks in advance
Davo

 

[GHTROUT]

Could the first macro contain a step "RunMacro" that called the next maro?

Forgive me - I am macro adept but code writing dumb... so maybe you are well beyond this idea



GHTROUT.com | FAQs | Recent Replies

 

[AlexCuse]

How comfortable are you with VBA? I think what you want to do is write your excel macro in such a way that it can be run from Access (this is not very hard in most cases). After macro runs, you can save the file, close it, and import it (the easy part).

Check this out, it might help get you started:

http://support.microsoft.com/default.aspx?kbid=260410

Good Luck,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[davjr]

I have found the following code and it opens the exel workbook (CustMacro.xls)which contains a macro (Autpen)to transpose layout to match MS Access fields. The macro does not auto run when the workbook is opened, The macro will auto run when I open the workbook from MS access using a hypolink or I open it from within exel can anyone explain why.

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

'Dimension the Variables
Dim xlApp As Excel.Application
Dim xlWkbk As Excel.Workbook
Dim xlSht As Excel.Worksheet

'Set the application
Set xlApp = New Excel.Application

'Make the Application Visible
xlApp.Visible = True

'Set the workbook and the filepath 'Change "C:\Book1.xls " to your own filepath and Workbook name
Set xlWkbk = xlApp.Workbooks.Open("C:\PTVC\Routing\CustMacro.xl s ")

'Set the worksheet that you want the workbook to open on
xlApp.Sheets("Sheet3").Select

'Turn everything off otherwise yo will have problems with your spreadsheet
Set xlApp = Nothing
Set xlWkbk = Nothing
Set xlSht = Nothing

Exit_Command16_Click:
Exit Sub

Thanks in advance
Davo

 

[PHV]

The macro does not auto run when the workbook is opened
...
Set xlWkbk = xlApp.Workbooks.Open("C:\PTVC\Routing\CustMacro.xls")
xlWkbk.RunAutoMacros xlAutoOpen
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davjr]

Thanks for the help PHV.

Another action I am trying to achieve without any success is closing The application of exel that I opened with my macro.

Is it possible to close the exel I opened without closing another opened exel application.

ie if I have a workbook "Expences" open then I open access and run my macro which will open workbook "Routing" can I close "Routing" without closing "Expences".

Thanks in Advance
davo

 

[PHV]

Have you tried this ?
xlWkbk.Close

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davjr]

Thanks PH for your quick responce and advise, I have tried this line in several locations in my code but to no success.

Thanks in Advance

davo

 

[PHV]

And this ?
xlWkbk.Close
Set xlWkbk = Nothing
xlApp.Quit
Set xlApp = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davjr]

Thanks again PH for your quick responce and advise.

I have inserted the as suggested still not closing, get the following error, "Automation error, the object invoked has disconnected from its clients".

Is the following what you are advising.

Private Sub ImpCust_Click()
On Error GoTo Err_ImpCust_Click

'Dimension the Variables
Dim xlApp As Excel.Application
Dim xlWkbk As Excel.Workbook

'Set the application
Set xlApp = New Excel.Application

'Make the Application Visible
xlApp.Visible = True

Set xlWkbk = xlApp.Workbooks.Open("C:\PTVC_InstMan\DailyRoute\CustMacro.xls ")
xlWkbk.RunAutoMacros xlAutoOpen

'Turn everything off otherwise you will have problems with your spreadsheet

xlWkbk.Close
Set xlWkbk = Nothing
xlApp.Quit
Set xlApp = Nothing

DoCmd.RunMacro "CustImport"

Exit_ImpCust_Click:
Exit Sub

Err_ImpCust_Click:
MsgBox Err.Description
Resume Exit_ImpCust_Click
xlWkbk.Close
End Sub

Thanks
davo

 

[PHV]

Which line of code raises the error ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davjr]

Deleted the following line all is working ok now, exel closes and no errors.

Is there a way to run exel in the background to avoid seeing the changes implemented by the macro.

THanks again PH for your help.

davo

 

[PHV]

Which line have you deleted ?
avoid seeing the changes implemented by the macro
xlApp.Visible = False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davjr]

Thanks PH

The line I deleted was xlWkbk.Close
error possibly because workbook is closed by the macro.

Hopefully one day I'll get to the point of not missing the obvious

Thanks again PH for all your help

davo