Halp! Excel data exporting

[Warlow]

Hey Guys,

I work for a call centre environment and I'm trying to create a tool for support staff to quickly log a "job" to track productivity and capacity.
It stores some basic info, Who supported, how long, context to the support.

The purpose for my tool is simply to capture this information and deposit it into a central spreadsheet for analytics/reporting later.
I have the form working and the data appears in A1 (initially, then a2 etc) through to F1 (f2, f3 etc)

This is an example of my headings and the first line of data populated by
===========================

Consultant	Help with?	Work Required	What caused the pull?	Time Spent	Details
george	stuff	handoff	Lack of product knowledge	2	handed off to steven

===========================


My problem is that multiple users will be running/using the form at any given time and due to my extremely limited knowledge of coding, I don't know how to avoid each of these users saving over eachother's submissions without hitting save before submit, which you can't do because the form is open.

What I came up with was a mechanism to send the data from the "JPAPP" spreadsheet through to a new workbook I label as "JPAPPDATA.xlsx". The end result being that each support staffer will run the app and every time they submit it will pile up in the first available slot in the JPAPPDATA.xlsx.

I have created a button to "export" my data and I've found a few examples of code I could use, but as this is my first project and I'm learning this code on the fly, I'm not skilled enough to modify it to suit my needs.


I had posted a couple of links to reference some examples of code I have scavenged, but until I have 5 posts I can't include them

I'm sorry, if I knew enough to write code to use as an example I would, but I'm still early in my learnings!

I'm trying to get this finished by friday (business deadline) and I'm swimming a bit. Any assistance would be greatly appreciated!

[Warlow]

With workbook("book1").worksheets("sheet1")
.range("B5:B1`3").copy
With workbooks(*"book2").worksheets("sheet1")
,cells(rows.count,"A").end(xlup).offset(1,0).pastespecial
End With
End With

I found this on a random website, would there be a way to modify it to my needs? Would it work at all?

*Note: I tried to fix the indentation but the forum trims leading spaces & tab selects the next element on the webpage. Not too sure how to make it more legible for you guys!

[westconn1]

I tried to fix the indentation but the forum trims leading spaces

not in you use code tags

you can use that method to get the last row

with workbooks("book1.xls").sheets("datasheet")
    nextrow = .cells(.rows.count, 1).end(xlup).row + 1
    .cells(nextrow, 1) = textboxA
    .cells(nextrow, 2) = textboxB
    ' etc
end with

change workbook and sheet names to suit, use the textbox or controls from your form

[Warlow]

Ok, so rather than the form depositing the form data to the app sheet then having a button to deposit the info into the destination workbook JPAPPDATA, this would simply output the data directly to the new workbook from the form huh?

How does excel know where the JPAPPDATA workbook is? I mean, it's in the same directory, but is it intuitive enough to understand that without specification?

[westconn1]

How does excel know where the JPAPPDATA workbook is?

if the workbook is already open no path information is required
if you are using workbooks.open to add the .xls file to the workbook collection, full path is required

[Warlow]

I hope to have it closed on a network folder so each user can open the app and export data to the network file.

If the JPAPPDATA file has to be open I'm going to run into the same problem that multiple users using the same excel file causes with saving and overwriting. (having different sheets did occur to me, but I'm trying to avoid it due to how we need it to output the data for reporting.

I'll try and figure out how to use the workbooks.open command as that seems like what I need to use.

workbooks open - Path
Export data on first available cell
Workbooks close

This would cause the destination file to be saved wouldn't it? IE if two users hit the export button right after eachother, the first would land, save and close before the other overwrites the target cells?