Auto Run "Import Database" upon open file

Beatrice

New Member
Joined
Sep 17, 2019
Messages
18
I need some help here as still learning Excel Macros

I have few sets of code within an excel file.
1) clear "results" data if "input" criteria has any change
2) Export "results" as a separated file in values only - button
3) Import "Database"- button

So far, I tested them separately, all are working.
However, I would like to run the "Import Database" without a button

Here is my trial:
--------------------------------------------
Sub Auto_Open()

MsgBox "Please allow a few second for importing..."

' Open Source and Destination files

ChDir "S:\Project"
Workbooks.Open Filename:="S:\Project\Database.xlsx"

' Copy Dedicated Part

Workbooks("Database.xlsx").Sheets("Database").Range("A1:K50").Copy
Workbooks("Export.xlsm").Sheets("Database").Range("A1").PasteSpecial xlPasteValues

Application.CutCopyMode = False

Windows("Database.xlsx").Activate
ActiveWindow.Close

End Sub
-------------------------------------

but it is not running automatically as expect, can someone tell me what did I do wrong?
Thanks in advance for your help.
 

Beatrice

New Member
Joined
Sep 17, 2019
Messages
18
The Excel file will be used as a form, user not allow to use "Alt + F11"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,806
Office Version
2007
Platform
Windows
The macro works for me.

Or

ThisWorkbook EVENT
- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the above code.
- Save the workbook as a macro-enabled file, close it and then re-open it and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Code:
Private Sub [COLOR=#0000ff]Workbook_Open[/COLOR]()
  MsgBox "Please allow a few second for importing..."
  ' Open Source and Destination files
  ChDir "S:\Project"
  Workbooks.Open Filename:="S:\Project\Database.xlsx"
  ' Copy Dedicated Part
  Workbooks("Database.xlsx").Sheets("Database").Range("A1:K50").Copy
  Workbooks("Export.xlsm").Sheets("Database").Range("A1").PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Windows("Database.xlsx").Activate
  ActiveWindow.Close
End Sub
 

Forum statistics

Threads
1,077,984
Messages
5,337,529
Members
399,154
Latest member
gavlink

Some videos you may like

This Week's Hot Topics

Top