Link to home
Create AccountLog in
Avatar of alam747
alam747

asked on

How to fill data from one sheet of a excel file to specified sheet of another excel file using Access VBA

Would you please advice how to fill data from one excel sheet to specified sheet of another excel file. for example I have two excel file test01.xls  and test02.xls, I want to fill the data from specied cell of sheet 1 of test01.xls to specified cell of sheet 1 of test02.xls using Access VBA.

Your prompt response will be highly appreciated...
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

first you need to have two excel object variables defined and set in access environment to manipulate them remotely.

brb
Use the following code to make sure you are able to remotely access these files and open them. Later we can do with them whatever you have in mind.

Mike
Dim xl as Excel.Application
Dim wb_Source as Excel.Workbook
Dim wb_Target as Excel.Workbook
Dim ws_Source as Excel.Worksheet
Dim ws_Target as Excel.Worksheet

Set xl = CreateObject("Excel.Application")
Set wb_Source = xl.Workbooks.open("c:\Test01.xls")
Set wb_Target = xl.Workbooks.open("c:\Test02.xls")
Set ws_Source = wb_Source.Sheets("Sheet 1")
Set ws_Target = wb_Target .Sheets("Sheet 1")

xl.visible=true

'wb_Source .close   ' for later use
'wb_Target.close
'set xl=nothing
'set wb=nothing

Open in new window

correction...

Set ws_Source = wb_Source.Worksheets("Sheet1")
Set ws_Target = wb_Target.Worksheets("Sheet1")
       you may have to remove space before 1--^
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Sorry Change the line 24 and 25

    '~~> Get value from cell A1
    wbTest2.Range("A1").Value = wbTest1.Range("A1").Value

to this

    '~~> Get value from cell A1
    wbTest2.Sheets("Sheet1").Range("A1").Value = wbTest1.Sheets("Sheet1").Range("A1").Value

Open in new window


Sid
re:> set a reference to Microsoft Excel object library from VBA Menu ~~> Tools ~~> Reference

This has to be set regardkess what solution is used.

re:> This method is called Early Binding.

Both methods are using late binding. An example of early binding would be:

Dim xl As New Excel.Application   'early binding

in place of:

Dim xl As Excel.Application    
Set xl = CreateObject("Excel.Application")    'late binding

re:> oXLApp.Visible = False

Setting it to invisible is not a good idea at this stahe. One would change to invisible only after the code works as intended. Other wise with each error bugging atemp, the computer needs to be restarted to make sure the open (but hidden) files is closed.


re:> set a reference to Microsoft Excel object library from VBA Menu ~~> Tools ~~> Reference

This has to be set regardkess what solution is used.

With respect, eghtebas. That is not true. In latebinding you don't need to set a reference.

Sid
Dim xl As Excel.Application    
Set xl = CreateObject("Excel.Application")    'late binding

Also

This is not latebinding...

Sid
And Lastly,

Dim xl As Object
Set xl = CreateObject("Excel.Application")    'late binding

This is latebinding....

If you use

Dim xl As Excel.Application    
Set xl = CreateObject("Excel.Application")    'late binding

Open in new window


Without setting a reference then you will get an error.

Sid
I will test it to make sure one way or another. I appreciate fr the posts.

Mike
Avatar of alam747
alam747

ASKER

Thanks to both of you for the solution..
while it close the file it display an Automation error
"The object invoked has disconnected from its client.I appreciate any advice to fix the problem.
That's because you ignored my post ;)

ID: 34455974

Go through it once again...

Sid
And what is sad is that you gave a "A" class solution a "B"!!!!!

It's really sad....