Import Text into Excel with VBA HELP??? 1

[NCSUVBAnewb]

Hey all, I'm new to this forum and VB in general (only been using for 2 days now) and I need some help executing a text import into Excel.

I've got a series of text files with columns of data that import just fine into Excel using the Import Data Wizard, but I'd like to automate this process with a script that opens a new Excel workbook and imports and formats the data. Ideally I'd like to have it so I just have to change the input filename and execute the script, and eventually maybe automate that too.

Currently the script will run and a window flashes open and closed instantly, and I cannot for the life of me figure out why!? Notable errors to this point were every time I had a ":=", namely in the

Selection.Insert Shift.Shift=xlShiftToRight

line, I would get a "Expected Statement" error at the comma. So, I removed them and since then the script executes but flashes the window open and closed. There are no saved files produced by the NewBook subroutine.

The majority of the code was taken from a macro recorded directly in Excel and cleaned up a bit since then.

Any help is appreciated!


Code:
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

Sub AddNew()
Set NewBook = Workbooks.Add
With NewBook
.Title = "TEST"
.Subject = "TEST"
.SaveAs Filename="TEST.xlsm"
End With
End Sub

Sub ImportTXTtoXLSM()

' ImportTXTtoXLSM Macro
' Imports .txt input calibration files using Import Wizard into properly formatted .xlsm files.

With objXL.NewBook.QueryTables.Add("D:\3DMGT\08ALLTXTS\0801\16-41_080104.TXT",Range("$A$3"))
.Name = "16-41_080104"
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(9, 8, 8, 8, 8, 8, 7)
.TextFileTrailingMinusNumbers = True
End With

With objXL.NewBook
Range("B2").Select
ActiveCell.FormulaR1C1 = "1"
Range("C2").Select
ActiveCell.FormulaR1C1 = "2"
Range("D2").Select
ActiveCell.FormulaR1C1 = "3"
Range("E2").Select
ActiveCell.FormulaR1C1 = "4"
Range("F2").Select
ActiveCell.FormulaR1C1 = "5"
Range("G2").Select
ActiveCell.FormulaR1C1 = "6"
Range("H2").Select
ActiveCell.FormulaR1C1 = "7"
Range("B1:H1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge

Columns("B:B").Select
Selection.Insert Shift=xlShiftToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "Timestamp"
Range("B3").Select
ActiveCell.FormulaR1C1 = "0:00:00"
Range("B4").Select
ActiveCell.FormulaR1C1 = "0:00:01"
Range("B3:B4").Select
Selection.AutoFill Destination=Range("B3:B84400")
End With

Columns("A:A").ColumnWidth = 9
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 7
Columns("C:I").Select
Range("C2").Activate
Selection.ColumnWidth = 10
Cells.Select
Range("C2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("B2:I2").Select
Selection.Font.Bold = True

End Sub

 

[SkipVought]

hi,

FIRST, you cannot have this code before a procedure!

[b]
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
[/b]
Sub AddNew()

Second, declare ALL variables. Use Option Explicit at the top of each module. Turn on automatically in Tools > Options > Editor - require variable declaration.

Also you do not need to declare or set the Excel application object (assuming that this is coded in Excel)

Third, avoid using the Select and Activate methods. Rather...

    With NewBook.Sheets(1)
        .Range("B2").Value = "1"
        .Range("C2").Value = "2"
'...
        With Range("B1:H1")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = TRUE
        
        End With
        [s]Selection.Merge[/s]
'...
        .Columns("B:B").Insert Shift=xlShiftToRight
'...
    End With

I also avoid using MERGE as there are pitfalls. Try Center across selection.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[NCSUVBAnewb]

I really am new to this so could you clarify a little more what you mean by not placing a code above procedure?

Secondly, when I use the Option Explicit command, it requires that I declare all variables. I am a little confused about the objXL object being a variable, must be my unfamiliarity with the VB system.

Thank you so much for all the help, I'm doing a little additional research now and trying to adjust the changes you recommended.

 

[SkipVought]

'top of module
Option Explicit   'requires that all variables be declared

'post MODULE-LEVEL DECLARATION here
Dim SomeVar as SomeType

'now post CODED PROCEDURES
Sub MyFirstProc()
'post procedure-level declarations here
   Dim OtherVar as SomeOtherType

'now your code

End Sub

Sub MySecondProc()
'post procedure-level declarations here
   Dim OtherVar as SomeOtherType

'now your code

End Sub

objXL is a variable. It would be declared like...

Dim objXL as Object

or

Dim objXL as Excel.Application

When any variable is asigned, or Set for an object, it must be done within a procedure.
Please refer OFTEN to VB Help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[NCSUVBAnewb]

Skip,

Thanks for clarifying, I read up about procedure-module-project level variables and it makes more sense now.

Now, I rearraged the code such that Sub AddNew is now above the object declaration for objXL. I have not changed any of the merge and/or selection commands just yet, but thats next on the list.

When I execute the code, it still flashes open and closes instantly. What could be causing this? I don't see an "exit" or "close" command anywhere thus what would be instructing the script to close? Is an an input/output error with importing the text file?

 

[SkipVought]

HOW & WHERE did you declare NewBook?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[NCSUVBAnewb]

The following is the rearranged first few lines of my code:

Option Explicit

Dim objXL

Sub AddNew()
Set NewBook = Workbooks.Add
With NewBook
.Title = "TEST"
.Subject = "TEST"
.SaveAs Filename="TEST.xlsm"
End With
End Sub

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

Sub ImportTXTtoXLSM()

' ImportTXTtoXLSM Macro
' Imports .txt input calibration files using Import Wizard into properly formatted .xlsm files.

....

 

[SkipVought]

PROBLEMS!

The statements...

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

must be IN A PROCEDURE!

ONLY the module level declarations can be outside a procedure AND before ALL procedures.

Also, if you were to Debug > Compile, you would notice that NewBook has not be declared. Using NewBook as you have posted, will require NewBook to be declared at the module level.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

It looks to me NCSUVBAnewb codes in VBA in Excel already, so there is no need to declare Excel object in Excel (is that right, Skip?)

Currently the script will run and a window flashes open and closed instantly

You may want to put some breakes in your code (click on a gray area just left of the code in VBA Editor) and step thru it (hit F8 to step line-by-line)

Have fun.

---- Andy

 

[SkipVought]

as I previously stated...

Also you do not need to declare or set the Excel application object (assuming that this is coded in Excel)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[NCSUVBAnewb]

So this is coded in Excel, but my ultimate goal is to run this external to Excel and have the script open and process all the data for me.

Because theres a large number of files, I want to have a script that I run separately that grinds through each data import for me. I could easily just do everything I need through Excel, but my goal is automation.

Skip - "declaring" NewBook means what? As a variable? I'm thinking it'd be best declared within the subroutine because it doesn't appear elsewhere?

Sorry if my lack of knowledge is frustrating...

 

[SkipVought]

to run this external to Excel

To run in WHAT application?

I'm thinking it'd be best declared within the subroutine because it doesn't appear elsewhere

So you are going to open this workbook anb never reference it in any other procedure, EVER, never saving it and leaving it open for the user to do something with?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[NCSUVBAnewb]

To run the script external to Excel, have it import and format the data, and close.

I don't plan to reference this workbook anywhere else, I just want to open, import, format, save, close, repeat.

 

[SkipVought]

I am asking, in WHAT application will you run this code? This IS Visual Basic for Applications after all.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[NCSUVBAnewb]

I originally wanted to run the code just as a .vbs file written in notepad, but because of the excessive difficulties I have decided to rewrite it. Now I have a macro that runs in Excel - user opens and imports data using wizard, then runs macro which formats and saves file into new directory.

 

[SkipVought]

There is another forum for VBS: forum329.

There are DIFFERENT issues to consider.

This is forum707. We ASSUME that ALL the code posted and referenced in this forum, will be run in an APPLICATION, like Excel, Word, Outlook, Access etc.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[NCSUVBAnewb]

Skip,

Sorry for the confusion, I now understand the differences between the two.

I've revised my entire code, now working much better. Thank you so much for guiding me through these first through difficult days of misunderstanding