Modify Excel Spreadsheet from Access?

[Guest]

Hi,

I am working with Access 2003 and have a button with an "on click" event
which copies the contents of a query to a new excel spreadsheet:
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strQuery & "]"
objDB.Close
Set objDB = Nothing

This works great, but is ther away to then make changes to the contents of
the spreadsheet (from the same on-click event in Access)?
EG
I want to add a date to one of the cells.
I also have abbreviations in the query, which I would like to change once in
the spreadsheet.

I have no idea where to begin or what to search for in the help files, so
any info would really be appreciated!

Thanks!!

 

[Alex Dybenko]

Yes, you can modify Excel sheet, but no so easy as export data. Browse to
support.microsoft.com and search for "Excel Application", you will get lot
of samples
you can try for example this one:
http://support.microsoft.com/default.aspx?scid=kb;en-us;129304&Product=acc

 

[Jamie Collins]

Yes, you can modify Excel sheet, but no so easy as export data. Browse to
support.microsoft.com and search for "Excel Application", you will get lot
of samples

What are you saying? That an UPDATE query harder to write, so don't
bother and use automation instead? It's not so hard e.g.

UPDATE
[Excel 8.0;HDR=YES,DATABASE=MyWorkbook$].[MyWorksheet$]
SET
MyCol1=55
;

Jamie.

--

 

[Alex Dybenko]

well, also nice method, it if fits to original poster needs - thats fine!

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Yes, you can modify Excel sheet, but no so easy as export data. Browse to
support.microsoft.com and search for "Excel Application", you will get
lot
of samples

What are you saying? That an UPDATE query harder to write, so don't
bother and use automation instead? It's not so hard e.g.

UPDATE
[Excel 8.0;HDR=YES,DATABASE=MyWorkbook$].[MyWorksheet$]
SET
MyCol1=55
;

Jamie.

--

 

[Guest]

Hi,

thanks to both for your help, but in this example, what would the syntax be
of MyCol1? Would I not need to specify a row and column number in this one
parameter, to change the cell in question?

thanks!

Yes, you can modify Excel sheet, but no so easy as export data. Browse to
support.microsoft.com and search for "Excel Application", you will get lot
of samples

What are you saying? That an UPDATE query harder to write, so don't
bother and use automation instead? It's not so hard e.g.

UPDATE
[Excel 8.0;HDR=YES,DATABASE=MyWorkbook$].[MyWorksheet$]
SET
MyCol1=55
;

Jamie.

 

[Alex Dybenko]

Hi,
this should be a column name you want to update.
same names when you make select * from Excel
8.0;HDR=YES,DATABASE=MyWorkbook$].[MyWorksheet$]


--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Hi,

thanks to both for your help, but in this example, what would the syntax
be
of MyCol1? Would I not need to specify a row and column number in this one
parameter, to change the cell in question?

thanks!

Yes, you can modify Excel sheet, but no so easy as export data. Browse
to
support.microsoft.com and search for "Excel Application", you will get
lot
of samples

What are you saying? That an UPDATE query harder to write, so don't
bother and use automation instead? It's not so hard e.g.

UPDATE
[Excel 8.0;HDR=YES,DATABASE=MyWorkbook$].[MyWorksheet$]
SET
MyCol1=55
;

Jamie.

 

[Jamie Collins]

this should be a column name you want to update.
same names when you make select * from Excel
8.0;HDR=YES,DATABASE=MyWorkbook$].[MyWorksheet$]

If the OP wants to update a single cell using its range address, say
cell B4, the syntax would be:

UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MyWorksheet$B4:B4]
SET
F1=55
;

When headers are not used (or header names are invalid), Jet assigns
the names as F1, F2, F3 etc.

Jamie.

--