Solved

open a file, lookup a reference and then add to it

Posted on 2014-10-31
18
66 Views
Last Modified: 2014-11-06
I am building a check list that will identify what has been created during the course of the day and then when the item is authorised show the item as completed.

the procedure will be:

When the original item is created it will open a workbook [called Sign Off Log] and in the sheet named 'Log'  enter the details from the active workbook: It will find next blank row in Sign Off Log Wb and enter current  Date [Column A], Reference from C5 in Active Workbook to [Column B] and Pay Date from Active Workbook to [Column C] and then close the workbook.

When the item that was created previously is being authorised, it will open the workbook [called Sign Off Log], find the respective item in the sheet  called 'Log' [i.e. look up the reference in cell C5 and find in Sign off Log then copy 'P and Q' from file and paste into 'D and E' in the  'Sign Off Log' workbook. Then close the 'Sign Off Log' workbook.

That sounds complicated but I have attached a file that should make it a little clearer, The file I have attached represents the ' Sign Off Log' The data that would populate this is from the original file that would contain the Macro.

So if I have the original file open and I am entering a new item it would open the Sign off Lofg and find the next blank cell then enter details into A,B and C.

If I am doing the authorising then I would open the sign off file, find the relevant item using the Reference from C5 and look this up in the sign off log and put data from P3 and Q3 in D & E highlighted orange in both files.

I hope this is clear enough.
Test-Input-file.xlsx
Sign-Off-Log.xlsx
0
Comment
Question by:Jagwarman
  • 10
  • 8
18 Comments
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40416574
Here you are:

Is this what your looking for ? these 2 subs will either create the record or update it depending on what fields are filled in your worksheet. Note I have pre-coded your log file basis it is located in the same directory as this input file and its name is: Sign off Log.xlsx

Try it and let me know.
the below is the code and attached is the workbook with the macro all you need to do is simply change the data in the workbook and provided the log file is in the same directory it will automatically update it.

Sub CreateLog(WS As Worksheet)
Dim MaxRow As Long
Dim oApp As Object
Dim WB As Workbook
Dim WSLog As Worksheet
Dim sFileName As String

Set oApp = CreateObject("Excel.application")
sFileName = ActiveWorkbook.Path & "\Sign-Off-Log.xlsx"
Set WB = oApp.Workbooks.Open(sFileName)
Set WSLog = WB.ActiveSheet
MaxRow = WSLog.Range("A" & WSLog.Rows.Count).End(xlUp).Row + 1

WSLog.Range("A" & MaxRow) = Now
WSLog.Range("B" & MaxRow) = WS.Range("C5")
WSLog.Range("C" & MaxRow) = WS.Range("H5")

Application.DisplayAlerts = False
WB.Close savechanges:=True
Application.DisplayAlerts = True
Set WB = Nothing
Set WSLog = Nothing
oApp.Quit
Set oApp = Nothing


End Sub

Sub UpdateLog(WS As Worksheet)
Dim MaxRow As Long
Dim oApp As Object
Dim WB As Workbook
Dim WSLog As Worksheet
Dim sFileName As String
Dim cCell As Range

Set oApp = CreateObject("Excel.application")
sFileName = ActiveWorkbook.Path & "\Sign-Off-Log.xlsx"
Set WB = oApp.Workbooks.Open(sFileName)
Set WSLog = WB.ActiveSheet

Set cCell = WSLog.Range("B:B").Find(what:=WS.Range("C5"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not cCell Is Nothing Then
    WSLog.Range("D" & cCell.Row) = WS.Range("P3")
    WSLog.Range("E" & cCell.Row) = WS.Range("Q3")
End If

Application.DisplayAlerts = False
WB.Close savechanges:=True
Application.DisplayAlerts = True
Set WB = Nothing
Set WSLog = Nothing
oApp.Quit
Set oApp = Nothing

End Sub

Open in new window



Let me know
gowflow
Test-Input-file.xlsm
0
 

Author Comment

by:Jagwarman
ID: 40423409
Gowflow sorry for taking so long to get back to you, working on several projects at the moment and I can't multi task :-)

I have to say this is brilliant exactly what I need, but [sorry about the but] my problem is the sign off log will always be in the same place but not the same folder as the other file.

The folder it will be in is P:\CPN\Claim\Log

Is it posible to still make it Auto update

Many thanks
John
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40423489
ok here it is. The location of the file is hardcoded in the macro to point to
P:\CPN\Claim\Log\Sign-Off-Log.xlsx

If it is something else you need let me know.
gowlfow
Test-Input-file-V01.xlsm
0
 

Author Comment

by:Jagwarman
ID: 40423620
Thanks gowflow thanks for all your work on this it's perfect.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40423718
Your welcome glad I could help. Pls let me know if any other help needed.
gowlfow
0
 

Author Comment

by:Jagwarman
ID: 40425591
Hi Gowflow, I have added an additional field in and it works fine. My question now is where do I find the trigger. As I have 3 fields it records 3 records. I deally I would like it to trigger after the last cell has been entered which is cell J5.

I could run a delete duplicates but I am sure there is a better way to do this.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40425661
this question was answered correctly ? if yes pls proceed to close it accordingly and post a new one. Adding up issues as we go along will only contribute to confuse whoever is checking on similar issues.

Rgds/gowflow
0
 

Author Comment

by:Jagwarman
ID: 40425794
Hi gowflow I will post a separate question

I have accepted twice but it does not appear to be working it still shows on my screen as open
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40425800
You need to select the comment that for you is the answer and make sure it is not 'your comment' as sometimes one makes a mistake without noticing and click his/her comment as the solution (unless you believe so then it is not a problem) and award points and then close with a rating.

gowflow
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:gowflow
ID: 40425996
You did not let me know that you posted a new question related to this one.

Do we have a problem here that I am not catching ?
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40426040
Hi Gowflow no really sorry I thought you would pickup on  when I said "Hi gowflow I will post a separate question" I guess I should have said what I would call it.

Very sorry I have another question I will call it "call different sheets"

Regards
John
0
 

Author Comment

by:Jagwarman
ID: 40426119
OK just posted my question hope it's clear
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40426207
You need to post a link of the question in here difficult to search for your question.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40426222
Again if your not able to close this question ask for Attention and a moderator will assist.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40426286
Two files to assist with question.

Test-Input-file V2 has 3 tabs Sheet1, Sheet2 and Sheet3. So this is a 3 part process.

The user should complete in that order but may not.

the cells in C5 and H5 in sheets 2 and 3 are taken from Sheet1 when sheet1is completed  

When the user completes P3 and Q3 in Sheet 1 in [Test-Input-file_V2] it updates the sign-off log. [Log1]

When the user completes P3 and Q3 in Sheet 2 [Test-Input-file_V2] I need it to update the sign-off log in Sheet2 [Log2]

When the user completes P3 and Q3 in Sheet 3 I need it to update the sign-off log in Sheet3 [Log3]

Hope this makes it clearer.
Sign-Off-LogV2.xlsx
Test-Input-file-V2.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40426370
Please keep issues separate.

Above comment relates to an other question and is irrelevant here. Pls proceed to close this one.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40426376
posted on wrong one but have posted on correct one. Have also contact a moderator to tey to assist in closing completed questions

Apologies for messing you around and thanks for your help
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40426398
No problem.
gowflow
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now