MrExcel.com - Your One Stop for Excel Tips & Solutions

buttons for macros

Posted by steve on December 28, 2001 6:40 AM

Hello,

I have a macro that reads in log files one of our printers generates and seperates the info so we can track usage. I am having a problem with opening files though. the macro opens a file from the printers folder then saves it in the same folder with a new name. when i run the macro from the built in blue arrow macro button, it works fine. when i run the macro from the macro button is where i run into trouble. when i do that it wants to run the macros in the last file the macro saved and not from the workbook the macro was originally in. i then get an error message saying "false.xls" couldn't be found and it refers me to the code in the macro for opening the file. naturally there is no "false.xls" in there anywhere. finally the program exits with an error code 400 in VB. i doubt this is really easy to follow, its kind of hard to explain but if you could thatd be the bee's knees

Posted by Scott on December 28, 2001 6:45 AM

Steve, Can you post your code for this macro?

Posted by steve on December 28, 2001 6:47 AM

"that should read when i run the macro from the macro button i made...."

Posted by steve on December 28, 2001 6:53 AM


Scott, i sure could but it is kind of a large program. if you'd like it all, id be happy to send but maybe it'd be better for you if i pasted the code that opens and saves files..either way is ok with me

Posted by steve on December 28, 2001 6:56 AM

*******Open File*****************************

Sub InputFile()
myfile = ""
myfile = Application.GetOpenFilename("text files, *.log")
Workbooks.OpenText Filename:=myfile, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), _
Array(12, 1), Array(13, 1), Array(14, 1))

ActiveSheet.Select
ActiveSheet.Move Before:=Workbooks("Kip_Prog.xls").Sheets(1)
Range("A:A,B:B,D:D,F:F,G:G,I:I,J:J,K:K,L:L,M:M,N:N").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub

'*************Save File**************************
Sub SaveKip()
Dim strOldSheet, strNewSheet As String

strOldSheet = ActiveSheet.Name
ActiveSheet.Name = strOldSheet & "-Processed"
Sheets("Other").Select
strNewSheet = ActiveSheet.Name
ActiveSheet.Name = strOldSheet & "-Other"
Range("A1").Select
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.SaveAs Filename:= _
"H:\Kip Logs\" & strOldSheet & "_Processed.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Sub

Posted by Scott on December 28, 2001 6:59 AM

Steve, I think that the button is referring to the macro on false.xls and not on your current workbook. So when you run it from the macro menu, it uses the one on the workbook, but when you use the button, it looks for the one on the other workbook. You can try to right click on the button and re-assign the macro and make sure it is using "this workbook". Then save the file.

Posted by steve on December 28, 2001 7:02 AM

what is false.xls? i don't have that on my hard drive anywhere. thanks again Steve, I think that the button is referring to the macro on false.xls and not on your current workbook. So when you run it from the macro menu, it uses the one on the workbook, but when you use the button, it looks for the one on the other workbook. You can try to right click on the button and re-assign the macro and make sure it is using "this workbook". Then save the file. :


Posted by Scott on December 28, 2001 7:05 AM

Not sure really. I would try to reassign the button to the macro again making sure it's using the macro on "this workbook". Other than that, I'm at a loss.

what is false.xls? i don't have that on my hard drive anywhere. thanks again : Steve, I think that the button is referring to the macro on false.xls and not on your current workbook. So when you run it from the macro menu, it uses the one on the workbook, but when you use the button, it looks for the one on the other workbook. You can try to right click on the button and re-assign the macro and make sure it is using "this workbook". Then save the file.