Replies

Before you push the order button…

WD is a good choice but they make more than one type of NAS (as do others). I strongly suggest that you get a mirrored drive. This will help safeguard your data should something happen to one of the two drives. It is a more expensive solution, but is well worth the extra bucks.

Thanks………and I almost did do just that. However in the end I decided to stay with the single bay and my present back up system of a separate SSD cloned drive sitting on the shelf. While this NAS data drive won’t get cloned it is only holding data files a simple full disk back up via the USB port will be more than adequate. While the NAS HDD is 2tb we actually have less than 100gb of data files.

I prefer this method because this way the back up drive is not permanently connected to the system. Any strange electrical or hacking event cannot touch it if it is sitting on a shelf in the closet.

Thanks again everyone. The WD NAS should be here on Thursday. I’m looking forward to connecting it into the network.

BH

Thanks guys.
I had actually found that WD NAS on Amazon and was settling in on ordering it. Thanks for confirming it as a good choice.
BH

Resolved. I just eliminated the SendKeys command and added Application.DisplayAlerts = False.
Thanks,
BH

Application.DisplayAlerts = False
‘ SendKeys “{Y}”
ActiveWorkbook.SaveAs Filename:=”D:MS OFFICE DOCUMENTSINVOICESPACK_SLIP.xls”, _
FileFormat:=xlNormal, Password:=””, WriteResPassword:=””, _
ReadOnlyRecommended:=False, CreateBackup:=False

Maud,

That was close. A little editing and now this finally does the trick and brings it back to the original local folder to open the next file. Thanks for all your help.
BH

‘ THE FOLLOWING STATEMENT SAVES THE NEW QUOTE FILE TO A NETWORK LOCATION
Range(“A1”).Select
Set Workbook = ActiveSheet
fname = Application.GetSaveAsFilename(“\OfficedMS Office DocumentsQuotes” & fname & (“.xls”))
Workbook.SaveAs Filename:=fname

‘ This changes to the AAQuote Local Directory
ChDir “D:MS Office DocumentsQuotes”

BH,

Please use one of the following codes to save your workbook to a network folder. Not sure if your intention is to prompt for a name.

Code:

Sub Save_File()
[COLOR=#008000]’SAVE FILE TO NETWORK FOLDER AS .XLS
‘————————————
‘SAVE WITH SAME NAME AS CURRENT WORKBOOK NAME[/COLOR]
    fname = ThisWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8

[COLOR=#008000]’————————————
‘SAVE WITH A PROMPTED NAME[/COLOR]
    fname = InputBox(“Enter file name with no extension”, “Save File as .xls”)
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8

[COLOR=#008000]’————————————
‘SAVE WITH NAME USING A CELL VALUE[/COLOR]
    fname = Range(“B3”) [COLOR=#008000]’ex: cell B3= “Yearly Quotes” without quotes[/COLOR]
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8

[COLOR=#008000]’————————————
‘SAVE WITH NAME USING VALUE FROM TEXTBOX ON A FORM[/COLOR]
    fname = Userform1.textbox1.Value
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8
End Sub

Maud,

Thanks. Will try in the morning.

I need to prompt for a name when the save is made so your 2nd suggestion should do the trick. Even better than before it would look like as now I think it is defaulting to the .xls file extension for us.

Thanks again,
BH

Hi BH,

The changes made in blue should resolve your issue. It will save to your local computer by prompting for a name then save to your networked computer using the new path but same name.

Code:

‘…..
Range(“A1”).Select
Set Workbook = ActiveSheet
fname = Application.GetSaveAsFilename(“.xls”)
Workbook.SaveAs Filename:=fname
[COLOR=#0000FF]s = Split(fname, “”)
fname = s(UBound(s))[/COLOR]
ChDir “\OfficedMS Office DocumentsQuotes”
ActiveWorkbook.SaveAs Filename:= _
“\OfficedMS Office DocumentsQuotes”[COLOR=#0000FF] & fname[/COLOR], FileFormat:= _
xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False
‘….
End Sub

HTH,
Maud

Note: this was untested

Drats, didn’t work. As written above it prompts to save on the local HDD but then fails. It also fails without the first 4 lines of code. It’s saying something like S=split when I point at that line highlighted in yellow after the failure.

I don’t know if it matters but this is for Excel XP. I’ve maintained using it over the years because of the hassle it would be to deal with the macros on more recent versions.

Thanks,
BH

BH,

in your original code, you were opening a save as dialogue box and prompting for a new name to save it to your local computer. If you need tostill change the name, then why not just do a normal File > Save As > navigate to your network folder > Save?

That’s what I was trying to do in the macro unsuccessfully. When I was adding the network path it was changing how it was working. I know a little more now about how it should be worded though so will try again.

Thanks,
BH

Hi BH,

The changes made in blue should resolve your issue. It will save to your local computer by prompting for a name then save to your networked computer using the new path but same name.

Code:

‘…..
Range(“A1”).Select
Set Workbook = ActiveSheet
fname = Application.GetSaveAsFilename(“.xls”)
Workbook.SaveAs Filename:=fname
[COLOR=#0000FF]s = Split(fname, “”)
fname = s(UBound(s))[/COLOR]
ChDir “\OfficedMS Office DocumentsQuotes”
ActiveWorkbook.SaveAs Filename:= _
“\OfficedMS Office DocumentsQuotes”[COLOR=#0000FF] & fname[/COLOR], FileFormat:= _
xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False
‘….
End Sub

HTH,
Maud

Note: this was untested

Thanks Maud,

What if I don’t need to save it on the local computer? Just delete the local computer top 4 lines of code? Or do I need those to predefine fName? I’d just try it but I’m at home now instead of at the office.

LIKE THIS BELOW?

Code:

‘…..

[COLOR=#0000FF]s = Split(fname, “”)
fname = s(UBound(s))[/COLOR]
ChDir “\OfficedMS Office DocumentsQuotes”
ActiveWorkbook.SaveAs Filename:= _
“\OfficedMS Office DocumentsQuotes”[COLOR=#0000FF] & fname[/COLOR], FileFormat:= _
xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False
‘….
End Sub

Thanks,
BH

bh,

In Post 12, try removing the space between ISTEXT and (J12)

TH,
Maud

THANKS !!
That would appear to have solved the problem.
BH

Hi BH

copy this and try it in cell [O27]

Code:

=IF(NOT(ISNUMBER(J12)),"",IF(ISNUMBER(SEARCH("NON",C14)),0,IF(OR(ISNUMBER(SEARCH("jamb",J16)), ISNUMBER(SEARCH("strip",J16)), C14="STRAIGHTS",C16="MDF",C16="PVC"),0,IF(C14="SERPENTINE",0,IF(C12="",0,IF(C14="ELLIPTICAL",J12+3,IF(C14="OVAL",J12+3,ROUND(AD20*16,0)/16)))))))

..let us know if this works?

zeddy

Thanks, I’ll give it a try in the morning.
BH

Hi BH

re:Identifying text vs. a number in a cell question
..perhaps you just need the =ISTEXT( function ???

zeddy

That would be awesome if I could get it to work. I tried this at the beginning of a cell formula to no avail. Obviously I’m doing something wrong as it didn’t return a blank cell. That is the same format I use on an ISNUMBER(SEARCH) function with a search phrase added so it was of course my first thought.

=IF(ISTEXT (J12),””,IF(ISNUMBER(SEARCH(“NON”,C14)),0,IF(OR(ISNUMBER(SEARCH(“jamb”,J16)), ISNUMBER(SEARCH(“strip”,J16)), C14=”STRAIGHTS”,C16=”MDF”,C16=”PVC”),0,IF(C14=”SERPENTINE”,0,IF(C12=””,0,IF(C14=”ELLIPTICAL”,J12+3,IF(C14=”OVAL”,J12+3,ROUND(AD20*16,0)/16)))))))

BH

RG’s is simple. “IFERROR” was a nice function added to recent releases.

Also, suppose you replaced the two instances of “J12+3” with if(isnumber(J12),J12+3,0)

Thanks everyone. I’ll give both suggestions a try.
BH

BH,

You mean something like this:

45255-check-for-numbers

Formula: [noparse]=IF(AND(ISNUMBER(C1),ISNUMBER(D1),ISNUMBER(E1)),C1+D1+E1,0)[/noparse]

HTH :cheers:

Well, that got me thinking a little more. Maybe an example would help.

Here is the formula from from cell O27:

=IF(ISNUMBER(SEARCH(“NON”,C14)),0,IF(OR(ISNUMBER(SEARCH(“jamb”,J16)), ISNUMBER(SEARCH(“strip”,J16)), C14=”STRAIGHTS”,C16=”MDF”,C16=”PVC”),0,IF(C14=”SERPENTINE”,0,IF(C12=””,0,IF(C14=”ELLIPTICAL”,J12+3,IF(C14=”OVAL”,J12+3,ROUND(AD20*16,0)/16))))))

You’ll see J12+3 referenced near the end of this formula. That is referencing the width of a part. Normally I will enter 3, 3 1/2, 4 etc as the width of the part but every now and then I want to enter something like “any width” in J12 for information on the printout. In that case I’d want cell O27 to remain blank or 0, but instead it returns an errror since J12 does not contain a number. And since other cells act in relation to what is in O27 I end up with errors in numerous places around the worksheet.

Helpful?
BH

I’m not clear to me what you mean by “any text.”

This is what I understand from your post: =IF(isnumber(cell), SOME_FORMULA,””) where if the cell does not contain a number, the result will be a blank. Change the “” to whatever you want to be there if the cell does not contain a number.

Maybe post a sample file?!

Thanks for bearing with me on this.

Unfortunately a sample file isn’t going to help here.

I have complex sheets with a lot of cross references between cells. Various cells react different ways depending upon what is in other cells.

All these cells depend upon number references. If one cell has 25 entered for example, and the one next to it has 35 entered, then several other cells will return specific number results based upon those entries. If the numbers go up or down in those first two cells then the formulas in the other cells with make different calculations and give different results.

The occasional problem is that every now and then I’ll need to enter text in one of those first 2 cells so the person using the print out of all this will get some textual information. This is when an error is returned in some of those other cells…………..because the formulas in those cells are reacting to numbers…..not text.

The text that could be typed in occasionally could be any word or words so I can’t just specify a single word or group of words that will result in the cell with the text being ignored.

I hope I’ve explained this a little more clearly now.

Thanks,
BH

Is the issue within a macro or on the sheet itself? ISNUMBER(cell) will return true or false depending on the cells format.

The issue is within the sheet……..not the macro.

I use ISNUMBER quite a lot but for specific text. Is there a way to use it for “any text”?

Thanks,
BH