Auto Run "Import Database" upon open file

Beatrice

Board Regular
Joined
Sep 17, 2019
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
The Excel file will be used as a form, user not allow to use "Alt + F11"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top