Linked Excel Spreadsheet

Experts,

I have an excel sheet that is linked.
It has dates at the top
It has accounts along the left hand side (its like an Income statement)
I thought I would be able to sum for the accounts along the left hand side'
However, what I see in the query design after dragging the linked table in are only the dates and not the field names along left side (they are under a field called "F1")
I cant really make much use of this file because I cant sum for an account.  
I imagine an expert has some experience with linking excel files of this nature with dates along top and accounts on left.

How would I be able to sum for a particular account?


2

1
pdvsaProject financeAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
First, copy the file to a (local) work folder to not lock the original.
Second, link the Named Range in question.

Then do something like this:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Index As Integer
Dim MonthSum As Currency

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * From NameOfYourLinkedRange Where F1 = 'Daily Draw/ (Repayment)'")
If rs.RecordCount = 1 Then
    For Index = 2 To rs.Fields.Count - 1
        If Nz(rs.Fields(Index).Value, 0) > 0 Then
            MonthSum = MonthSum + rs.Fields(Index).Value
        End If
    Next
End If
rs.Close

Set rs = Nothing
Set db = Nothing

Open in new window

/gustav
0
 
pdvsaProject financeAuthor Commented:
Here is what the query design looks like:

QueryDesign
1
 
Gustav BrockCIOCommented:
If you filter on one account (F1) only, there is only this one to "sum" - and as your values shown for the example account are Null, you don't even have one value, just Null.

This is probably not what you are after, so I guess you'll have to rephrase your question or provide an extended example and also the expected output.

/gustav
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
remove the equal "=" sign from your criteria

criteria   'Daily Draw/ (Repayment)'
0
 
PatHartmanCommented:
If your query is not returning any rows, it is probably because the account name is different from what you typed.  Try copy and paste since that should pick up the correct number of embedded spaces.


This file will be a royal PITA every time you use it since the column headers will change.  If you need to automate this, you will need to use VBA to normalize the data into a correctly designed table where the columns are fixed.  Do you need help with that?
0
 
pdvsaProject financeAuthor Commented:
Sorry for my tardy response..I was tied up with my real job.  Appreciate the responses.  Let me give a bit more info.  
The field names will not change but the column headers (the dates along top) could change (this is an answer to Pats question above).  ==>Yes, VBA would be interesting.  

OK so what I am looking for is to sum: Daily Draw / (Repayment) for each of the months along top but for only where it is NOT NULL and >0 (do not want to pay attention to the negatives).  .  I think the formula would need not to reference any hard coded dates (ie 9/2/2016) in the column but a more general reference (Not sure how to explain that properly).

as FYI:  I will be either linking or importing this excel file on a daily basis to get updated info for current and previous day.  Im not sure if importing or linking is better.  I do have a named range.  It works well but I think if its linked, the excel file on the network is locked so I am siding with importing the data.  

any suggestions are always welcome.  I am grateful for any info.  

here is a new screen print of the excel table in Access.  I have hidden some rows so you can see "Daily Draw / (Repayment)" has data in some of those date columns along top.  Let me know if need more of a description of what I am trying to accomplish.
 tbl screen print
0
 
pdvsaProject financeAuthor Commented:
Gustav, that looks nice.  Where should I put that code (ie under a button?)  Is the code to "look" at where the copied file is?  I do have it imported into the db.
0
 
Gustav BrockCIOCommented:
Yes, you could place it in the OnClick event of a button and add this line:

    MsgBox "Sum is: " & Format(MonthSum, "Currency"), vbInformation

Replace "NameOfYourLinkedRange" with the name of your table, linked or imported.

/gustav
0
 
pdvsaProject financeAuthor Commented:
gustav,

I have pasted the code on a button and added the MsBox line as well.
I click the button and the error its giving me is : runtime 3061 "too few parameters"  and highlights the yellow line as below.  

Let me know what is next.  thank you.  

error
0
 
pdvsaProject financeAuthor Commented:
I think that since I imported the table, the F1 field is now named "Field1".  I changed the code from F1 to Field1 and I didnt get the error but I also did not get a msgbox stating the sum.   Not sure if I have placed the msgbox in the correct area.


tablimported
0
 
Gustav BrockCIOCommented:
Your code seems correct, and the line with the MsgBox cannot be skipped, so something else is going on.

/gustav
0
 
pdvsaProject financeAuthor Commented:
Gustav, not sure what happened but I tried it again and it worked!

thank you for the help.  I sincerely appreciate your assistance.
0
 
Gustav BrockCIOCommented:
Great! You are welcome.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.