Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet laptop malware memory modem monitor motherboard network printer problem ram registry router security slow software sound toshiba trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
update log worksheet to external workbook

Reply  
Thread Tools
steve420's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Feb 2010
Experience: Intermediate
08-Feb-2010, 06:04 PM #1
update log worksheet to external workbook
I have a template form we use for creating job quotes. We've been using the updatelogworksheet macro below to add within the workbook itself, but the other macros that I had to create to reset the main form after every use and general lack of knowledge by other users made this not useful in that earlier logs would be overwritten or the form would not reset properly. Is there a way to create a log of history in a separate workbook to track this separately and maintain the blank template form? macro follows. Using Excel 2003 in XP. Steve Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range 'cells to copy from Input sheet - some contain formulas myCopy = "D12,D17,G14,G16,J48,J51" Set inputWks = Worksheets("ONLY") Set historyWks = Worksheets("sheet1") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With End Sub '
Keebellah's Avatar
Computer Specs
Senior Member with 3,541 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Never too old to learn!
09-Feb-2010, 05:46 AM #2
Better send a workbook with dummy data and we'll take a look.

The log sheet should of course be available to all using the template sheet.
steve420's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Feb 2010
Experience: Intermediate
09-Feb-2010, 01:24 PM #3
Here is a sample of how the form is currently. The problem is with a template, the Quotes sheet stays the same every time the template is opened. The form is on a shared network drive, and my intent is to put a tracking list workbook on the same shared drive, so the template form is always the same for users and the tracking form is to check open quotes at a glance. I have tried adding open workbook commands to the macro, but I can't figure out how to get the macro to look at the other workbook. The macro limits itself to the current quotes workbook template or the tracking workbook.
Attached Files
File Type: xls SAMPLE.xls (190.0 KB, 65 views)
Keebellah's Avatar
Computer Specs
Senior Member with 3,541 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Never too old to learn!
09-Feb-2010, 05:52 PM #4
I think I understand you.
What you should do in your vba module

Public wba as workbook
Pubic wbb as workbook

this way you don't have to Dim them every time.

Application.Workbooks.Open (<workbookname>), ReadOnly:=True

where workbookname is the fullpath and filename
Since you call it from your workbook you can access all te macros and do whatever you need just keeping in mind that you reference the correct workbook(s)

wba.sheets("quotes")range("A2").value = wbb.sheets(whatever.Range("B3").value
etc etc.

I hope you get the idea.
__________________
Cheers,
Hans
It's all in the code... and may the code be with you! If it isn't . . . start debugging! (Time zone: Western Europe)

Last edited by Keebellah; 09-Feb-2010 at 05:53 PM.. Reason: Type-O
steve420's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Feb 2010
Experience: Intermediate
09-Feb-2010, 06:42 PM #5
Somewhat. Your last note about the wba.sheets range. Does that go in the string area of the macro.

Also "wba" and "wbb" are replaced by the actual file names or am I doing that incorrectly?
Keebellah's Avatar
Computer Specs
Senior Member with 3,541 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Never too old to learn!
09-Feb-2010, 07:17 PM #6
in the function, to reference the workbooks:

set wba = activeworkbook.name
set wbb = the name of the workbook you are about to open.xls or xlst or the extension it's saved with in offie 2007

the all you have to do is refer to it

wba.sheet("A").range("B3").value = wbb.sheet( "X").Range("f6").value

just try things out.

You can also record a macro once the other sheet is opened and then see how it's coded.
That's how I started with macro's

Bedtime for me now, its'00:16

I'll read your post tomorrow.
I have an example somewhere of something similar and I'll send it
__________________
Cheers,
Hans
It's all in the code... and may the code be with you! If it isn't . . . start debugging! (Time zone: Western Europe)
steve420's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Feb 2010
Experience: Intermediate
10-Feb-2010, 11:13 AM #7
I keep getting a type mismatch compile error trying to "set" the destination workbook or an object required runtime error 424. I have the two set as public now, and no trouble setting the active workbook. I have tried opening the destination workbook before set, or placing it after "set" does not seem to make any difference.

Set wbb = quotetracking.xls.Name

I have tried including the folders (as used in the open file command), with and w/o the extension, with and w/o the ".name"
Keebellah's Avatar
Computer Specs
Senior Member with 3,541 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Never too old to learn!
10-Feb-2010, 12:55 PM #8
You got yoiur syntax wrong
An example:

Dim wkbData As Workbook
Set wkbData = Workbooks.Open(filename:=ActiveWorkbook.Path & "\KPLTS\Kostenplaats.xls", ReadOnly:=True)
Reply

Tags
excel, log, tracking

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 10:06 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.