Create new workbook and import data into it

[spewer]

Searching around the forums and I haven't been able to find a solution. I would like to do the following:

• Create a new workbook with two worksheets - Done
• Prompt the user to select a text file that contains the data - Done
• Take the text file and popluate it into one of the sheets in the new workbook - don't know how to do
• Take the data in the new sheet and create a summary in the remaining sheet in the new workbook - don't know how to do
• In order to create the summary, it would have to apply formulas to the new workbook - don't know how to do.

The idea is to have the main workbook that could go out and create a new workbook and do the data analysis. It would be up to the user to save the new workbook. But the bulk of the work will already be done for them.
Thanks in advance.
BTW, I don't have any source code to share yet. Just bits for the creation and prompt of the user is complete.

[Dennis7849]

Item:Take the text file and popluate it into one of the sheets in the new workbook - don't know how to do
_______Let's see a sample of the text file. With this text file, does it matter what data from it goes where?

Item: Take the data in the new sheet and create a summary in the remaining sheet in the new workbook - don't know how to do
_______This is possible but it requires the the format of the new sheet to always be the same each time.

Item: In order to create the summary, it would have to apply formulas to the new workbook - don't know how to do.
_______It would be better for this project to use a template that gets save after it is filled in. This means that you do not start a new workbook but rather save a template AS some name.

I would need a sample of the text file and a sample of what the workbook(with analysis) would look like in the end.

[JBeaucaire]

Yes, there are a myriad of people on the forum you could hire to simply do your project for you if you wish.

Meanwhile, you can still continue on your own with our tips if you get a little more specific with each question.

You can turn on the macro recorder and then manually do your entire project from start to end including creating your summary the way you want it to look.

Then, that source code coupled with your starting TXT file and your final result spreadsheet, the macro could could be tweaked to make it more dynamic and reusable.

You can learn simple things like how to enter a formula into a cell with VBA using the macro recorder to record you just typing the formula into the cell.

The recorded code would invariably need tweaking, but it's a great first step for learning the "vba" of anything you already know how to do manually.

[spewer]

Thank Jerry. I agree, I could just hire someone. However, I am trying help myself and others who may want to also tackle this type of issue in the future. This problem is a little harder than what the macro recorder will handle. I need one workbook to create and control a newly created workbook.
The text file would be a simple .txt file that has a 13 rows and 5 columns. Data is tab-delimited. Data would just start in A1
For the formula to place in the destination cell in the newly created workbook, it could be simple as =sum(a1:a13).
The reason I want to automate this is because I would have to open several files and automation is just plain cool.
I have attached a sample text file.
Here is the code for creating a new workbook and asking a user to choose a file

Dim UserFile as String
Dim DestBook As Workbook
Dim DestCell As Range
DestCell = ("A1")'used to as starting location to paste the data

UserFile= Application.GetOpenFilename(FileFilter:="Text files (*.txt),*.txt", Title:="Choose the Text File", MultiSelect:=False)

Set DestBook = Workbooks.Add
Sheets("Sheet1").Name = "Summary"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Name = "Data"

[JBeaucaire]

Heh, almost there. Now, forgetting code for a moment, show me what you would want done with this data. I'm trying simply to get what's in your mind onto a sheet where I can see it, too. Automation follows being able to see the finish line.

THe macro recorder can easily handle recording YOU doing something simple, then you're right, you have to tweak it (with our help) to make it dynamic and repeatable. That's the point of this exercise.

This code is yours, with mine derived from the macro recorder recording me actually importing that text file into a sheet. Then I edited it to use the variables you had already designed.

Option Explicit

Sub CreateWorkbook()
Dim UserFile As String, DestBook As Workbook

UserFile = Application.GetOpenFilename(FileFilter:="Text files (*.txt),*.txt", Title:="Choose the Text File", MultiSelect:=False)
If UserFile = "False" Then Exit Sub

Set DestBook = Workbooks.Add
Sheets("Sheet1").Name = "Summary"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Data"
    
    With Sheets("Data").QueryTables.Add(Connection:="TEXT;" & UserFile, _
        Destination:=Sheets("Data").Range("A1"))
        .Name = "sampleTextFile"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    With Sheets("Summary")
        .Range("A1") = "Data-ColA"
        .Range("A2").Formula = "=SUM(Data!$A:$A)"
    End With
End Sub

[spewer]

Jerry,
I will not underestimate the power of the macro recorder again! The confusing part is how the macro is referencing the newly creating workbook and hence, manipulating it.
Your macro work except one line

With Sheets("Summary")
        .Range("A1") = "Data-ColA"
        .Range("A2").FormulaR1C1 = "=SUM(Data!$A:$A)"
End With

It is like it does not know about that sheet. It gives me Application Defined or Object Defined Error. Again, it is confusing how the macro is referencing the newly created book.
Thanks again!

[spewer]

Well.. that didn't turn out like I wanted it for the formatting. I was trying to highlight

.Range("A2").FormulaR1C1 = "=SUM(Data!$A:$A)"

[spewer]

Ah... I fixed it. I change it to Formula vs. FormulaR1C1

    With Sheets("Summary")
        .Range("A1") = "Data-ColA"
        .Range("A2").Formula = "=SUM(Data!A:A)"
    End With

Thanks again Jerry!

[JBeaucaire]

Good edit. R1C1 means "Row1ColumnA1", which is numeric syntax for A1. With that, you can use that to create a numeric version of the same formula:

.Range("A2").FormulaR1C1 = "=SUM(Data!C1)"

...which means "column 1" in R1C1 context, not cell 'C1'.