[pctsvs]

This might be a shot in the dark, but is there a macro to open a new file while working in another.

I guess what I am wanting is: say I am entering information on one spread sheet, then I want to click a button that will upload the daily info that I entered into several spreadsheets in a diff file.

So I enter the info onto the October2005.xls and then I want the macro to open each individual part number.xls and input the info in there.

Part number.xls =
934 265-00.xls
934 285-01.xls
and so on

If all this does not make sense just let me know.

[cristobal03]

Automating data entry depends quite a bit on the data structure. And by quite a bit I mean entirely. It is possible to open a file using a macro. It is also possible to posit data from the current project into locations within the opened file. But. Since a macro works the same way every time, the data has to be stored in a manner that follows some pattern.

How are your spreadsheets set up? Do the sheets you want to open already exist, or are you creating new files? Does each sheet have a different setup, or require different information of the parent sheet (in your example, October2005.xls)?

chris.

[pctsvs]

Well I'm not sure if I want to say if they already exist or not. Yes the sheets I am speaking about exist, but I was asked to make it simpler. More than likely I will create sheets that are more user friendly, but I can't distort the info that is entered. So I am going to have to make a main page where all the info is stored and all the individual sheets by part number will take the info from the main sheet. Right now we are putting all the info on the main sheet (october2005.xls) and also going in and opening each individual sheet (945 001-25, 945 258-01, and so on). There are over thousands of part numbers that are stored on the main sheet and each individual part number has its own file. I don't want to open each and every file manually, but want a macro that will input only the information that is put in on the main sheet over to each individual file. All I guess I really need is the code that opens a file while working in another file. The rest of the macro, I understand would have to write myself to tell what info goes to what file and so forth.

I hope this makes sense and helps you.

[cristobal03]

Just to clarify a couple of things, because terminology helps avoid a lot of confusion.

First of all, there's a big difference between a sheet, a book, and a file when it comes to Excel. Generally speaking, it's okay to think of a file and a workbook as the same thing, though a file includes more (like macros and custom toolbars). A workbook is a collection of worksheets--it is not appropriate to think of a file and a worksheet as the same thing, because a worksheet is part of a workbook. So what it sounds like you have is a collection of files for each part number (e.g., using your numbers, 945 001-25.xls, 945 258-01.xls, and so forth--by the way, it'd probably be a good idea to remove the spaces from your file names), and each file has one (or more?) populated worksheets. This doesn't really change anything with the algorithm, but it helps to be precise so we know exactly what the situation is.

Another couple of questions, then. Are all these files stored in the same location, or directory? When you make updates to your main file, are you replacing data in a cell or are you adding a new dimension--for example, adding the data to a new row or column? I assume the usage will be such that you may update information for, say, a dozen parts...so you won't want to open the other 2,000 files or you'll probably put down your computer.

If we think of this in terms of records, each unique vector (think: row) represents one record--so one record corresponds to one part number, yes? If the file name for each part number is identical to or contains an identical match of the part number as it's entered on your main sheet, and if all the part files are located in the same directory tree, you can loop through the directory tree to find the corresponding file and open only that file. There may be a way to figure out which cells have been dirtied--that is, which cells have been changed since the file was last saved--but I don't know it. So, in lieu of that, you'll want to add some kind of control to your main sheet that you can use to show you've updated a record. For example, add a column to your sheet that is populated with Ns when the workbook is opened; when you modify a record, change the N to Y.

These are just some things to think about, because I think they'll be worthwhile to incorporate into your solution. Post back and we'll start adding meat to this skeleton.

chris.

[pctsvs]

Thank you for the clarification of excel 101. Yes I am working with different files (e.g. october2005.xls, 945 001-25, and so forth) and they are in different locations or directories ( I am assuming that you mean they are in diff folders or do you mean diff drives). They are on the same drive (K but are in diff folders in this drive. I may have just put the space in the names by accident cause that is the way I am used to entering the info, but the spaces can be dealt with. Yes each prefix (945, 960, etc.) has its own folder and each folder has its own part number file. I believe that I will have one file for each part number and add each part number to a diff sheet in that one file.

When changes are made it is done by adding to the dimension - adding new rows to the bottom of the already entered info. I will be only updating a days worth of info each day, so it's not like it will be 2000 files opening at once but just a handful, meaning 10 at the most for one day.

I like the idea with the N's and Y's and will probably incorporate it into this file, but if I don't like I said it is updated on a daily basis and the information does have a date for that specific information entered, so there won't be any confusion on what has been updated and not.

[cristobal03]

If you want to get started on something, one way to instantiate/open an existing file using VBA is to use the following code model:

Private Sub OpenAFile()
  Dim xlApp As New Excel.Application
  Dim oWb As Workbook

  Set oWb = xlApp.Workbooks.Open ("c:\somefile.xls")

  'do your actions here

  Set oWb = Nothing
End Sub

You say there's a column containing the date? So you wouldn't really need the Y/N column; you could run the process on only those records containing the current date, or a range of dates if you prefer.

I can't really help you with the rest of the code without having more specific information. I like the idea of creating a directory for each general part and a child file for each special part; there'd be more to maintain but also more clarity.

The algorithm I've been thinking about goes a little something like this (it's changed somewhat since you mentioned a Date column):

• Iterate through the rows in the ActiveSheet (the main sheet), and for each row where the date matches the current date, push that row index into an array. (Originally this matched a "Y" in the column we discussed earlier, but that's superfluous now.)

• For each index in the array, split the part number into genus (e.g., 934) and species (e.g., 265-00). Assign those strings to variables.

• Locate the appropriate file using a path constant & genus & species & ".xls"; open that file.

• Copy the info from the main file's record into the opened file; .Save, .Close the file.

• Repeat for each index in the array.

Something like that would be pretty hands-off, but means that every file has to be structured the same way. So before you start coding, really get a good handle on how you want to store/present this stuff. Otherwise you'll have headaches, big-time. I know I've given myself plenty o' thems by having poorly thought-out designs.

Post back with some more specifics and info if you want help with the code.

GL

chris.

[pctsvs]

Thank you I will work on that and get a reply back if I get into a bind and reply back to let you know how it turned out.

Thank you

[cristobal03]

OP pointed out that I didn't include the code for saving and closing the file. Sorry about that. For future reference, the .Save method of an application object saves any changes made to the file, and the .Close method closes and unlocks the file. Otherwise, I'm pretty sure you have to close any instances of the MDI/parent application to unlock the file. Anyway, here they be.

Using the previous code model:

Private Sub OpenAFile()
  Dim xlApp As New Excel.Application
  Dim oWb As Workbook

  Set oWb = xlApp.Workbooks.Open ("c:\somefile.xls")

  'do your actions here

  With oWb
    .Save
    .Close
  End With
  Set oWb = Nothing
End Sub

Sorry for the confusion.

chris.

[edit]
By the bye, I tried my original algorithm of storing the rows (range objects) in an array, and I guess that'n's a no-go. I mean, it works alright, and it might be how I declared the array--as an array of Variants not Objects or Ranges. So I'll play with that a bit, but my original plan might need some rethinking.

I'll post back.
[/edit]

[pctsvs]

I figured that out after I sent you the message cristobal03. Thanks for the clarification though. One other problem (I guess) is that when I run the macro, the ("c\somefile.xls") does not open. Is it supposed to? I tried to run just a mock macro to see how the whole thing worked and I wrote the code in there and in the ('do your actions here) I typed:

Sheets("DATA").select
Range("B4").select
ActiveCell = HELLO

When I tried to run this it gave me an error, but for the life of me I could not remember what error I got. I work third shift and all this info is there on my puter at work.

Just so that everyone knows, I know enough about macros and VBA to be dangerous and have written some macros to run alot of our reports. When I was asked to simplify this one report that does not have macros and is manually updated on a daily basis, I thought it would be easy to do.

[cristobal03]

Instantiating a new Application object does not mean that when you set the object to a file, the file actually opens in a window. It opens in the background for input/output processes. That said, you would probably get something like "Object variable or With block not set" trying to run your code (I think that's the error description). For any actions, you have to reference the object taking the action. For example, if you dimensioned your new application object xlApp, and dimensioned a Workbook object oWb, then your code might be:

  oWb.Sheets("DATA").Range("B4").Select
  ActiveCell.Value = "HELLO"

Does that make any sense? Just referencing Sheets("DATA") isn't enough because you have two files open (technically, even if you only had one file open, it's a good practice to point to the source object); Excel doesn't know which workbook to check for a sheet named DATA. So we add our object reference oWb, which (per the previous code model) has been set to the workbook file that was opened.

HTH, sorry I haven't been very explicit. I'll work a little harder on this from now on.

chris.

[pctsvs]

Below is the code that I have in my mock file. I don't mean to be a pain but the activecell.value = "HELLO" puts the word "HELLO" in the mock file and not in the ("C:\sample.xls") file, but it does however make the activecell on the sample.xls file in B4. What did I do wrong here?



Private Sub OpenAFile()
Dim xlApp As New Excel.Application
Dim oWb As Workbook

Set oWb = xlApp.Workbooks.Open("C:\sample.xls")

'do your actions here
oWb.Sheets("Sheet1").Range("B4").Select
ActiveCell.Value = "HELLO"
With oWb
.Save
.Close
End With
Set oWb = Nothing
End Sub

[cristobal03]

Yeah, that's my fault. Sorry 'bout that. So's you know, I'm a beginner with VBA. The main file (your mock file) retains the focus because the other runs in the background. Therefore the ActiveCell will always be in the active window, which is the mock file; you haven't set focus to the destination file.

Originally posted by pctsvs:
but it does however make the activecell on the sample.xls file in B4

Not exactly. Cell B4 of Sheet1 in sample.xls is selected not active.

The problem here is that, because I don't know anything about Excel VBA, I didn't verify the code you were originally trying to run to see if it would work in my code model. Guess we found that one out.

For future reference, you don't have to select a cell to change its value (I knew this but thought I'd try your way so it'd be more familiar to you). All you really need is this:

oWb.Sheets("Sheet1").Range("B4").Value = "HELLO"

But let's add to that, because we're not just trying to put a string in a cell; we're trying to build a frame for a versatile script. A few modifications to the code we have thus far:

Private Sub OpenAFile()
  Dim xlApp As New Excel.Application
  Dim oWb As Workbook
  Dim wsSource As Worksheet
  Dim wsDest As Worksheet
  Dim strTextToTransfer As String

  Set oWb = xlApp.Workbooks.Open("C:\sample.xls")
  Set wsSource = ActiveWindow.ActiveSheet
  Set wsDest = oWb.Sheets("Sheet1")

  'type "HELLO" into cell B4 of the mock file before
  'running this macro.  We'll grab that value on the
  'fly.

  strTextToTransfer = wsSource.Range("B4").Value

  wsDest.Range("B4").Value = strTextToTransfer

  With oWb
    .Save
    .Close
  End With

  Set wsSource = Nothing
  Set wsDest = Nothing
  Set oWb = Nothing
End Sub

Because this needs to be as dynamic as possible, we need as few literal references (like the Range reference of "B4") as possible. So we'll be adding more objects as we go. This time around there's a new worksheet object for the current file and the file to be opened. Also, there's a string object to hold the value that we're moving.

Sorry about the mistake last time around. I'll try to be more careful.

chris.

[pctsvs]

Ok now I am understanding. Just as you said, I am a beginner too. That code worked fine and when I go into work tonight I will start to add some meat and potatoes to the code. Just one more question before I hit the hay: I am going to have some loops and IF statements for the macro to pick up the dates on the reports and to know what part number to put each info into. Do I have to continue with all the owb and ws and str or can I code that with the "Do Until and loop" statements?

[cristobal03]

I should make a distinction.

An object is something that is quantifiable in some sense. For example, xlApp is an instance of Microsoft Excel, and you can work with it just as you could if you double-clicked your Excel startup icon. The same principle is true of wsSource or strTextToTransfer, or any other object; like a car, or an apple, or a piece of paper, you can do something with an object.

Loops and conditionals (If...Then...Else statements) usually use objects to perform actions, but not always. You'll be using loops and conditionals to handle objects in your application--you'll be looking for a Range or Cell that matches a certain value. Therefore, because objects are nested in a document hierarchy--that is, an object is usually contained within another object (think: workbook-->worksheet-->range-->row-->cell)--you have to reference an object so the VBA engine knows what you're pointing to. Some references are implicit, in that the VBA engine assumes a certain value if it hasn't been provided, but I personally believe in always using explicit references--being as exact as possible when pointing to objects.

(As an extended example of implicit v. explicit, consider this: when I talk to my family about my car, they implicitly know what kind of car I mean, and I can just say "my car"; if I tried to talk about my car with you, I'd have to say something explicit like, "my car, a late 90's honda civic sedan.")

Anyway, you should always use explicit object references in your code. You'll probably get errors if you don't, actually. So when you're building your loops and conditionals, you'll still have to use object references like wsDest.Range("A3:F3").Cells(,2).Value, or whatever.

HTH and makes things a little clearer.

chris.