none
Run-Time error '1004' ---- Please view my VBA codes RRS feed

  • Question

  • Hi Everyone

    Please see my below code

                                   

    Sub Manual_Coversheet()

        Application.ScreenUpdating = False

        Dim Soeidno, Mypath, Mypath1

        Mypath = "C:\Users\"
        Soeidno = InputBox("Please state your Soeid no.")
        Mypath1 = "\Desktop\Coversheet\"


        Sheets("Details").Select
        Range("B2").Select

        Do Until ActiveCell.Value = ""

        Selection.Copy
        ActiveSheet.Previous.Select
        Range("B6").Select
        ActiveSheet.Paste
        Sheets("COVERSHEET").Copy
        Columns("A:G").Select
        Range("A3").Activate
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    ' This is where the Run-Time error '1004' Application-defined or object error occurs
    ActiveWorkbook.SaveAs Mypath & Soeidno & Mypath1 & ".xlsb", _
            FileFormat:=xlExcel12, CreateBackup:=False
        ActiveWindow.Close
        Sheets("Details").Select
        ActiveCell.Offset(1, 0).Select

        Loop

    End Sub

    The "Soeidno" is the employee id code and "mypath" and "mypath1" are the path for saving file.

    the code works with fine

    ActiveWorkbook.SaveAs Filename:="C:\Users\wb78131\Desktop\Coversheet\" & Range("B6").Text & ".xlsb", _
            FileFormat:=xlExcel12, CreateBackup:=False

    but i wanted the "WB78131" to be inserted via a inputbox, so that everyone in the team can use the macro without manually changing the employee id in vba codes.

    Can anyone please me understand on how to make this work or let me know if you  need info.

    Thanks in Advance and have a nice day!!!

    Wasimali Bori


    Tuesday, February 17, 2015 3:29 PM

Answers

  • You forgot to include Range("B6").Text:

        ActiveWorkbook.SaveAs Mypath & Soeidno & Mypath1 & Range("B6").Text & ".xlsb", _
            FileFormat:=xlExcel12, CreateBackup:=False

    You can also get the path to the desktop of the user as follows:

        Dim strDesktop As String
        strDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop")
        ActiveWorkbook.SaveAs FileName:=strDesktop & "\CoverSheet\" & Range("B6").Text & ".xlsb", _
            FileFormat:=xlExcel12, CreateBackup:=False


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Wasimali Bori Wednesday, February 18, 2015 4:12 PM
    Tuesday, February 17, 2015 4:31 PM

All replies

  • You forgot to include Range("B6").Text:

        ActiveWorkbook.SaveAs Mypath & Soeidno & Mypath1 & Range("B6").Text & ".xlsb", _
            FileFormat:=xlExcel12, CreateBackup:=False

    You can also get the path to the desktop of the user as follows:

        Dim strDesktop As String
        strDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop")
        ActiveWorkbook.SaveAs FileName:=strDesktop & "\CoverSheet\" & Range("B6").Text & ".xlsb", _
            FileFormat:=xlExcel12, CreateBackup:=False


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Wasimali Bori Wednesday, February 18, 2015 4:12 PM
    Tuesday, February 17, 2015 4:31 PM
  • Yup, i didnt see that.... i forgot the Range("B6").text.......... :)


    Thanks for the code, it works just fine. Can you help me understand the codes you have just put in or may be you can guide where I should look for the explanation on its working.


    BTW, you are a lifesaver!!!! and thanks for your time,, the second time!!!!

    Thank you,

    Wasimali Bori

    Wednesday, February 18, 2015 12:41 PM
  • WScript.Shell is a code library. It contains functions that let you deal with files, folders, drives and the Windows registry. One of these functions is SpecialFolders. It returns the path of "special" Windows folders, such as your desktop, your My Documents folder etc.

    See Working with Special Folders for an overview of what you can do with it.

    CreateObject("WScript.Shell") loads the code library temporarily (it is discarded automatically when the macro ends).

    CreateObject("WScript.Shell").SpecialFolders("Desktop") returns the path to the user's desktop, regardless of the version of Windows and of the name of the user.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, February 18, 2015 3:54 PM
  • Thanks for your reply Hans!! You are a rockstar!!

    I read the link provided by you but most of things goes over my head, total bouncer to me... :)

    I am doing courses on LYNDA.COM to understand VBA codes. 

    Can you recommend me some online sites or tutorials or videos which can help me understand the VBA stuff better..........

    Wednesday, February 18, 2015 4:18 PM
  • I wouldn't worry about stuff such as WScript.Shell for the moment - that's rather technical.

    Some other online tutorials for VBA in Excel:

    http://www.homeandlearn.org/

    http://www.excel-easy.com/vba.html

    http://excelvbatutor.com/vba_tutorial.html


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, February 18, 2015 8:01 PM
  • Thank you Hans!!!!

    Regards,

    Wasimali Bori

    Thursday, February 19, 2015 9:55 AM