Excel vba to transfer data to closed excel file

[KUYJS]

Hi Team,

Is there a way to transfer data from one excel file to other closed excel file. Like we do from excel to access without opening access file/database by creating connection.

I tried to create connection between two excel file. Its working fine for extracting data without opening main excel file but giving vba automation error in transfering data to same file without opening it.

Kindly help
Tarun

 

[Fazza]

Siang Tarun

Yes, it is routine: just like working with Access.
Maybe post the code you're using and a couple of lines of sample data. And the Excel version.
Are you adding to existing table/worksheet, or inserting a new worksheet?

There is/was an excellent tutorial article on the subject by Herilane at xtremevbtalk.com from ~2005
And google will find many more

regards, Fazza

 

[Fazza]

from a glance, this looks OK
https://support.microsoft.com/en-au...-to-use-ado-to-read-and-write-data-in-excel-w

 

[KUYJS]

Thanks Fazza for your time and sharing the tutorial. Totorial seems helpfull, i will try it however didn't understand completely.

I am uaing excel 2016 and transfering data to existing file. It's like work is allocated in a workbook named 'Allocation.xlsx'in sheet named 'Hire' with task name in column A (pre filled), emp id in column B (pre filled) and task status in column C (blank and employee will update this column). 50 employees are working on this file. They all will have this tool named 'interface' on their desktop. When they will click on 'Get new' button, one task will get extracted from 'Allocation.xlsx' to their interface. Till here rool is working fine.

Problem starts here- After working on it, they will click on 'Save' button, now this entry should go back to same location where it was in 'Allocation.xlsx' with status in task status column.

 

[Fazza]

sample

Sub test()

    Const sCONN As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Allocation.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    
    Dim sSQL As String
    Dim objRS As Object
    
    'Make the SQL for your situation
    sSQL = Join$(Array("UPDATE [Hire$]", _
        "SET TaskStatus = 'Complete'", _
        "WHERE TaskName = 'alpha' AND EmpID = 2"), vbCr)
     
    Set objRS = CreateObject("ADODB.Recordset")
    objRS.Open sSQL, sCONN
    Set objRS = Nothing


End Sub

 

[KUYJS]

Hi Fazza,
Thank you for your help. Data transmission is working fine with this but some times it is opening target file where i am updating data. And this is happening randomly.

Secondly just want to understand why you used Const sCONN as string and not sCONN as string. This is for my knowledge only, what is the use of Const here

 

[Fazza]

I don't understand about the target file opening. You'll need to provide full information for me to comment on that.

sCONN as constant because it is a constant. The code is never changing its value.

Unlike the SQL which could be created/edited/changed within the code. Such as by getting inputs from cells or inputbox.

 

[KUYJS]

File where i am updating data, as per above code it is Allocation.xlsx

 

[KUYJS]

Currently the only problem i am facing here is: when more than one person click to get or save button, it throws error. How to handle it with multiple people at same time

 

[Fazza]

I don't know. Maybe try different cursor type (so you have more control)? Maybe some coding to identify the error and wait a second before re-trying - I'm guessing the file access limitation is very brief. If updates take a long time (so more than a second) then a more complex version of the same approach.

Maybe best to change from storing in an Excel file to storing in an mdb file. You can have an mdb file without MS Access. (mdb file can be made from Excel VBA. I did have a post giving an example of that ~10 years ago)

Maybe do some googling. This will have been addressed by others in the past. If all that fails, start a new thread (& include descriptions of what you have tried).