Forum Discussion

jaredcott6's avatar
jaredcott6
Copper Contributor
May 05, 2023

VBA issues with automation

Hello, I am currently trying to make a "template" to use for work. I have the data collection and averaging already done I just need a quicker way to get the data into my workbook. Currently I am using a Button to trigger the start of the report:

 

Sub ImportCSVFile()

Dim Ws As Worksheet
Dim FileName As String

Set Ws = ActiveWorkbook.Sheets("DATA") ' change to suit

FileName = Application.GetOpenFilename("Text Files (*.csv),*.csv", , _
"Please select source file")

With Ws.QueryTables.Add(Connection:="TEXT;" & FileName, _
Destination:=Ws.Range("A2")) ' change to suit
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
End Sub

 

This is the script I am currently using. I need to have it also Save As immediately with a dialog box as well as reopen the new report. Thank you!

Formulas and Functions
Macros and VBA

1 Reply

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 08, 2023

    jaredcott6 

    You can try to add the following code to your existing script to achieve this:

     Just make sure to place the new code after the End With statement in your existing script.

    ' Save the file with a dialog box
Dim NewFileName As Variant
NewFileName = Application.GetSaveAsFilename(InitialFileName:=FileName, _
FileFilter:="Excel Files (*.xlsx), *.xlsx")
If NewFileName <> False Then
    ActiveWorkbook.SaveAs Filename:=NewFileName
End If

' Reopen the new report
Workbooks.Open NewFileName

     

    I hope this helps!

    NikolinoDE

    I know I don't know anything (Socrates)

Resources