trouble importing AND exporting between access and Excel 
Author Message
 trouble importing AND exporting between access and Excel

I have a DB which imports data from some two spreadsheets
through the link approach.  One of these spreadsheets
also exports data to access.

The problem I have is that if I just want to put the
input data in Access (via a form) and have it exported to
excel it works.  I go into excel and refresh the range.

When I just have access link to a spreadheet that also
works.  

BUT when I try to do both, that is input data in access,
go to excel (if I can even open the file, sometimes I get
errors) refresh it, the output from the excel file never
gets bak to Acccess.  I also get all sorts of errors
after I close saying the excel file is available for read
write.



Thu, 27 Oct 2005 00:11:58 GMT  
 trouble importing AND exporting between access and Excel
jonathan

Sounds to me that the Excel workbook is not set up for sharing.  If Access
has the file open, you won't be able to open it yourself except as
read-only,  when the Access handle on the file is released, Excel generates
the message stating that the file is now available for read/write access.

Open the workbook in Excel and choose Tools > Share Workbook, check the
'Share...'option on the Share Tab and then click the Advanced Tab to set
options on data sharing options.

HTH

Andy


Quote:
> I have a DB which imports data from some two spreadsheets
> through the link approach.  One of these spreadsheets
> also exports data to access.

> The problem I have is that if I just want to put the
> input data in Access (via a form) and have it exported to
> excel it works.  I go into excel and refresh the range.

> When I just have access link to a spreadheet that also
> works.

> BUT when I try to do both, that is input data in access,
> go to excel (if I can even open the file, sometimes I get
> errors) refresh it, the output from the excel file never
> gets bak to Acccess.  I also get all sorts of errors
> after I close saying the excel file is available for read
> write.



Thu, 27 Oct 2005 08:01:52 GMT  
 trouble importing AND exporting between access and Excel
Thanks. That helps the avail for read-write pop ups but
the original problem with the forms still exists.

I make a change in a form, go to excel and hit refresh to
update the values. that works fine. but the data that
excel shoots back to access still doesnt work unless I
close access, then close excel and then reopen access.
Any thoughts? Is there a similar share control in access
perhahps or is the problem something different?

Quote:
>-----Original Message-----
>jonathan

>Sounds to me that the Excel workbook is not set up for
sharing.  If Access
>has the file open, you won't be able to open it yourself
except as
>read-only,  when the Access handle on the file is

released, Excel generates
Quote:
>the message stating that the file is now available for
read/write access.

>Open the workbook in Excel and choose Tools > Share
Workbook, check the
>'Share...'option on the Share Tab and then click the
Advanced Tab to set
>options on data sharing options.

>HTH

>Andy



>> I have a DB which imports data from some two
spreadsheets
>> through the link approach.  One of these spreadsheets
>> also exports data to access.

>> The problem I have is that if I just want to put the
>> input data in Access (via a form) and have it exported
to
>> excel it works.  I go into excel and refresh the range.

>> When I just have access link to a spreadheet that also
>> works.

>> BUT when I try to do both, that is input data in
access,
>> go to excel (if I can even open the file, sometimes I
get
>> errors) refresh it, the output from the excel file
never
>> gets bak to Acccess.  I also get all sorts of errors
>> after I close saying the excel file is available for
read
>> write.

>.



Thu, 27 Oct 2005 09:36:56 GMT  
 trouble importing AND exporting between access and Excel
I believe that operation is by design. Generally, when you have the Excel
file as a linked table in Access and you have the form open you are in
essence opening the Excel spreadsheet. Therefore, you can't have the Excel
spreadsheet open until you close down Access and release the linked file. By
the same token in order for the Excel spreadsheet to update Access, the form
must be closed down or you issue a requery to show the new record if the
form is open.


Quote:
> Thanks. That helps the avail for read-write pop ups but
> the original problem with the forms still exists.

> I make a change in a form, go to excel and hit refresh to
> update the values. that works fine. but the data that
> excel shoots back to access still doesnt work unless I
> close access, then close excel and then reopen access.
> Any thoughts? Is there a similar share control in access
> perhahps or is the problem something different?

> >-----Original Message-----
> >jonathan

> >Sounds to me that the Excel workbook is not set up for
> sharing.  If Access
> >has the file open, you won't be able to open it yourself
> except as
> >read-only,  when the Access handle on the file is
> released, Excel generates
> >the message stating that the file is now available for
> read/write access.

> >Open the workbook in Excel and choose Tools > Share
> Workbook, check the
> >'Share...'option on the Share Tab and then click the
> Advanced Tab to set
> >options on data sharing options.

> >HTH

> >Andy



> >> I have a DB which imports data from some two
> spreadsheets
> >> through the link approach.  One of these spreadsheets
> >> also exports data to access.

> >> The problem I have is that if I just want to put the
> >> input data in Access (via a form) and have it exported
> to
> >> excel it works.  I go into excel and refresh the range.

> >> When I just have access link to a spreadheet that also
> >> works.

> >> BUT when I try to do both, that is input data in
> access,
> >> go to excel (if I can even open the file, sometimes I
> get
> >> errors) refresh it, the output from the excel file
> never
> >> gets bak to Acccess.  I also get all sorts of errors
> >> after I close saying the excel file is available for
> read
> >> write.

> >.



Thu, 27 Oct 2005 13:09:24 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Importing/Exporting data from a custom Outlook 97 Form into an Excel Spreadsheet

2. importing/exporting excel to vb5

3. Importing/Exporting HTML <-> Excel

4. how to write a VB to import and export data to/from SQL2K to Excel

5. how write a vb to import from and export data to excel fromm SQL sever

6. LDIF-file-import to Excel and prepare data for VBA-ADSI-Export

7. how write a vb to import from and export data to excel fromm SQL sever

8. how to write a VB to import and export data to/from SQL2K to Excel

9. Excel Import/Export

10. Import/Export to named ranges in Excel from VB5

11. Trouble accessing access dbase data from excel.

12. Trouble importing into Access 2.0 after installing VB4.0

 

 
Powered by phpBB® Forum Software