Updating a field based on data from a specific cell in excel

[SuzyQ]

Below is a snippet of code that I need help finishing. One of the fields in
my AccountNo table stores the cell location to retrieve data from an excel
spreadsheet. The data stored looks like this "Sheet1!K5" i.e. the sheet name
and cell location. I am also getting the path and name of spreadsheet and
storing it in the variable strDocName. I want to loop through a set of
records and update a specific field using the cell reference stored in
rs.ExcelCell from the spreadsheet location stored in strDocName. Can I
reference the table and cell using the data stored as mentioned? If so, what
is my statement going to look like? See code below. Thanks for all your
help.


sql = "SELECT AccountNo.FinancialReport,
AccountNo.ActivitySummaryReport, "
sql = sql & "FiscalYearBudget.[Acct No], FiscalYearBudget.FiscalYear, "
sql = sql & "FiscalYearBudget.ActualBudgetAmt, AccountNo.ExcelCell "
sql = sql & "FROM AccountNo INNER JOIN FiscalYearBudget ON "
sql = sql & "AccountNo.[Acct No] = FiscalYearBudget.[Acct No] "
sql = sql & "WHERE AccountNo.FinancialReport = True And "
sql = sql & "AccountNo.ActivitySummaryReport = False And "
sql = sql & "FiscalYearBudget.FiscalYear = " & Me.cmbFiscalYear

'update from spreadsheet
Set rs = CurrentDb.OpenRecordset(sql)

'stDocName = the path and file name of the spreadsheet

Do While Not rs.EOF
sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & "I NEED HELP
WITH THIS CODE RIGHT HERE"
sql = sql & "WHERE FiscalYearBudget.[Acct No]= " & rs![Acct No]
sql = sql & "AND FiscalYearBudget.FiscalYear)= " & Me.cmbFiscalYear


If Not rs.EOF Then
rs.MoveNext
End If
Loop

 

[SuzyQ]

I was rereading my post and realized that it may have been a little unclear,
and I had a typo in the code. The excel filename stored in strDocName and the
excel cell location stored in AccountNo.ExcelCell which can be accessed via
the open record set rs.ExcelCell. How do I use that information to reference
the data stored in the spreadsheet at that specific cell location to update
the value stored in FiscalYearBudget.ActualBudgetAmt?

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & "REFERENCE
THE EXCEL CELL RIGHT HERE TO UPDATE FIELD"
sql = sql & "WHERE FiscalYearBudget.[Acct No]= " & rs![Acct No]
sql = sql & "AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

docmd.RunSQL(sql)

 

[SuzyQ]

I would still like to know if this is possible, but I decided to go a
different route and use docmd.transferspreadsheet method into a temporary
table which is less efficient, but works for now. If you have a more elegant
answer for me please respond. Thanks.

 

[Klatuu]

The TransferSpreadsheet method is the correct, but you don't have to load it
into a table. You can use acLink as the transfer type, then open the linked
spreadsheet as if it were a table.

As to your original question, you cannot do what you were trying to do
directly. You can use Automation to create an instance of Excel, open the
workbook file, and get the value by referencing the Worksheet and cell, but
that would actually take a lot more code and be much slower.

Linking to Excel spreadsheets is a very normal way to retrieve data in them.
Be aware; however, you cannot update a linked spreadsheet. To update the
spreadsheet data, you would have to import it into a table, update the table,
then export the table back to Excel or you can use automation to modify a
spreadsheet.
--
Dave Hargis, Microsoft Access MVP

I would still like to know if this is possible, but I decided to go a
different route and use docmd.transferspreadsheet method into a temporary
table which is less efficient, but works for now. If you have a more elegant
answer for me please respond. Thanks.

I was rereading my post and realized that it may have been a little unclear,
and I had a typo in the code. The excel filename stored in strDocName and the
excel cell location stored in AccountNo.ExcelCell which can be accessed via
the open record set rs.ExcelCell. How do I use that information to reference
the data stored in the spreadsheet at that specific cell location to update
the value stored in FiscalYearBudget.ActualBudgetAmt?

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & "REFERENCE
THE EXCEL CELL RIGHT HERE TO UPDATE FIELD"
sql = sql & "WHERE FiscalYearBudget.[Acct No]= " & rs![Acct No]
sql = sql & "AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

docmd.RunSQL(sql)

 

[SuzyQ]

The spreadsheet is not set up logically in row/colum to access it like a
table, the data is all over the place. If I linked to the spreadsheet using
transferspreadsheet method to use it like a table, how might refer to a
specific cell to get data? I will not be updating the spreadsheet, I only
want to get the data from it into access.

The TransferSpreadsheet method is the correct, but you don't have to load it
into a table. You can use acLink as the transfer type, then open the linked
spreadsheet as if it were a table.

As to your original question, you cannot do what you were trying to do
directly. You can use Automation to create an instance of Excel, open the
workbook file, and get the value by referencing the Worksheet and cell, but
that would actually take a lot more code and be much slower.

Linking to Excel spreadsheets is a very normal way to retrieve data in them.
Be aware; however, you cannot update a linked spreadsheet. To update the
spreadsheet data, you would have to import it into a table, update the table,
then export the table back to Excel or you can use automation to modify a
spreadsheet.
--
Dave Hargis, Microsoft Access MVP

I would still like to know if this is possible, but I decided to go a
different route and use docmd.transferspreadsheet method into a temporary
table which is less efficient, but works for now. If you have a more elegant
answer for me please respond. Thanks.

I was rereading my post and realized that it may have been a little unclear,
and I had a typo in the code. The excel filename stored in strDocName and the
excel cell location stored in AccountNo.ExcelCell which can be accessed via
the open record set rs.ExcelCell. How do I use that information to reference
the data stored in the spreadsheet at that specific cell location to update
the value stored in FiscalYearBudget.ActualBudgetAmt?

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & "REFERENCE
THE EXCEL CELL RIGHT HERE TO UPDATE FIELD"
sql = sql & "WHERE FiscalYearBudget.[Acct No]= " & rs![Acct No]
sql = sql & "AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

docmd.RunSQL(sql)

 

[Klatuu]

If it is not set up in tabluar (row/column) form, it may not work at all.
What happens when you import the spreadsheet into a temporary table? Are
you able to get the value then?

If not, let me know. You are going to have to learn about Automation.
--
Dave Hargis, Microsoft Access MVP

The spreadsheet is not set up logically in row/colum to access it like a
table, the data is all over the place. If I linked to the spreadsheet using
transferspreadsheet method to use it like a table, how might refer to a
specific cell to get data? I will not be updating the spreadsheet, I only
want to get the data from it into access.

The TransferSpreadsheet method is the correct, but you don't have to load it
into a table. You can use acLink as the transfer type, then open the linked
spreadsheet as if it were a table.

As to your original question, you cannot do what you were trying to do
directly. You can use Automation to create an instance of Excel, open the
workbook file, and get the value by referencing the Worksheet and cell, but
that would actually take a lot more code and be much slower.

Linking to Excel spreadsheets is a very normal way to retrieve data in them.
Be aware; however, you cannot update a linked spreadsheet. To update the
spreadsheet data, you would have to import it into a table, update the table,
then export the table back to Excel or you can use automation to modify a
spreadsheet.
--
Dave Hargis, Microsoft Access MVP

I would still like to know if this is possible, but I decided to go a
different route and use docmd.transferspreadsheet method into a temporary
table which is less efficient, but works for now. If you have a more elegant
answer for me please respond. Thanks.

:

I was rereading my post and realized that it may have been a little unclear,
and I had a typo in the code. The excel filename stored in strDocName and the
excel cell location stored in AccountNo.ExcelCell which can be accessed via
the open record set rs.ExcelCell. How do I use that information to reference
the data stored in the spreadsheet at that specific cell location to update
the value stored in FiscalYearBudget.ActualBudgetAmt?

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & "REFERENCE
THE EXCEL CELL RIGHT HERE TO UPDATE FIELD"
sql = sql & "WHERE FiscalYearBudget.[Acct No]= " & rs![Acct No]
sql = sql & "AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

docmd.RunSQL(sql)

 

[SuzyQ]

Sorry, I wasn't thinking when I responded. I forgot the the specific field
was part of the trasferspreadsheet method. I made the change and it works
perfectly. Thanks for your post.

The TransferSpreadsheet method is the correct, but you don't have to load it
into a table. You can use acLink as the transfer type, then open the linked
spreadsheet as if it were a table.

As to your original question, you cannot do what you were trying to do
directly. You can use Automation to create an instance of Excel, open the
workbook file, and get the value by referencing the Worksheet and cell, but
that would actually take a lot more code and be much slower.

Linking to Excel spreadsheets is a very normal way to retrieve data in them.
Be aware; however, you cannot update a linked spreadsheet. To update the
spreadsheet data, you would have to import it into a table, update the table,
then export the table back to Excel or you can use automation to modify a
spreadsheet.
--
Dave Hargis, Microsoft Access MVP

I would still like to know if this is possible, but I decided to go a
different route and use docmd.transferspreadsheet method into a temporary
table which is less efficient, but works for now. If you have a more elegant
answer for me please respond. Thanks.

I was rereading my post and realized that it may have been a little unclear,
and I had a typo in the code. The excel filename stored in strDocName and the
excel cell location stored in AccountNo.ExcelCell which can be accessed via
the open record set rs.ExcelCell. How do I use that information to reference
the data stored in the spreadsheet at that specific cell location to update
the value stored in FiscalYearBudget.ActualBudgetAmt?

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & "REFERENCE
THE EXCEL CELL RIGHT HERE TO UPDATE FIELD"
sql = sql & "WHERE FiscalYearBudget.[Acct No]= " & rs![Acct No]
sql = sql & "AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

docmd.RunSQL(sql)

 

[Klatuu]

Okay, good.
You solution is actually very clever.
Good work.
--
Dave Hargis, Microsoft Access MVP

Sorry, I wasn't thinking when I responded. I forgot the the specific field
was part of the trasferspreadsheet method. I made the change and it works
perfectly. Thanks for your post.

The TransferSpreadsheet method is the correct, but you don't have to load it
into a table. You can use acLink as the transfer type, then open the linked
spreadsheet as if it were a table.

As to your original question, you cannot do what you were trying to do
directly. You can use Automation to create an instance of Excel, open the
workbook file, and get the value by referencing the Worksheet and cell, but
that would actually take a lot more code and be much slower.

Linking to Excel spreadsheets is a very normal way to retrieve data in them.
Be aware; however, you cannot update a linked spreadsheet. To update the
spreadsheet data, you would have to import it into a table, update the table,
then export the table back to Excel or you can use automation to modify a
spreadsheet.
--
Dave Hargis, Microsoft Access MVP

I would still like to know if this is possible, but I decided to go a
different route and use docmd.transferspreadsheet method into a temporary
table which is less efficient, but works for now. If you have a more elegant
answer for me please respond. Thanks.

:

I was rereading my post and realized that it may have been a little unclear,
and I had a typo in the code. The excel filename stored in strDocName and the
excel cell location stored in AccountNo.ExcelCell which can be accessed via
the open record set rs.ExcelCell. How do I use that information to reference
the data stored in the spreadsheet at that specific cell location to update
the value stored in FiscalYearBudget.ActualBudgetAmt?

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & "REFERENCE
THE EXCEL CELL RIGHT HERE TO UPDATE FIELD"
sql = sql & "WHERE FiscalYearBudget.[Acct No]= " & rs![Acct No]
sql = sql & "AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

docmd.RunSQL(sql)

 

[SuzyQ]

I spoke too soon. It ran fine, but all my data came through as if getting it
from the same cell. It was working before when I used acImport and updated a
table then took that data from the table, but when I changed it to acLink I
now have 16 links to my spreadsheet in my database, obviously it was taking
the information from the first link it created. Is there a way to break the
link for each subsequent loop. In the meantime, I'm going to change it back
to acImport, because that was working fine.

Code below:

Do While Not rs.EOF

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97,
"ExcelData", stDocName, False, rs!ExcelCell

ExcelSQL = "select * from exceldata"
Set rsExcel = CurrentDb.OpenRecordset(ExcelSQL)

If Not IsNull(rsExcel!f1) Then
ExcelData = rsExcel!f1
Else
ExcelData = 0#
End If
rsExcel.Close
Set rsExcel = Nothing

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & ExcelData
sql = sql & " WHERE FiscalYearBudget.[Acct No]= '" & rs![acct no]
sql = sql & "' AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

DoCmd.RunSQL (sql)
UpdateCount = UpdateCount + 1

If Not rs.EOF Then
rs.MoveNext
End If
Loop
rs.Close
Set rs = Nothing

The TransferSpreadsheet method is the correct, but you don't have to load it
into a table. You can use acLink as the transfer type, then open the linked
spreadsheet as if it were a table.

As to your original question, you cannot do what you were trying to do
directly. You can use Automation to create an instance of Excel, open the
workbook file, and get the value by referencing the Worksheet and cell, but
that would actually take a lot more code and be much slower.

Linking to Excel spreadsheets is a very normal way to retrieve data in them.
Be aware; however, you cannot update a linked spreadsheet. To update the
spreadsheet data, you would have to import it into a table, update the table,
then export the table back to Excel or you can use automation to modify a
spreadsheet.
--
Dave Hargis, Microsoft Access MVP

I would still like to know if this is possible, but I decided to go a
different route and use docmd.transferspreadsheet method into a temporary
table which is less efficient, but works for now. If you have a more elegant
answer for me please respond. Thanks.

I was rereading my post and realized that it may have been a little unclear,
and I had a typo in the code. The excel filename stored in strDocName and the
excel cell location stored in AccountNo.ExcelCell which can be accessed via
the open record set rs.ExcelCell. How do I use that information to reference
the data stored in the spreadsheet at that specific cell location to update
the value stored in FiscalYearBudget.ActualBudgetAmt?

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & "REFERENCE
THE EXCEL CELL RIGHT HERE TO UPDATE FIELD"
sql = sql & "WHERE FiscalYearBudget.[Acct No]= " & rs![Acct No]
sql = sql & "AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

docmd.RunSQL(sql)

 

[Klatuu]

In your case, the import would be better.
One way is not always the best way. Sometimes circumstances make the
difference.
--
Dave Hargis, Microsoft Access MVP

I spoke too soon. It ran fine, but all my data came through as if getting it
from the same cell. It was working before when I used acImport and updated a
table then took that data from the table, but when I changed it to acLink I
now have 16 links to my spreadsheet in my database, obviously it was taking
the information from the first link it created. Is there a way to break the
link for each subsequent loop. In the meantime, I'm going to change it back
to acImport, because that was working fine.

Code below:

Do While Not rs.EOF

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97,
"ExcelData", stDocName, False, rs!ExcelCell

ExcelSQL = "select * from exceldata"
Set rsExcel = CurrentDb.OpenRecordset(ExcelSQL)

If Not IsNull(rsExcel!f1) Then
ExcelData = rsExcel!f1
Else
ExcelData = 0#
End If
rsExcel.Close
Set rsExcel = Nothing

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & ExcelData
sql = sql & " WHERE FiscalYearBudget.[Acct No]= '" & rs![acct no]
sql = sql & "' AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

DoCmd.RunSQL (sql)
UpdateCount = UpdateCount + 1

If Not rs.EOF Then
rs.MoveNext
End If
Loop
rs.Close
Set rs = Nothing

The TransferSpreadsheet method is the correct, but you don't have to load it
into a table. You can use acLink as the transfer type, then open the linked
spreadsheet as if it were a table.

As to your original question, you cannot do what you were trying to do
directly. You can use Automation to create an instance of Excel, open the
workbook file, and get the value by referencing the Worksheet and cell, but
that would actually take a lot more code and be much slower.

Linking to Excel spreadsheets is a very normal way to retrieve data in them.
Be aware; however, you cannot update a linked spreadsheet. To update the
spreadsheet data, you would have to import it into a table, update the table,
then export the table back to Excel or you can use automation to modify a
spreadsheet.
--
Dave Hargis, Microsoft Access MVP

I would still like to know if this is possible, but I decided to go a
different route and use docmd.transferspreadsheet method into a temporary
table which is less efficient, but works for now. If you have a more elegant
answer for me please respond. Thanks.

:

I was rereading my post and realized that it may have been a little unclear,
and I had a typo in the code. The excel filename stored in strDocName and the
excel cell location stored in AccountNo.ExcelCell which can be accessed via
the open record set rs.ExcelCell. How do I use that information to reference
the data stored in the spreadsheet at that specific cell location to update
the value stored in FiscalYearBudget.ActualBudgetAmt?

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & "REFERENCE
THE EXCEL CELL RIGHT HERE TO UPDATE FIELD"
sql = sql & "WHERE FiscalYearBudget.[Acct No]= " & rs![Acct No]
sql = sql & "AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

docmd.RunSQL(sql)

 

[SuzyQ]

Yes, I get one field at a time and then delete it. I know it's bad, but I
can't think of anything else.

If it is not set up in tabluar (row/column) form, it may not work at all.
What happens when you import the spreadsheet into a temporary table? Are
you able to get the value then?

If not, let me know. You are going to have to learn about Automation.
--
Dave Hargis, Microsoft Access MVP

The spreadsheet is not set up logically in row/colum to access it like a
table, the data is all over the place. If I linked to the spreadsheet using
transferspreadsheet method to use it like a table, how might refer to a
specific cell to get data? I will not be updating the spreadsheet, I only
want to get the data from it into access.

The TransferSpreadsheet method is the correct, but you don't have to load it
into a table. You can use acLink as the transfer type, then open the linked
spreadsheet as if it were a table.

As to your original question, you cannot do what you were trying to do
directly. You can use Automation to create an instance of Excel, open the
workbook file, and get the value by referencing the Worksheet and cell, but
that would actually take a lot more code and be much slower.

Linking to Excel spreadsheets is a very normal way to retrieve data in them.
Be aware; however, you cannot update a linked spreadsheet. To update the
spreadsheet data, you would have to import it into a table, update the table,
then export the table back to Excel or you can use automation to modify a
spreadsheet.
--
Dave Hargis, Microsoft Access MVP


:

I would still like to know if this is possible, but I decided to go a
different route and use docmd.transferspreadsheet method into a temporary
table which is less efficient, but works for now. If you have a more elegant
answer for me please respond. Thanks.

:

I was rereading my post and realized that it may have been a little unclear,
and I had a typo in the code. The excel filename stored in strDocName and the
excel cell location stored in AccountNo.ExcelCell which can be accessed via
the open record set rs.ExcelCell. How do I use that information to reference
the data stored in the spreadsheet at that specific cell location to update
the value stored in FiscalYearBudget.ActualBudgetAmt?

sql = "UPDATE FiscalYearBudget "
sql = sql & "SET FiscalYearBudget.ActualBudgetAmt = " & "REFERENCE
THE EXCEL CELL RIGHT HERE TO UPDATE FIELD"
sql = sql & "WHERE FiscalYearBudget.[Acct No]= " & rs![Acct No]
sql = sql & "AND FiscalYearBudget.FiscalYear= " & Me.cmbFiscalYear

docmd.RunSQL(sql)

 

[Guest]

You can link to a specific cell in a spreadsheet using the spreadsheet
cell name expressed in normal database syntax, [database].

.[field],
(you can experiment by using a cell as the data source for a control
on a form) but you normally will find it is better to open the file, read
all
the cells into a table, then use that.

Also, beware that reading and writing data from a spreadsheet does
not update any calculations. If you want to use calculated values,
you must use an Excel object to update the spreadsheet before
you read the calculated values.

(david)