Posts by Quickhelpplz

Ill have to get some approval on that since these are work documents and contain sensitive material.

If you can still help without it at this point, that would be great.

Ill try to explain whats going on in more detail and maybe thatll give you an idea of where im going without the documents.

I have my Summary Document that all the code is in. It displays a button on the page that allows the user to open a file (wbSource) to be summarized located in the same place as the Summary Document. After which it opens a userform that will allow me to select a begin column and end column on wbSource for the test being summarized.

The issue im currently working on now is 1. the file that i opened (wbSource) thru the summary document, i want to call it out in the code to specify between the 2 files when summarizing. So the just opened file i want to set = wbsource but i dont know how to do that yet. Ive got

Set wbTarget = ActiveWorkbook 'to call out the summary document

Im using the userform in document #1 to open another file in the same location as document #1 where ever its located on the server. Thats just the start. In the end it is intented to summarize test data.

That got me a little further but didnt do it. I found a webpage on opening files and it really helped. https://powerspreadsheets.com/vba-open-workbook/

This is the code i ended up using.

Private Sub TestFileButton_Click()

Dim TestData As Variant

Dim OpenPath As String

OpenPath = ActiveWorkbook.Path

ChDrive OpenPath

ChDir OpenPath

TestData = Application.GetOpenFilename(FileFilter:="Excel Files, *.xl*;*.xm*")

If TestData <> False Then

Workbooks.Open FileName:=TestData

End If

End Sub

I made a Macro for summarizing tests at work a while ago and it works good. Im now tyring to make another one 3 years later for a different test slightly differently. Im trying to make a macro that will open a directory folder using the current workbooks path and from there i can select an excel document to open. The file is not opening when i select a file but directory opens in the correct spot.

Here is the code im using.

Private Sub TestFileButton_Click()

    Dim Directory  As String
    Dim OpenPath   As String
    
    OpenPath = ActiveWorkbook.Path
    ChDrive OpenPath
    ChDir OpenPath


    Directory = Application.GetOpenFilename(FileFilter:="Excel workbook (*.xls),*.xls", Title:="Open data")

    
End Sub

to take it a step further, after that document opens, i want another user form (i feel ill get stuck on this part) to open so i can set the parameters for the test being summarized. For example, the start column, how many columns in that test and a few others and then macro will auto populate.

Thanks in advance

Okay I figured out how to specify the save as format

[VBA]wbTarget.SaveAs Application.GetSaveAsFilename(filefilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")[/VBA]

The other option which seems more complicated to me but instead of having the user name the file if the original is taken, would be to have an "a" added to the original file name and then a "b" and so on and so forth. So it does it all automatically.

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?

runNum = wbSource.Worksheets(1).Range("B3").Value  'Gets Run Number from Stability File
    runStr = "Run #" & runNum
    saveAsName = wbSource.Worksheets(1).Range("B2").Text ' using test file as filename
    saveAsName = saveAsName & " " & runStr   


    wbTarget.Worksheets(1).Range("A1").Select
    On Error Resume Next
        wbTarget.SaveAs wbSource.Path & "/" & saveAsName & ".xlsm", FileFormat:=52
    If Err.Number <> 0 Then
        wbTarget.Worksheets(1).Range("A1").Select
        wbTarget.SaveAs Application.GetSaveAsFilename' This is where it opens up as .txt format but I want it to default to .xlsm
    End If
    On Error GoTo 0

I got it to work by changing

Anchor:=Cells(cell.row,1),

to

Anchor:=ActiveSheet.Range("A1")

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

Dim sXLFile              As String
    Dim Anchor               As Object 'I don't think I need this
    Dim TextToDisplay   As Object 'I don't think I need this either but put them in there just in case
    Dim cell                     As Range
        Sheets("Duct TC Map").Activate
        sXLFile = "U:\ENG\Lab\Lab 3\EH Test Notes & TC Maps.xlsx"
        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?

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

Thanks in advance for any help provided.

Thank you Robert for your assistance.
Which code tags should I have used so I know for the future? I used the [VBA] ones I thought.

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,

Private Sub btnSelectFile_Click()
Dim SourceName As String
Dim filename As String
Dim wbTarget As Workbook 'workbook where the data is to be pasted
Dim wbSource As Workbook 'workbook from where the data is to copied
Dim sh As Worksheet
Dim wb As Workbook


filename = Application.GetOpenFilename
Application.EnableEvents = False
Application.ScreenUpdating = False


Set wbTarget = ActiveWorkbook
Set wbSource = Application.Workbooks.Open(filename)
Set wb = wbTarget


wbSource.Activate


For Each sh In wbSource.Worksheets
sh.Copy Before:=wb.Sheets(wb.Sheets.Count)
Next sh


wbTarget.Activate


Application.DisplayAlerts = False
wbSource.Close


Application.DisplayAlerts = True
wbTarget.Activate


End Sub

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)'

Private Sub btnSelectFile_Click()
Dim SourceName As String
Dim filename As String
Dim wbTarget As Workbook 'workbook where the data is to be pasted
Dim wbSource As Workbook 'workbook from where the data is to copied
Dim sh As Worksheet
Dim wb As Workbook


filename = Shell("explorer.exe U:\ENG\Lab\Master Lab Files & Logs\Lab Electronic Test Requests\", vbNormalFocus)
Application.EnableEvents = False
Application.ScreenUpdating = False


Set wbTarget = ActiveWorkbook
Set wbSource = Application.Workbooks.Open(filename) 'it stops on this line
Set wb = wbTarget


wbSource.Activate


For Each sh In wbSource.Worksheets
sh.Copy Before:=wb.Sheets(wb.Sheets.Count)
Next sh


wbTarget.Activate


Application.DisplayAlerts = False
wbSource.Close


Application.DisplayAlerts = True
wbTarget.Activate


End Sub

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]

I’m too new to coding to understand that. What is ado connection?