How can I add values to a closed excel file? |
|
Author |
Message |
MarkAnderson
|
Posted: Fri Jun 10 13:41:16 CDT 2005 |
Top |
worksheet functions >> How can I add values to a closed excel file?
Hi, if someone can please tell me how I can add values to closed excel file
from an open excel file. What needs to be done here.
Thanks.
Excel348
|
|
|
|
|
PeoSjoblom
|
Posted: Fri Jun 10 13:41:16 CDT 2005 |
Top |
worksheet functions >> How can I add values to a closed excel file?
You can't
Regards,
Peo Sjoblom
> Hi, if someone can please tell me how I can add values to closed excel file
> from an open excel file. What needs to be done here.
>
>
> Thanks.
|
|
|
|
|
Andy
|
Posted: Fri Jun 10 14:11:05 CDT 2005 |
Top |
worksheet functions >> How can I add values to a closed excel file?
You can!
.. and here's how:
http://www.bygsoftware.com/Excel/SQL/update_using_sql.htm
Using an UPDATE query in SQL.REQUEST to change values in closed workbooks.
How to COUNT data in an Excel table and in an MsAccess database using
SQL.REQUEST.
--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
> You can't
>
>
> Regards,
>
> Peo Sjoblom
>
>
> > Hi, if someone can please tell me how I can add values to closed excel
file
> > from an open excel file. What needs to be done here.
> >
> >
> > Thanks.
|
|
|
|
|
George
|
Posted: Fri Jun 10 14:40:43 CDT 2005 |
Top |
worksheet functions >> How can I add values to a closed excel file?
Andy:
Out of curiousity, do those allow you to actually "add values" like the OP
asked, or just change/update existing values?
--
George Nicholson
Remove 'Junk' from return address.
> You can!
>
> .. and here's how:
>
> http://www.bygsoftware.com/Excel/SQL/update_using_sql.htm
>
> Using an UPDATE query in SQL.REQUEST to change values in closed workbooks.
> How to COUNT data in an Excel table and in an MsAccess database using
> SQL.REQUEST.
>
> --
> Andy Wiggins FCCA
> www.BygSoftware.com
> Excel, Access and VBA Consultancy
> -
>
>> You can't
>>
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> > Hi, if someone can please tell me how I can add values to closed excel
> file
>> > from an open excel file. What needs to be done here.
>> >
>> >
>> > Thanks.
>
>
|
|
|
|
|
Andy
|
Posted: Fri Jun 10 14:39:36 CDT 2005 |
Top |
worksheet functions >> How can I add values to a closed excel file?
It uses the Sql UPDATE functionality, therefore it updates cells. If you
want to add a new value to the current value in a cell then you would need
to obtain the current value, add the new value and then update the target
cell(s).
--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
> Andy:
>
> Out of curiousity, do those allow you to actually "add values" like the OP
> asked, or just change/update existing values?
>
> --
> George Nicholson
>
> Remove 'Junk' from return address.
>
>
> > You can!
> >
> > .. and here's how:
> >
> > http://www.bygsoftware.com/Excel/SQL/update_using_sql.htm
> >
> > Using an UPDATE query in SQL.REQUEST to change values in closed
workbooks.
> > How to COUNT data in an Excel table and in an MsAccess database using
> > SQL.REQUEST.
> >
> > --
> > Andy Wiggins FCCA
> > www.BygSoftware.com
> > Excel, Access and VBA Consultancy
> > -
> >
> >> You can't
> >>
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >>
> >> > Hi, if someone can please tell me how I can add values to closed
excel
> > file
> >> > from an open excel file. What needs to be done here.
> >> >
> >> >
> >> > Thanks.
> >
> >
>
>
|
|
|
|
|
PeoSjoblom
|
Posted: Fri Jun 10 15:02:01 CDT 2005 |
Top |
worksheet functions >> How can I add values to a closed excel file?
It's not built in though, as far as I know you need to install it and also if
you have excel 2003 you need to find the 2002 version since it does not come
with Office/Excel 2003 so given that, for the next version of office it will
probably not work unless they include a new add-in
Regards
Peo Sjoblom
> You can!
>
> ... and here's how:
>
> http://www.bygsoftware.com/Excel/SQL/update_using_sql.htm
>
> Using an UPDATE query in SQL.REQUEST to change values in closed workbooks.
> How to COUNT data in an Excel table and in an MsAccess database using
> SQL.REQUEST.
>
> --
> Andy Wiggins FCCA
> www.BygSoftware.com
> Excel, Access and VBA Consultancy
> -
>
> > You can't
> >
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > > Hi, if someone can please tell me how I can add values to closed excel
> file
> > > from an open excel file. What needs to be done here.
> > >
> > >
> > > Thanks.
>
>
>
|
|
|
|
|
Andy
|
Posted: Fri Jun 10 15:11:09 CDT 2005 |
Top |
worksheet functions >> How can I add values to a closed excel file?
As the OP asked, "What needs to be done here", and that's the question I
answered. (I'm starting to sound like a pedant)
As to the XLODBC add-in ...
http://www.bygsoftware.com/Excel/sqlrequest.html
Q: I can't find the XLODBC add-in. I'm using Excel 2002
A: This add-in is no longer included with Microsoft Excel. You can get it
from Microsoft by following this link -
http://office.microsoft.com/downloads/2002/xlodbc.aspx
Q: I can't find the XLODBC add-in. I'm using Excel 2003. I've tried the
above link but that says it can't find Excel 2002 or Excel XP.
A: This is where Uncle Bill isn't being too helpful :-( But, here is a way
to get to those files:
Open the xlodbc.exe using, say, Winzip.
Copy the files to a convenient location.
From within Excel, browse to the add-in file and load it as normal.
--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
> It's not built in though, as far as I know you need to install it and also
if
> you have excel 2003 you need to find the 2002 version since it does not
come
> with Office/Excel 2003 so given that, for the next version of office it
will
> probably not work unless they include a new add-in
>
>
> Regards
>
>
> Peo Sjoblom
>
>
>
> > You can!
> >
> > ... and here's how:
> >
> > http://www.bygsoftware.com/Excel/SQL/update_using_sql.htm
> >
> > Using an UPDATE query in SQL.REQUEST to change values in closed
workbooks.
> > How to COUNT data in an Excel table and in an MsAccess database using
> > SQL.REQUEST.
> >
> > --
> > Andy Wiggins FCCA
> > www.BygSoftware.com
> > Excel, Access and VBA Consultancy
> > -
> >
> > > You can't
> > >
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > >
> > > > Hi, if someone can please tell me how I can add values to closed
excel
> > file
> > > > from an open excel file. What needs to be done here.
> > > >
> > > >
> > > > Thanks.
> >
> >
> >
|
|
|
|
|
George
|
Posted: Fri Jun 10 15:21:37 CDT 2005 |
Top |
worksheet functions >> How can I add values to a closed excel file?
Assume I have an excel file/sheet with 500 records and wanted to add 75
additional records (leaving the original 500 untouched)....?
--
George Nicholson
Remove 'Junk' from return address.
> It uses the Sql UPDATE functionality, therefore it updates cells. If you
> want to add a new value to the current value in a cell then you would need
> to obtain the current value, add the new value and then update the target
> cell(s).
>
> --
> Andy Wiggins FCCA
> www.BygSoftware.com
> Excel, Access and VBA Consultancy
> -
>
>> Andy:
>>
>> Out of curiousity, do those allow you to actually "add values" like the
>> OP
>> asked, or just change/update existing values?
>>
>> --
>> George Nicholson
>>
>> Remove 'Junk' from return address.
>>
>>
>> > You can!
>> >
>> > .. and here's how:
>> >
>> > http://www.bygsoftware.com/Excel/SQL/update_using_sql.htm
>> >
>> > Using an UPDATE query in SQL.REQUEST to change values in closed
> workbooks.
>> > How to COUNT data in an Excel table and in an MsAccess database using
>> > SQL.REQUEST.
>> >
>> > --
>> > Andy Wiggins FCCA
>> > www.BygSoftware.com
>> > Excel, Access and VBA Consultancy
>> > -
>> >
>> >> You can't
>> >>
>> >>
>> >> Regards,
>> >>
>> >> Peo Sjoblom
>> >>
>> >>
>> >> > Hi, if someone can please tell me how I can add values to closed
> excel
>> > file
>> >> > from an open excel file. What needs to be done here.
>> >> >
>> >> >
>> >> > Thanks.
>> >
>> >
>>
>>
>
>
|
|
|
|
|
Andy
|
Posted: Fri Jun 10 19:13:05 CDT 2005 |
Top |
worksheet functions >> How can I add values to a closed excel file?
You can't use SQL.REQUEST to achieve this because it's a function.
Here is a way using SQL code:
It demonstrates two methods for inserting records into a CLOSED Excel
workbook.
For this demonstration I have used, as the target workbook,
SqlRequest_COUNT.xls
http://www.bygsoftware.com/examples/zipfiles/SqlRequest_COUNT.zip
This workbook contains a range called LocalTable. It is two (2) coumns wide.
Copy the code, below, into a VBA module.
The SQL code in Method 1 INSERTS a single record into the table.
The SQL code in Method 2 INSERTS multiple rows from a table in the source
workbook.
The source table, called MyTable, must also be two columns wide.
You may have to adjust the paths in the connection string depending on where
you save the source workbook.
This works for hundreds of records.
'' Code begins here - - - - - - - - - - - - - -
Option Explicit
Sub InsertValuesIntoClosedExcelWorkbook()
Dim lStr_Conn As String
Dim lStr_Sql As String
lStr_Conn = ""
lStr_Conn = lStr_Conn & "ODBC;DSN=Excel Files;"
lStr_Conn = lStr_Conn & "DBQ=C:\Demo\SqlRequest_COUNT.xls;"
lStr_Conn = lStr_Conn & "DefaultDir=C:\Demo;"
lStr_Conn = lStr_Conn & "DriverId=790;"
lStr_Conn = lStr_Conn & "MaxBufferSize=2048;"
lStr_Conn = lStr_Conn & "PageTimeout=5;"
'' Method 1
'' lStr_Sql = ""
'' lStr_Sql = lStr_Sql & " INSERT INTO LocalTable"
'' lStr_Sql = lStr_Sql & " VALUES (555,5)"
'' Method 2
lStr_Sql = ""
lStr_Sql = lStr_Sql & " INSERT INTO LocalTable"
lStr_Sql = lStr_Sql & " SELECT *"
lStr_Sql = lStr_Sql & " FROM `" & ThisWorkbook.FullName & "`.MyTable"
With ActiveSheet.QueryTables.Add(Connection:=lStr_Conn,
Destination:=Range("A1"))
.CommandText = lStr_Sql
.Name = "Query from Excel Files"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh
End With
End Sub
'' Code ends here - - - - - - - - - - - - - -
--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
> Assume I have an excel file/sheet with 500 records and wanted to add 75
> additional records (leaving the original 500 untouched)....?
>
> --
> George Nicholson
>
> Remove 'Junk' from return address.
>
>
> > It uses the Sql UPDATE functionality, therefore it updates cells. If you
> > want to add a new value to the current value in a cell then you would
need
> > to obtain the current value, add the new value and then update the
target
> > cell(s).
> >
> > --
> > Andy Wiggins FCCA
> > www.BygSoftware.com
> > Excel, Access and VBA Consultancy
> > -
> >
> >> Andy:
> >>
> >> Out of curiousity, do those allow you to actually "add values" like the
> >> OP
> >> asked, or just change/update existing values?
> >>
> >> --
> >> George Nicholson
> >>
> >> Remove 'Junk' from return address.
> >>
> >>
> >> > You can!
> >> >
> >> > .. and here's how:
> >> >
> >> > http://www.bygsoftware.com/Excel/SQL/update_using_sql.htm
> >> >
> >> > Using an UPDATE query in SQL.REQUEST to change values in closed
> > workbooks.
> >> > How to COUNT data in an Excel table and in an MsAccess database using
> >> > SQL.REQUEST.
> >> >
> >> > --
> >> > Andy Wiggins FCCA
> >> > www.BygSoftware.com
> >> > Excel, Access and VBA Consultancy
> >> > -
> >> >
> >> >> You can't
> >> >>
> >> >>
> >> >> Regards,
> >> >>
> >> >> Peo Sjoblom
> >> >>
> >> >>
> >> >> > Hi, if someone can please tell me how I can add values to closed
> > excel
> >> > file
> >> >> > from an open excel file. What needs to be done here.
> >> >> >
> >> >> >
> >> >> > Thanks.
> >> >
> >> >
> >>
> >>
> >
> >
>
>
|
|
|
|
|
|
|