Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

update log worksheet to external workbook


(!)

steve420's Avatar
steve420 steve420 is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
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
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,283 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
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
steve420 steve420 is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
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, 109 views)
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,283 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
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.

Last edited by Keebellah; 09-Feb-2010 at 05:53 PM.. Reason: Type-O
steve420's Avatar
steve420 steve420 is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
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
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,283 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
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
steve420's Avatar
steve420 steve420 is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
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
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,283 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
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)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
excel, log, tracking

(clock)
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)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑