Repalcing sheets with VB excel

[Kavy]

Hello all, I have no idea where to start on this one!

I have a sort of a program in excel, which the point of is for the user never to actually see any worksheets, only my forms.

Theres lots of worksheets, but its all based off one and does work from there. This sheet may get updated from time to time so I would like the user to be able to do this him self so I have two questions

1. If I had a form with a textbox option to enter or a directory (or browse if anyone knows how to make that work) could the user enter the file name for the workbook, and the name of the updated sheet in that work book and click a command and have it replace my sheet?

2. This sheet is a list of equipement and its detials, I would like to tell the user if any new items were added (check by tag number, collumn 3) and also if any details for the already existing equipments were changed, (only collumns 4,9,10,11) Since the row that the equipment was in could change from updated sheet to upfdated sheet, the only way I no of is by searching for the existing equipment on the old sheet, then comparing the collumns, then moving to the next equipment piece. After thats done compare every tag from the new sheet on to the old sheet and see if theres any new ones. Then replace the sheets

Any ideas

Thanks a tons!

Shaun

 

[Kavy]

just to re-post, anyone?

 

[Gates Is Antichrist]

Your answer to 2 sounds right. To simplify, I'd add information like "date added" and "date changed" in the main worksheet record, and just check for greater than or less than some date (perhaps input by the user).

For 1, you can use a standard file dialog with

dim sPath as string, sPathShort as String
sPath = Application.GetSaveAsFilename(, , , Title:="Set folder")
If CStr(sPath) = "False" Then msgbox "Bye": Exit Sub
sPathShort = Left(sPath, InStrRev(sPath, "\") - 1)

and work from there.

Once you open the file you could present a listbox of sheet names and let them pick that way.

I can't remember why I put the last statement in there above. Maybe I was just showing off my manual splitting technique

 

[Gates Is Antichrist]

By the way, don't let the "SaveAs" name bother you. Nothing gets saved. That command just gives you the interface and returns the name of the item selected. (Gives you the full path and name, that is.)

 

[Kavy]

Thanks for your reply! i have been trying to figure it out i am not good with crtls.

What does the last statment do?

i figured out the getsaveasfilename statment or to get it to work, if i click a file what will it do, i am scared to try i might save over some work or sometrhing

 

[Kavy]

ah spath is the final file name they pick. Can i do a test to see if its an excel file or not?

 

[Kavy]

This is what i got so far, i can't get it find set e to the workbook!
Thanks for your time!

 

[Kavy]

Heres what i have now, any comments?

<code>
Dim e As Workbook
Dim sPath As String, sPathShort As String
sPath = Application.GetSaveAsFilename(name, , , Title:="Set folder")
If CStr(sPath) = "False" Then MsgBox "Bye": Exit Sub




sPathShort = InStrRev(sPath, "\", -1, vbTextCompare)

sPathShort = Mid(sPath, Len(sPath) - sPathShort)

Workbooks.Open (sPath)



Set e = Workbooks(sPathShort)
listFile.AddItem
listFile.list(i) = e.Sheets

</code>

 

[Gates Is Antichrist]

Sorry for the delay. I wish others had pitched in.

sPathShort is probably not useful for you, as I defined it. It is just the folder name, and sPath is folder name + filename.

You changed sPathShort to use it as an integer, because InStrRev returns an integer. You don't need sPathShort, and you don't need InStrRev. Just open sPath as you've done, and go
(add dim sht as worksheet at the top)

For Each sht In ActiveWorkbook.sheets
	listfile.additem sht.name
next

and go from there.

Other guys feel free to help out - my time's limited.

 

[Gates Is Antichrist]

Remember to save off the location(i.e. name) of your original (active) workbook before opening the other one. You'll need to return there to replace the contents that they chose in the listbox.

I suppose you'll go something like activeworkbook.sheets(listbox.Value).copy and paste it into the original workbook.

Again, others please also help, since I have to leave now.