How can I reference another spreadsheet to open within excel using a command?

I want to have 2 spreadsheets in File 1.

The first spreadsheet will contain a form that needs to be filled out by a user for a specific car.
In the second spreadsheet, I want to have a list of the cars that have already been filled out in a form before.

So example, someone with a car got into an accident and the report is filed under vin:1234 and vin:1234 is added to the list in the second spreadsheet.
A couple days later another accident report is written up, but when the user enters vin:1234 in the form, it will notify the user that vin:1234 has a report already written up on it. The notification also asks if they want to open the file containing vin:1234. When they click 'yes', it will open the file and allow them to either use the current report for their own, or modify what was already entered.

Is this possible to do within excel? I would constantly update spreadsheet 2 with the vins that have been used to keep it as a database.

Originally Posted by Bernie Deitrick

If you store all the workbooks in the same folder, then you can use code like this - change the names and ranges as needed.

Please Login or Register  to view this content.

Ok, everything looks good code wise. I'm trying to figure out how to use it.
I'm fairly new to the visual basic concept with excel. I know Visual basic, but have always done Excel with basic formulas in the spreadsheet.

So if I wanted to test this out with brand new files, how would I do so?
This will allow me to understand how exactly the code works, and then I can apply it to my project.

Originally Posted by Bernie Deitrick

Use your workbook with two sheets - Sheet1 has all the info, Sheet2 has the record of what values have been used. They should be named "Sheet1" and "Sheet2", or change the code to match. On Sheet1, name a cell "CellWithID" using the name manager or the Name Box, where the key info will be stored. Then copy the code, insert a module into the project (in the VBE), then paste the code into that module. Save the file in a folder as a macro enabled workbook, .xlsm. Enter a value in the cell CellWithID, and run the macro.

Ok, I run the macro and it says the file exists. When I click to open it, it return with a VSE error of 400. Both files are in the same folder.

Fixed this error, I had saved the existing file as .xlsm and it was searching for an xlxs

So everything works now. Another questions I have: if I run the macros and it says the file exists, instead of opening it, is it possible to have that file open in a new sheet? So File2 would be added to my current file under Sheet 4.

Originally Posted by Bernie Deitrick

I interpreted "under sheet 4" to mean "after the last sheet"

Please Login or Register  to view this content.

Ok, this opens the second file, but does not have it open under Sheet4, meaning after the last sheet like you said.
It opens the second file, but gives an error "Subscript out of range".

When you use 'i' in "wBk.Sheets(1).Copy After:=ThisWorkbook.Worksheets(i)" what defines the 'i'?

Perfect, I knew something was missing haha

I appreciate all the help Bernie!

And of course another idea comes to mind. If I have a subfolder created for every different file, ie. File1 (Folder), file2 (Folder) etc..

Am I able to leave the form with the macros at the root folder and have it search for any file containing a set of specific letters?

By specific letters, I mean a file saved as abc1234, but on the form I would be typing in '1234' and want it to find 'abc1234'.

Originally Posted by GSirko

And of course another idea comes to mind. If I have a subfolder created for every different file, ie. File1 (Folder), file2 (Folder) etc..

Am I able to leave the form with the macros at the root folder and have it search for any file containing a set of specific letters?

By specific letters, I mean a file saved as abc1234, but on the form I would be typing in '1234' and want it to find 'abc1234'.

I figured it out, just editing the pathway for the file search.

Yeah I'm doing my best haha.

How would I make this work? I want to have a series of checks done for a file

Please Login or Register  to view this content.

What I'm trying to do is have a user input a number. That number has extra information attached to it in the folders and file it saves as. So if the user puts FM1168, I'm going to be searching for a couple of options:

QFM1168-12-001
QFM1168-12-002
QFM1168-05-001
QFM1168-05-002

So if the statements can find these files, I'd like them all to open up under new sheets

Originally Posted by Bernie Deitrick

Change each of these parts, from:

Please Login or Register  to view this content.

to this, to check if the file exists first

Please Login or Register  to view this content.

Perfect. Now one last problem, when I type in a number that doesn't exist yet, it adds it to the 'database' list. It opens a new book, but gives an error "Script Out of range"

It references: 'Sheets("SpecialOrderQuote").Range("A13") = numb' as the problem
The file only shows the first sheet being created.

This is what I have:

Please Login or Register  to view this content.

Ok so the reason for the error: Out of Range 'Sheets("SpecialOrderQuote").Range("A13") = numb'
When it makes a copy of the sheet, it gives the linkedcell (combobox) as the cell referenced in the original workbook.

So instead of a combobox linked cell as: SpecialOrderQuote!A13
It saved the combobox with a linked cell of: '[New quote system.xlsm]SpecialOrderQuote'!A13

The file isn't able to locate the file that it's trying to reference because when it saves, it puts the file in another folder(which is what I want it to do)

So what do I need to change to make sure the combobox linked cell remains connected to owns own workbook?

Originally Posted by Bernie Deitrick

That code should give you an error because as soon as you copy the first sheet, the activeworkbook will not have the second on that you want to copy. So copy the sheets of interest all at once:

Please Login or Register  to view this content.

Ok, now when it goes to save the new worksheet, it gives me the problem of not being able to keep the VB Project saved.

I changed the file save type in the code from "xlsx" to "xlsm" and it still gives me that problem.

Error: Wrong number of arguments or invalid property assignment.

It has 'ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\Q Numbers" & "\Q" & strV & ".xlsm", FileFormat:=52'
savecopyas is the problem apparently

yep that did it haha

Is there any way to automatically close the file? It opens the file, saves a copy with the proper file name and then has a separate version to copy I guess?
It asks me to save changes to Book8 (since I've run it a few times.) I'll always want to hit no since it's a bogus file and not needed at all.

I need to make this as user friendly as possible for some of our old school people who will be using this.

I'm not sure which file it is either. I run the macros and it creates the file, saves it in a folder and I'm left with a file named book8 which is trying to close and asks me to save.

Please Login or Register  to view this content.

Perfect, I also had to add in the FileFormat:=52'
Since it was changed to SaveAs

No worries.

Instead of running the macros every time manually, am I able to have macros run after a cell is edited? So when they type a serial number into a cell, it will automatically run the macros and do the search.

Well I have to quote in terms of time. So if I quote an item, and it takes 7 hours to make one, they get a price break for ordering 2 items. So the first item will be charged the regular amount of labor with 7 hours, but for the second item, every hour will now only take 40 minutes instead of 60 minutes.

Sounds good, I couldn't wrap my head around the code. I ended up just coding within the cells to get it to work.
So my last question for this project, since it'll be done now, my cell that adds up the labor price does not refresh itself. So when I change the quantity in my combobox, it changes the values I have in the calculation. My total price is updated, but my price that calculates the amount for labor does not update.

The value is getting through to my total cost cell which adds a cell and the total cost of labor cell, because it updates every time I change the quantity. My total labor hours doesn't change though, unless I minimize the screen and come back to it. I've checked my excel options and calculation options for automatic updates.

Any idea how to fix this for an instant update, rather than a minimize and fix?