Using InputBox to navigate to path

[Masterpsyker]

I'm working on a macro right now and I've run across a problem that I don't know how to solve on my own.

The macro I am creating will be used once a year to consolidate data across workbooks and worksheets that retain a constant structure. They never change in structure, only in data.

The macro needs to do the following:

1. Create a new workbook (no problem here)
2. Open a workbook from last year (Problem)
3. Open a workbook from the current year (Problem)
4. Copy specific worksheets from the now open workbooks to the new workbook (Can I copy a whole worksheet in VBA?)
5. Run opperations (no problems here)

Items 2, and 3 are where I hit stumbling blocks. The way I want to handle opening the workbooks is to have the user of the macro interface with an input box via the "Browse" mechanic to select the path of the workbook from last year and then proceed to the next input box when the path is properly selected. The filename of each workbook needs to be saved into its own string so that it can be referred to later in the activation lines of code for copying. Workbooks should open and then be minimized.

Item 4 is something I'm wondering about. Does anyone know the VBA function to copy an entire worksheet (name and everything) from one workbook to the next?

At the end of the macro, the two opened workbooks should be closed without saving changes.

I already have user input boxes created, but do not know what to put in them for buttons and commands assigned to those buttons.

How do I do these things?

 

[mikerickson]

Are you talking about the Application.InputBox function or a UserForm?

To get the file path to a file, the GetOpenFileName method will return the file path.

 

[Masterpsyker]

I honestly don't know. I haven't used the Input Box before as the macros I've done in the past are typically used for "in-sheet" calculations.

Which one would be better?

I think a UserForm can be saved and reused, but I don't know if you can script the buttons to do different things depending on where in the program you are.

I need it (whatever box I use) to have a text-box that CAN NOT BE ALTERED which displays the path for confirmation, along with a button to browse your directory structure, and a button to continue to the next step. Cancel / No are not options which shouldn't be available to the user. Once the macro begins, it can not be stopped until it is complete (unless aborted with Ctrl+Break)

Once the box "ok" button is clicked, the workbook should be opened and the filename saved to a string for reference later.

Thank you for your quick reply Mr. Rickson

 

[Norie]

Have you tried Mike's idea of GetOpenFileName?

There's no way you could use an inputbox to browse for a file, that I know of anyway.

And if you want to restrict what the user is doing you should probably look into logical structures like If...End If.

Again, you couldn't really do this with an inputbox without those anyway.

 

[Masterpsyker]

Norie:

I don't know if the GetOpenFileName method will work if I have three open workbooks. Does it use the Active workbook as its reference for return?

The user only has to supply the path to the workbooks, if this could be done via the browse command, that would be optimal... however, if I can't do that from a UserForm or Application.InputBox then I guess I can instruct the user via prompts to open the workbooks manually.

If they do it this way, then I could have them select the workbooks in order and then GetOpenFileName to save the paths.

Is there any way to isolate the filename from the GetOpenFileName method so that I can use a:

 Set objWorksheet = ActiveWorkbook("FILENAME.XLS").Worksheets("SheetName")
objWorksheet.Activate
Range.Activate

segment when I move from one workbook to the next?

 

[Datsmart]

Here is some code that runs some steps that might help you:

Sub OpenAWorkBook()
'Assign current opened file to variable
    WB1 = ActiveWorkbook.Name
'Steer Open Dialog box to specific start folder
    ChDir "C:\~~~\Excel\"
'Initiate Open Dialog box
    Application.Dialogs(xlDialogOpen).Show
'Assign newly opened file to variable
    WB2 = ActiveWorkbook.Name

'Copy an entire worksheet to first workbook
    Workbooks(WB2).Sheets("Sheet1").Cells.Copy Destination:= _
    Workbooks(WB1).Sheets("Sheet2A").Range("A1")

'Return to other workbook and Close it without Save
    Windows(WB2).Activate
    ActiveWorkbook.Close SaveChanges:=False
End Sub

The code uses the Excel "Open Dialog Box" to browse to a file.

 

[Norie]

GetOpenFileName has nothing to do with the active workbooks or any other open workbook(s).

It does what it says - it gets a filename.

You then need to write code to open that file.

And you shouldn't need to 'move' between workbooks, especially if all you seem to be doing is transferring data and you shouldn't need to use Activate.

Just create references to them which you can use in later code.

For example:

Set wbThis = ThisWorkboook ' create reference to workbook the code is in

Set wbAct  = ActiveWorkbook ' create reference to the active workbook

Set wbOpen = Workbooks.Open("C:\MyPath\MyBook.xls")
' open workbook and create a reference to it

Set wbNew = Workbooks.Add ' create new workbook and a reference to it

 

[Masterpsyker]

Between the two of you, and some craftiness, I have devised a NEAR foolproof way to ensure that the correct workbooks are opened every time.

As long as the user follows my MessageBox prompts, the macro should work quite well.

The last thing I need to know is how to fill a range of selected cells with a given color(s). Does anyone know the cell property which controls the FILL color (not the text color).

When the macro is complete (and working) I'll post it here. If you guys don't mind, I would like someone to go over my methods and tell me if they think there's a more efficient way of doing things.

 

[Norie]

Eh, how can we go over your methods without seeing them?

My crystal ball is in the garage right now.

As to the fill thing, here are 2 suggestions.

1 Try Format>Conditional Formatting...

2 Turn on the macro recorder if you are doing the colouring manually. That should generate code that you can use as the basis of what you want to do.

PS You could also try the macro recorder with 1, though the code generated by both 1 and 2 will probably need considerable changes.

 

[Masterpsyker]

Just tried to run the macro... working smoothly up to the point where this code happens:

Workbooks(wb2).Sheets("Sheet1").Cells.Copy Destination:= _
Workbooks(wb1).Sheets("Sheet1A").Range("A1")

I get error code 34: Type Mismatch... why is this happening? I've tried removing the .Cells and .Range to just keep it as a worksheet to worksheet copy, but that didn't work either. Any advice?