userform to send data to multiple files

Hi, Chris.

Likely the method you use to populate the sheets is inherently slow and a different approach would be better. Can't really be specific though based on the limited info posted about the setup and approach currently used.

regards, Fazza

Disregarding what I'm currently doing, is there enough information in the example I gave to create a code for a Command Button that sends the word "kick" to first available cell in column B in the sheet called "Raw Data" in the 20 other workbooks called "Game 1", "Game 2", etc etc as well as the same workbook that the Command Button & UserForm exist in.

Hi, Chris.

I'll make some assumptions such as that the other 20 files are closed and in the same sub-directory and your Excel version is pre Excel 2007. This code will populate the other 20 files in column B of 'Raw Data' - adjust to suit your field header. If the header contains a space, enclose the name `like this`. For the source file, the update is trivial (that is, the 'Raw Data' worksheet in the base file); I'll leave that for you. If the files are in a different sub-directory or Excel 2007 please adjust the connection string to suit. HTH, Fazza

PS

I just noticed your file names are "Game1", "Game 2", "Game 3", etc

I wrongly coded for "Games n" instead of "Game n"

Please edit my code and change "Games" to "Game"

F

Wow !! Thankyou, so much for putting so much effort into helping me, but I am completely lost with your code.

I am very new to coding and try to only use code I understand, so if changes need to be made, I can make the changes myself, but have no idea how to read and understand what you have taken the time to put together for me.

For example, to place the word "kick" into the first available cell in column B in the sheet called "TouchPaddata" in the same workbook as the Userform that bears the Command button, would woulde code :

Private Sub CommandButton1_Click()
Dim LR As Long
With Sheets("TouchPaddata")
LR = .Range("B" & Rows.Count).End(xlUp).Row + 1
.Range("B" & LR).Value = "kick"
End With
End Sub

I had hoped it would be a reasonably simple affair to send the same instruction to the 20 other workbooks (called "Game 1", "Game 2" etc etc etc all found within the same file).

Is there no simple addition to what I am currently soing or will I need to learn how to read what you have provided ?

Many, many thanks for what you have done,

Chris Jamieson

Chris,

Your code looks fine for the same workbook.

To other workbooks you have a choice. There is a simple approach - to open and close each of the 20 files. Sort of like you have but with a few extra steps. So, pseudo code

This is rather slow. Especially the opening/closing of 20 workbooks.

An alternative is like I coded where the 20 other files are not opened: this is faster / more efficient. It was what the question was about. I've used late bound ADO and no error checking. If you early bind it will be faster. If you are going to be doing this sort of work - saving/reading to closed files - it really is a good way to go. Suggest you google for some info with keywords like: Excel VBA ADO recordset. There are a few forum posts with some good links I can search for some tomorrow if you're interested. When one is new to this it is a different way of thinking though is pretty straightforward with familiarity.

So you can take the simple approach if you like - in fact it might not be too slow, depends on your network & file sizes - or otherwise start learning some ADO and other database type techniques which are really much more efficient for handling data.

Regards, Fazza

Like mentioned above, I am VERY new to this also. I am trying to do something very similar. I've created a userform to enter customer info. for appointment setting. I would then like for that info. to be transferred to a seperate workbook for database purposes (mail merge, labels, etc.) Any help would be appreciated! Also, maybe I should mention that I already have most of the info. going directly to specific cells in the same workbook that the userform is in, but I want that same info in a seperate database also.

Thanks,