PDA

View Full Version : Solved: msgbox displaying new workbook object path



edjohns
09-20-2012, 12:52 PM
hi

i'm trying to display a MsgBox which displays a workbook added and saved by a subroutine.

Sub ExportarIncidencia()
'

Dim new_report As Workbook
Dim user As String
Dim report_key As String
Dim folder As String
Dim success As String


report_key = Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now)
folder = SelectFolder("Selecciona una carpeta:", "")

file_path = folder & user & report_key

Set new_report = Workbooks.Add()
new_report.SaveAs Filename:=file_path & ".xls"

' DO STUFF WITH ACTIVE AND NEW WORKBOOKS

ActiveWorkbook.Save 'newly created workbook
ActiveWorkbook.Close



Windows("Formas.xls").Activate
Range("A1").Select

success = MsgBox("Se ha exportado el archivo:" & new_report, vbOKOnly)

Set new_report = Nothing

End Sub


what i am trying to do is let the user know the name of the new file that was just created.

i am getting a "object required" error (on the 3rd line from the bottom)
can't figure out whats wrong with this

thanks a lot guys

CatDaddy
09-20-2012, 01:00 PM
new_report.Name?

edjohns
09-20-2012, 01:15 PM
using new_report.Name i get Method Name of object workbook failed

i also tried new_report.Path before (to no avail)

BTW. thanks so much for your help... yet again! ;-D

CatDaddy
09-20-2012, 01:19 PM
replace new_report with user & report_key & ".xls" ?

Kenneth Hobs
09-20-2012, 01:26 PM
When you Compile the program, I assume that you have a routine called SelectFolder()?

I don't see where the value for user is set.

If you step through the code with F8, you should be able to determine if you need to add a backslash character or not after your SelectFolder routine.

If SaveAs is used, Save should not be needed.

Before closing the workbook, put the .Name property value into a string variable.

edjohns
09-20-2012, 04:51 PM
thank you both.

i will try these solutions tomorrow when i have access to the file again.

i do have a SelectFolder() function in the project which sets a selected folder from a dialog box to a string.

the variable user gets its value from a cell in a worksheet .

and i needed to use save and save as because otherwise i could not change the active window, although i guess i could use window(index) for both, but since it works fine, well.

and now that we are at it, i was wondering how i could do something like ThisWorkBook.Activate or similar, in case the workbook name is changed sometime.

thanks

snb
09-21-2012, 03:39 AM
Sub ExportarIncidencia()
with Workbooks.Add
.SaveAs SelectFolder("Selecciona una carpeta:", "") & FormatDateTime(Now, 0) & ".xls"
MsgBox "Se ha exportado el archivo:" & .fullname
.close true
end with
End Sub

edjohns
09-21-2012, 08:03 AM
thanks everybody