Posts by Quickhelpplz

    I've got a Macro that opens a Stability file and reformats that file and then gets the save-as name based off of certain parameters in the stability file. Sometimes we have more than 1 Stability file with the same parameters and it wants to save over the original file.
    Instead, I want a box to pop up so the user can name the file what he wants. Ive got this all to work so far.
    What I need, is to specify the file format so that when the user saves the file, its .xlsm. Right now it defaults to .txt. How do I go about getting the save-as box to pop up already in .xlsm file format?


    I got it so the Anchor would take. This is what I have now.



    Code
    1. Dim sXLFile As String
    2. Dim Anchor As Object 'I don't think I need this
    3. Dim TextToDisplay As Object 'I don't think I need this either but put them in there just in case
    4. Dim cell As Range
    5. Sheets("Duct TC Map").Activate
    6. sXLFile = "U:\ENG\Lab\Lab 3\EH Test Notes & TC Maps.xlsx"
    7. ActiveSheet.Hyperlinks.Add Anchor:=Cells(cell.Row, 1), Address:=sXLFile, TextToDisplay:="Add Duct TC Map"


    Im getting the following error on the very last line.

    Run-time error '91':
    Object variable or With block variable not set

    Im trying to create a hyperlink on a sheet using vba but I cant get it to execute. The Anchor part doesn't seem to take it my code since the 'a' is not capitalized. What do I need to correct or is there a different way to do it?


    Code
    1. Dim sXLFile As String
    2. sXLFile = "U:\ENG\Lab\Lab 3\EH Test Notes & TC Maps.xlsx"
    3. ActiveSheet.Hyperlinks.Add anchor:=Cells(1, A), Address:=sXLFile, TextToDisplay:="Add Duct TC Map"


    Thanks in advance for any help provided.

    I don't know why this is so difficult to find, but im not sure how to edit my title. Ill keep looking and change it if I find the edit button.


    The code that you provide SO worked so thank you for your assistance with that.

    do you want me to change my title?


    The Application.Getopenfilename opens the folder that is the same location to which my main document with the Macro is in, however I want to open a specific file path that is on the shared drive so that I can select whatever file I want in there, and then it will copy all the sheets into my main document with the Macro in it.


    I'm using Shell, because that was the only thing online I found that worked. To be honest, I have no idea what shell does. is there a way to use the application.getopenfilename to opena specific path?


    I hope this helps clarify my problem.

    Ive got a Macro File that is to open a folder, the user selects the file in that folder, and it copies all sheets into the Macro File. This is what I have,



    This part works. What I want to do is change the 'filename = Application.GetOpenFilename' to 'filename = Shell("explorer.exe U:\ENG\Lab\Master Lab Files & Logs\Lab Electronic Test Requests", vbNormalFocus)' so that it looks like the following where it then stops on the line 'set wbsource=application.workbooks.open(filename)'


    What am I doing wrong here?

    Okay, Ive got it down to open the file location I want. Im not sure what Shell does, but it works.
    Now I select the file I want, and I need it to copy all tabs on that file and insert them into my original document.



    [VBA]Private Sub CommandButton1_Click()
    Call selectfile3
    End Sub
    Sub selectfile3()
    Dim TestRequest As Long
    TestRequest = Shell("explorer.exe U:\ENG\Lab\Master Lab Files & Logs\Lab Electronic Test Requests\", vbNormalFocus)

    End Sub[/VBA]

    Im trying to write a Macro in a summary document so that I can open a specific browsing path, the user would select the file they want to use from the path, and then once they select the file, it would copy all the tabs in that document and move/copy them into the summary document.


    So what I have so far is the below code
    [VBA]Sub selectfile1()
    Dim fNameAndPath As Variant


    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")


    If fNameAndPath = False Then Exit Sub
    Workbooks.Open Filename:=fNameAndPath
    End Sub[/VBA]


    But it doesn't open the specific path I want which is U:\ENG\Lab\Master Lab Files & Logs\Lab Electronic Test Requests\


    How do I go about this?
    I know how to opne up a specific file in a specific path and copy it into my workbook, but the specific file can vary and I want that option to be there.


    Michael

    Okay I got it. I used the following
    [VBA]
    Application.DisplayAlerts = False
    Range("A8:P29").Select
    Selection.Copy
    Sheets("Room Inital Component Sheet").Select
    Range("A8:E8").Select
    ActiveSheet.Paste
    Sheets("Room Final Component Sheet").Select
    Range("A8:E8").Select
    ActiveSheet.Paste
    Application.DisplayAlerts = True[/VBA]

    I've got a Macro that I'm working on where depending on what project we are doing, it will format the pages a certain way. After I format it, I use the macro to copy and paste the info across other sheets in the same workbook. Those cells already have data in them.
    The box that pops up says "there is data already here. do you want to replace" options are "yes" or "no". If I want it to select yes automatically, is there a way to do that in the code?

    First let me start off by saying, thank you for the response and your willingness to help. The code that you put does not make complete sense to me and seems more complicated.
    This is the above code I ended up using.


    [VBA] If Lab2meterlog = True Then
    Workbooks.Open Filename:= _
    "U:\ENG\Lab\Master Lab Files & Logs\Logs\METER LOG.xls"
    Sheets("Lab #2 Meter Sheet").Select
    Cells.Select
    Selection.Copy
    Windows("Documentation Template1.xlsm").Activate
    Cells.Select
    ActiveSheet.Paste
    Windows("METER LOG.xls").Activate
    Application.DisplayAlerts = False
    ActiveWindow.Close
    End If[/VBA]

    Hello,


    What I'm doing is to have a command button that opens a userform with 5 separate buttons on it (One for each our 5 labs so Lab 1, Lab 2, Lab 3, Lab 4 and Lab 5). After it opens the form, the technicians would select the lab they are in and then the form would open a file on the server (U:\END\Lab\Master Lab Files & Logs\Logs\Meter Log.xls) and copy a tab out of that excel document and paste in into the current excel sheet. This part I have done. After that, I want it to close the document (U:\END\Lab\Master Lab Files & Logs\Logs\Meter Log.xls) after the information that I wanted has been copied out of it.


    The code I have is as follows


    [VBA]Sub Executemeterlog_click()



    If Lab2meterlog = True Then
    Workbooks.Open Filename:= _
    "U:\ENG\Lab\Master Lab Files & Logs\Logs\METER LOG.xls"
    Sheets("Lab #2 Meter Sheet").Select
    Cells.Select
    Selection.Copy
    Windows("Documentation Template1.xlsm").Activate
    ActiveSheet.Range("A1").Paste
    End If



    End Sub[/VBA]


    Im sure there are other ways to the above, but this is what I got based on using the record feature.


    Thanks


    Michael

    Re: Can i call out a run-time error in the code. If this happens, do this?


    I wasn't sure if the code would be necessary for that.
    The portion of the code that it errors on is as follows.



    Sometimes we have multiply files in the same location and it is designed to name them all the same. If the above save name is taken, I want the option to save it as a different file. I will check out the site you put on there.


    Thanks