Importing text files, best way? VBA, Access, SQL??

I have had a play with the VBA to put in question asking user sheetname but this doesn't work. My changes in bold...

Sub AppendTest()


'Resereves space for variables
Dim lLastRow As Long, lLastColumn As Long
Dim wksNew As Worksheet, wksCurrent As Worksheet
Dim SheetName As String

'Commissions and error handlers should anything fail
On Error GoTo ErrHandler

'With construct saves typing Object (in this case Application) on each line
'This statement stops the screen updating (flashing) and any warnings popping up
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'If you have an object variable (see above) you need to set it. Now
'I can just refer to these variables and they will always point to
'that object, for example no matter what worksheet is active
Set wksCurrent = ActiveSheet

'Ask user what destination sheet is called
SheetName = InputBox("Type in destination sheet name")

'finds the last row and column in the data and then copies that range
'and pastes it in one operation and deletes the original sheet
With SheetName
    lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .Range(.Cells(1, 1), .Cells(lLastRow, lLastColumn)).Copy Destination:=wksNew.Range("A1")
    .Delete
End With

'Switches screen updating and alerts back on
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

'Exits the routine if successful otherwise it would run the error handler as
'the next line of code
Exit Sub

'Error handler should anything go wrong which pops a message box
'and resets stuff
ErrHandler:
MsgBox "Somthing went wrong", vbInformation + vbOKOnly, "Error"
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

Catherine

That's one way to go with it and a pretty good stab. What you haven't done is told Excel that that sheet name is a worksheet object so the code below will do that. I've also moved the delete of the worksheet with the data import on it out of the 'with' construct.

I'm thinking that a better way to do this however would be to copy the code into each users model with a defined worksheet to place the data in and then do the import, copy over and delete original sheet in one fell swoop. this way there is nothing the user can do wrong, barring cancel a few dialogs and that can be handled. With you code there is always a danger they will enter a sheet name which doesn't exist, etc. Anyhow it should work.

Let us know if you need more help

Sub AppendTest()

'Resereves space for variables
Dim lLastRow As Long, lLastColumn As Long
Dim wksNew As Worksheet, wksCurrent As Worksheet
Dim SheetName As String

'Commissions and error handlers should anything fail
On Error GoTo ErrHandler

'With construct saves typing Object (in this case Application) on each line
'This statement stops the screen updating (flashing) and any warnings popping up
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'If you have an object variable (see above) you need to set it. Now
'I can just refer to these variables and they will always point to
'that object, for example no matter what worksheet is active
Set wksCurrent = ActiveSheet

'Ask user what destination sheet is called
SheetName = InputBox("Type in destination sheet name")

'Set a worksheet object reference to sheet name above
Set wksNew = Worksheets(SheetName)

'finds the last row and column in the data on the users selected sheet
'and then copies that range
'and pastes it in one operation and deletes the original sheet
With wksNew
    lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .Range(.Cells(1, 1), .Cells(lLastRow, lLastColumn)).Copy Destination:=wksNew.Range("A1")
End With

'Delete original sheet used in import
wksCurrent.Delete

'Switches screen updating and alerts back on
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

'Exits the routine if successful otherwise it would run the error handler as
'the next line of code
Exit Sub

'Error handler should anything go wrong which pops a message box
'and resets stuff
ErrHandler:
MsgBox "Somthing went wrong", vbInformation + vbOKOnly, "Error"
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

Hi Nick

I am glad I was on the right track,, been looking at a few books to try and understand how to go about it, as well as the great help from you.

Not quite there yet the program runs, I enter Sheet1 to the input box, and the current sheet deletes but the data doesn't get added to the destination sheet! Do I need to input the sheet name in inverted commas? eg "Sheet1" ?

or

I wondered about this line

With wksNew
    lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .Range(.Cells(1, 1), .Cells(lLastRow, lLastColumn)).Copy Destination:=wksNew.Range("A1")

Should the Copy Destination also need to find the Last row of data and increment by 1?

Thanks

Catherine

You seem to be just copying the data over itself. Shouldn't it be

'finds the last row and column in the data on the users selected sheet
'and then copies that range
'and pastes it in one operation and deletes the original sheet
With wksCurrent
    lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .Range(.Cells(1, 1), .Cells(lLastRow, lLastColumn)).Copy Destination:=wksNew.Range("A1")
End With

'Delete original sheet used in import
wksCurrent.Delete

Catherine/Bob

This has categorically been the biggest 'dog's dinner' of a reply I've ever given!

I am going to review it later, test it with the file Catherine sent and send back a tested bit of code that actually works! Bear with me for 9 hours or so.

(I must go to bed earlier and not answer questions at 6:45am, again... I must.....)

Hi bob and nick,

Appreciate your help.. I think I didn't explain it clearly about the second part at the beginning.. and I am a beginner .. biting off more than i can chew as they say.. but this project is coming together..  The first part is working a treat... I have 2 colleagues testing it.. and it will save so much repetitious deleting of the 20 or more unnecessary columns each time they add data.. This situation was crying about for a solution...

Rest assured I am not taking all the glory :-) and make mention of some amazing assistance from experts on the other side of the world! 

Regards

Catherine

I don't think anybody, Nick or I, care whether you take credit or not, we just want to see it done, as you say it is a problem crying out for automation.

My last comment was that the copy and paste action was being done on the same range, so the net effect was nothing, hence my suggestion of copying from current.

Nick is going to give it a more structured crack later on, so I will desist myself so as not to confuse the issue. Let's hope Nick can stay awake long enough to get through it.

OK Catherine, as promised here is the tested code using the file you supplied me. I make a couple of caveats

1. The imported code seems to bring in a merged blank row at the bottom of the data. In your file this was at row 40. if you look at it there is no data but it is merged, hence I am finding the last row in that data (lLastRowImport) and subtracting 1. if that is undesireable then change that.
2. I am copying from row 2 (.Range(.Cells(2, 1), .Cells(lLastRowImport, 7)).Copy), presuming the first always has titles which you do not want adding to the appending sheet each time.

The code is below amalgamated into one with an array adding afresh titles each time (I was too lazy to check if they exist on the sheet so thought I would add them). it also bolds them and autofits the columns to the data they contain. It now uses a 'spare' sheet that is added to the workbook and dleted at the end so the user never sees the import sheet and cannot therefore select it as the destination also.

Geez, I so hope this works now as I am nowhere near so rushed, but have missed Brazil -v- N.Korea [:'(]

Sub ImportAndAppendData()
Dim wksImport As Worksheet, wksAppend As Worksheet
Dim lLastRowImport As Long, lLastRowAppend As Long
Dim strFileName As String, strAppendSheetName As String

On Error GoTo ErrHandler

With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    strFileName = .GetOpenFilename("Text Files (*.txt), *.txt")
End With

If strFileName = "False" Then Exit Sub

Set wksImport = Worksheets.Add

    With wksImport.QueryTables.Add(Connection:= _
        "TEXT;" & strFileName _
        , Destination:=Range("A1"))
        .TextFileTabDelimiter = True
        .TextFileColumnDataTypes = Array(1, 9, 9, 9, 9, 9, 1, 9, 1, 1, 2, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
        , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
        , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
        .Refresh BackgroundQuery:=False
    End With
   
strAppendSheetName = InputBox("Enter the destination sheet name", "Destination Sheet")
Set wksAppend = Worksheets(strAppendSheetName)
lLastRowAppend = wksAppend.Cells(wksAppend.Rows.Count, "A").End(xlUp).Row + 1

With wksImport
    lLastRowImport = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
    .Range(.Cells(2, 1), .Cells(lLastRowImport, 7)).Copy _
        Destination:=wksAppend.Cells(lLastRowAppend, "A")
    .Delete
End With

With wksAppend
    .Columns.AutoFit
    .Range("A1:G1").Value = Array("Title", "Author", "Publisher", "Pub_Year", "ISBN", "Binding", "Added_To_List")
    .Range("A1:G1").Font.Bold = True
End With

With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

Exit Sub
   
ErrHandler:
MsgBox "Program has errored", vbInformation + vbOKOnly, "Error"
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

End Sub

You missed nothing, it was a boring game.

Nick, You are going to have to explain the points system on this site, I really don't get it. Earlier you post had 5 points, but when I replied it bumped up to 21. And the OP's latest post got 37! These numbers are so wierd.

Yay thanks Nick... It works a treat! and I think I can use it for other similiar jobs...

Obviously I have found this forum valuable and hope to be able to contribute as I learn more

Cheers

Catherine

Hi again This has been working well. However I have been asked to slightly change the columns that come into the spreadsheet. I thought this would be straight forward as I can just adjust this part -- here I have made changes to a couple

This is the one i have changed -- all works EXCEPT the last column marked "1". (i did try other data types eg 6 8) In this column is data like

.TextFileColumnDataTypes = Array(1, 9, 9, 9, 9, 9, 1, 1, 1, 1, 9, 1, 1, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
        , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
        , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)

This is earlier/original  one

.TextFileColumnDataTypes = Array(1, 9, 9, 9, 9, 9, 1, 1, 9, 9, 1, 1, 1, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
        , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
        , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)

I have been researching data types and wracking my brains... but I can't get it to bring any data for this column.

Any ideas?

Many thanks

Catherine

Yay, found the answer

here

With wksImport
    lLastRowImport = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
    .Range(.Cells(2, 1), .Cells(lLastRowImport, 8)).Copy _

The problem was that the "8" needs to be changed if I add columns!

This is proving a very popular Macro so I need to change it for individual preferences.

Cheers

Catherine

Catherine

cferart:

This is proving a very popular Macro so I need to change it for individual preferences

You may want to think about how you do this within one macro though as you would not want to maintain ten different ones for different users though. If that is what you are looking for, then you might get into userforms and set up a wizard type approach for the user to fill in with their particular preferences or if these are always the same, set up a hidden sheet with user IDs and their preferences so the user just enters their ID and you go off in the code, and look at their preferences in your hidden sheet, load these in to variables and read those variables in the code.

Just a pointer

Nick Hodge:

Catherine

cferart:

This is proving a very popular Macro so I need to change it for individual preferences

You may want to think about how you do this within one macro though as you would not want to maintain ten different ones for different users though. If that is what you are looking for, then you might get into userforms and set up a wizard type approach for the user to fill in with their particular preferences or if these are always the same, set up a hidden sheet with user IDs and their preferences so the user just enters their ID and you go off in the code, and look at their preferences in your hidden sheet, load these in to variables and read those variables in the code.

Just a pointer

Thanks Nick

good idea...It is probably just one or two columns that some may want/not want. 

I will wait until I get all the feedback..  

Catherne