Running Excel Macro in the Background

Hi guys,

I am a complete noob at excel vba and i need some help with a problem that i am facing.

I am trying to write a macro/add-in which copies data from a excel file and paste's it into another excel file which is in the shared drive, I am able to perform these actions, but my only problem here is that since the file that i am trying to access is on a server, it takes a lot of time to do these operations and during this time, i cant work on excel, so i wanted to know if there is anyway to run the macro in the background.

I wanted to know if there is any method by which i could just copy the data from the source file and then perform the pasting opertaion in the background. Or if i could generate some kind of script of it using vba which could run in the background.

Please advise.

Thanks a lot

Hi,

I am able to perform these actions

Post the code you are using

Here is the code

Application.ScreenUpdating=False
Range("A1").Copy
Set master_wb = Workbooks.Open("https://<Shared drive Loaction>/<File Name>.xlsx")
master_wb.Sheets("Sheet1").PasteSpecial
Workbooks("Master_file.xlsx").Close True


The copying and pasting options dont take mush time...but opening the file from the shared location takes a lot of time during which excel is unusable.

how often is data being entered into the front file? every 5 mins? 10 mins? how long between the user finishing entering data and them starting entering the next set of data?

i use this code to save a workbook 5 mins after the user has stopped moving the cursor:

in ThisWorkbook:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'
' Macro recorded 22/06/2010 by Ian Abbott
'
'as long as the workbook isn't readonly or a template then this will call the autosave macro

If ActiveWorkbook.ReadOnly = False Then

Call AutoSaveTimer

End If

End Sub

in a normal module:
Sub AutoSaveTimer()

'setup the timer to run the AutoSave macro in 5 mins
'this procedure will be called every time the cell is changed
'the existing timer will then be stopped and a new one set, so the timesheet
'will be saved 5 minutes after the user stops inputting data
'
Application.EnableEvents = False

Public nextTime As Double

On Error Resume Next

'cancel the currently scheduled autosave
Application.OnTime nextTime, "AutoSave", , False

'get the time 5 mins from now
nextTime = TimeSerial(Hour(Now()), Minute(Now()), 0)
nextTime = nextTime + TimeSerial(0, 5, 0)

'schedule the AutoSave procedure to run in 5 mins
Application.OnTime nextTime, "AutoSave", , True

Application.EnableEvents = True
(so every time the cursor is moved, the currently scheduled macro call is cancelled, and a new one set up. you'll also need an Application.OnTime nextTime, "AutoSave", , False in ThisWorkbook-BeforeClose event)

this then calls the macro 'AutoSave' after 5 mins

see if you can adapt that to your purpose, while the user is not entering data or is away from the pc is the best time as minimal disruption is caused. unfortunately, opening workbooks will always take a long time, longer if it is a big file, and there is no way (that i know of) to be able to open the file without totally locking up excel

Thanks.... I dont think i'll be able to adpt my code to this, but I think this would be useful for me elsewhere.

I had a curiosity if VBA could generate some kind of .exe/windows script that runs in the background and does the pasting part...

Sorry i have one more query...I have a column in which i have set Data Validation which points to a list which has about some 3000 items..I wanted to know if there is any autocmplete feature that could be implemented...i have heard about combobox ading..but i wanted to know if there is any other way around....