filling in cells in multiple spreadsheets?

[samohtwerdna]

Hello,
I am curious what code I could use to transfer and change (in some cases) data that is entered into my user form txt box or cbo box - into multiple spreadsheets often at differing columns?

I suppose that naming the ranges in each spreadsheet would help, but I still dont know how to deliver it from just the one entry.

Can anyone help?

[Zack Barresse]

Hi,

Well this really depends on where you are going and what you are trying to do here. Some specifics would help. Maybe this will get you started ...

Sub foo()
    Range("A1").Value = UserForm2.ComboBox1.Value
    Workbooks("Book1.xls").Worksheets("Sheet1").Range("A2").Value = UserForm2.TextBox1.Text
    Workbooks("Book2.xls").Worksheets("Sheet3").Range("A3").Value = UserForm2.TextBox2.Text
End Sub

If you need other help, just post back. Be sure and be as descriptive as possible when posting as this will get you the best possible help quicker.

[samohtwerdna]

Thanks for the help!

I am curious wheather or not I can write to these different spreadsheets without having to have them open.
If I must open them, is there a simple way to do that in VBA without having to Dim each workbook and worksheet?

[Zack Barresse]

Well, I guess that depends on how many workbooks we're talking about here. If not too many, I would Dim each one still. This gives you greater control and efficiency, imho.

Try describing, in explicit detail, what values you are talking about, where they come from (workbook -> worksheet -> range, workbook -> userform -> control object, etc) and where exactly they are going.

[samohtwerdna]

Sorry about being vague.

I have a different spreadsheet for each part of a cabinet assembly. (e.g. back.xls, side.xls, top-btm.xls and door.xls) and then 1 spreadsheet for the whole assembly. (cabinet.xls)

when the user enters a value for the width height and depth of a new cabinet. I need to populate the correct cells in each .xls because my cabinet.xls is not going to have the "width", "height" and "depth" of each part but just the "A1" range that contains the name that corresponds to the correct vaules, and the overall "width", "height" and "depth" of the cabinet.

for example: if the user imputs 15 in the txtWidth then the width of my top-btm would be txtWidth.Text - 1.5 since I have .75 thick sides.

[Zack Barresse]

Is there any reason why these have to be in seperate spreadsheets? Can you not just make them individual sheets in the same workbook?

Can you upload them?

(Assuming they are all in the same location/folder, and you're using windows 2000 or above):

Press and hold the Ctrl key.
With your mouse, left click each of the files you listed.
Release the Ctrl key.
With your mouse, right click any one single file you selected.
Select Send To.
Select Compressed Folder.

Upload that zipped file.

[mdmackillop]

Hi Sam...
Is it possible for you to zip and post a sample of the workbooks to see what you're trying to achieve. My first thought is to query the requirement for separate workbooks, but your needs may demand it.
There may also be other ways to assist, eg User Defined Functions.
The following UDF accepts two values, Height, Thickness and produces a return of Height - 2 x Thickness.

Function ActualHeight(Height, Thick)
    ActualHeight = Height - 2 * Thick
End Function

This is simply entered on the spreadsheet as =ActualHeight(1500,12) or =ActualHeight(A2,B2) as required.
MD

[samohtwerdna]

Thanks for the attention - I will .zip and upload the files asap.
the answer to the question: "Can you not just make them individual sheets in the same workbook?" - is No - they have to remain seperate workbooks.

[mdmackillop]

No harm in looking for the easy way out!

[samohtwerdna]

here are the .xls files - nothing fancy right now.

I have a rude user form in the SW_cabinet.xls file

[Zack Barresse]

Well, this can be done. Although it will take a lot of detail on your part, as in what is coming from where and where it's going to (books, sheets, range). And afaik, the book has to be open to write value(s) to it. But they can be opened at runtime and then closed back down if you don't want them open.

[mdmackillop]

If you don't mind me suggesting alternatives, you could store all of your data in one workbook and have all your others link to it. The data in the linked books could not be changed, which may or may not be advantageous.
MD

[Zack Barresse]

If you don't mind me suggesting alternatives, you could store all of your data in one workbook and have all your others link to it...

Very much agreed! Why re-invent the wheel?

[samohtwerdna]

would this be like a database file?

To create a file with every possible instance would be a very large database. I would prefer being able to create the database for each part on the fly, with only a few rules in the code to translate the one entry into the appropriate info for each spreadsheet. If the books that are being writen to need to be open - can I delcare that in one routine?
Something like:

Sub multi ()
Dim xlApp As Excel.Application, xlWbk As Excel.Workbook, xlSht As Excel.Worksheet
   Set xlApp = GetObject(, "Excel.Application")
   xlApp.Visible = False
   Set xlWbk = Workbooks.Open(FileName:="g:back2.xls", "g:Top-Btm.xls", "g:carcass_side.xls")
   Set xlSht = xlApp.Worksheets("Sheet1")
End Sub

[samohtwerdna]

ok, so my previous post shows my simple mindedness & inexperiance. I realize that = " Set xlWbk = Workbooks.Open(FileName:="g:back2.xls", "g:Top-Btm.xls", "g:carcass_side.xls") will not work because vb is looking for a qualifier after the single file name is entered.

So I changed it to :
" Set xlWbk = Workbooks.Open(FileName:="g:back2.xls")
Set xlWbk = Workbooks.Open(FileName:="g:Top-Btm2.xls") 'and so forth"
But how can I exit or close the workbook after update, without ending the routine or the application?

[Zack Barresse]

xlWbk.Close True 'Change to False to NOT save changes.

If they're in one folder, you can loop through each file in that folder. Then maybe do a Select Case statement to choose what to do to that file. Although your method might be just as easy w/ the limited amount of files you have, then just Set your xlWbk with every new file.

[johnske]

Hi Sam,

If you gotta lot of workbooks in a folder you need to open if they pertain to certain things like (something)_doors, (something else)_backs, (something more)_sides, you can list all these in full in a column on a sheet (Sheet2 in this example).
You then just enter (say) doors in A1 on Sheet1 and run this macro to find and open all the books with the suffix "doors". After you close off each book you'll be left with just Sheet1 in the active window. Naturally you could modify this to use list boxes or whatever....

Option Explicit
Sub GetRecords()
Dim N%, i%, ThingsName$
Application.ScreenUpdating = False
Worksheets("Sheet2").Activate
Range("A1").Select
i = 0
Start:
If Selection Like "*" & Range("Sheet1!A1") Then
ThingsName = Selection
On Error Resume Next
'//set your own path to your folder below
Application.Workbooks.Open("C:\Windows\Desktop\" & _
"NewKeeper\DBs\" & ThingsName & ".xls").Activate
'//if you want to do something (in VBA) in these books, insert the code here...
i = i + 1
Workbooks("Book2").Activate
Worksheets("Sheet2").Select
ActiveCell.Offset(1, 0).Select
If ActiveCell = Empty Then GoTo Finish
GoTo Start
Else
Workbooks("Book2").Activate
Worksheets("Sheet2").Select
ActiveCell.Offset(1, 0).Select
If ActiveCell = Empty Then GoTo Finish
GoTo Start
End If
Finish:
Worksheets("Sheet1").Select
'//this's only if you actually want to LOOK at the book
For N = 1 To i
ActiveWindow.ActivatePrevious
Next N
End Sub

HTH

[johnske]

PS You can also shorten the previous code by using a "Do" loop >>>

Sub GetRecords2()
Dim N%, i%, ThingsName$
Application.ScreenUpdating = False
Worksheets("Sheet2").Activate
Range("A1").Select
i = 0
Do Until ActiveCell = Empty
    If Selection Like "*" & Range("Sheet1!A1") Then
        ThingsName = Selection
        On Error Resume Next
        Application.Workbooks.Open("C:\Windows\Desktop\" & _
       "NewKeeper\DBs\" & ThingsName & ".xls").Activate
       '//your own code to do things in each book here
        i = i + 1
    End If
    Workbooks("Book2").Activate
    Worksheets("Sheet2").Select
    ActiveCell.Offset(1, 0).Select
Loop
Worksheets("Sheet1").Select
For N = 1 To I
   ActiveWindow.ActivatePrevious
Next N
End Sub

[samohtwerdna]

Thanks John!

The code looks great, I will test it out soon. From the looks of it I won't have to do a :

Set xlWkb

for each workbook I write to, and that is very helpful!
Thanks

[johnske]

Thanks John!

The code looks great, I will test it out soon. From the looks of it I won't have to do a :

Set xlWkb

for each workbook I write to, and that is very helpful!
Thanks

Not a prob. I'm currently doing a similar project where there are a large number of workbooks arranged in classes and sub-classes of a topic that have to be opened.

Note: The "Like" command is a wildcard operator in vba for Excel and can also be used for prefixes:

If Selection Like Range("Sheet1!A1") & "*" Then
'//whatever

or for key words in the centre...

If Selection Like "*" & Range("Sheet1!A1") & "*" Then

'//whatever