Modify Excel Spreadsheet from Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 said:
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
Click to expand...

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.

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

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


Jamie Collins said:
Alex Dybenko said:
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
Click to expand...

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.

--
Click to expand...
 
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!

Jamie Collins said:
Alex Dybenko said:
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
Click to expand...

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.
Click to expand...
 
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


confused said:
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!

Jamie Collins said:
Alex Dybenko said:
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
Click to expand...

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.
Click to expand...
Click to expand...
 
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$]
Click to expand...

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.

--
 
Back
Top