userform to send data to multiple files

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
Is it possible to click a Command Button on a userform and have the data it refers to sent to a place in the same workbook, but also the same location in 20 other workbooks ?

example :

When I press Command Button 1 I want the word "kick" to appear in the first available cell in column B in the sheet called "Raw Data" which is found in the same workbook as the Userform, but also sent to the first available cell in column B in a sheet called "Raw Data" in 20 other workbooks named "Game1", "Game 2", "Game 3" etc etc etc.

I currently have this happening all in the one sheet, ie: the Games 1 through 20 are 20 sheets in the one workbook, but it is processing way too slowly, so I'm guessing that having each game as a sepearate file, that should speed the processing up considerable.

Am I on the right track with making each game a different fworkbook or is there another, more efficient way to do all this ?

Thanks in advance for any help,

Chris
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 
Upvote 0

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
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.
 
Upvote 0

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

Code:
Sub populate_other_files()
 
  Dim i As Long
  Dim strConn As String
  Dim objRS As Object
 
  strConn = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
      ActiveWorkbook.Path & "\Games xyz.xls", ";Extended Properties=""Excel 8.0;"""), vbNullString)
 
  Set objRS = CreateObject("ADODB.Recordset")
  For i = 1 To 20
    objRS.Open "INSERT INTO [Raw Data$] (Your_Col_B_Header) VALUES ('kick')", Replace$(strConn, "xyz", i)
  Next i
  Set objRS = Nothing
End Sub
 
Upvote 0

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 
Upvote 0

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
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
 
Upvote 0

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
Code:
dim wbk as workbook
 
for i = 1 to 20
 set wbk = workbooks.open("your path\" & "Games " & i & ".xls")
' then the step similar to your code above
 with wbk
'  do the last row and enter 'kick'
  .save
 end with
 wbk.close
next i

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

Sunset Decks

New Member
Joined
Mar 20, 2009
Messages
2
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,:rolleyes:
 
Upvote 0

Forum statistics

Threads
1,191,003
Messages
5,984,109
Members
439,872
Latest member
noaman79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top