Solved

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

Posted on 2014-10-31
18
71 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
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 

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
 
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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