[AccessD] Visual Basic error - Finished - thanks Terri

Kathryn,

May I suggest a small refinement to your code?  In XL one may usually
eliminate the '.Select' Method for readability and speed.  So,...

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "bib"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "timetext"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "day"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "checkpoint"

Would become:

    Range("B1").Value = "bib"
    Range("C1").Value = "timetext"
    Range("D1").Value = "day"
    Range("E1").Value = "checkpoint"

HTH,
Ron Moore
Sr. Database Administrator
Melville, NY
www.comtechpst.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kathryn
Bassett
Sent: Friday, September 26, 2003 5:55 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Visual Basic error - Finished - thanks Terri

Thanks to those of you who gave further hints on the problems with the
macro. Terri Jarus re-did it on a former version and it now works just
fine. If you've been keeping track, here is the final code:

Sub getdata()
'
' getdata Macro
' Macro recorded 9/22/2003 by Terri Jarus
'
' Keyboard Shortcut: Ctrl+d
'
 Dim txtfile, savefile As String
 Application.Goto Reference:="R1C1"
 txtfile = InputBox("Enter last four digits of filename", "File Name",
"D:\MyFiles\Access\AC100\2003\output*.txt")
 savefile = Mid(txtfile, 30, 10) & ".xls"
     Workbooks.OpenText Filename:=txtfile, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 9), _
        Array(2, 9), Array(3, 2), Array(4, 1), Array(5, 2), Array(6, 1),
Array(7, 2))
    Selection.EntireRow.Insert
    ActiveCell.FormulaR1C1 = "activity"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "bib"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "timetext"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "day"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "checkpoint"
    Range("A1").Select
    ActiveWorkbook.SaveAs Filename:= _
        savefile, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    
End Sub

Thanks Terri and the list!

--
Kathryn Rhinehart Bassett (Pasadena CA)
"Genealogy is my bag" "GH is my soap"
kathryn at bassett.net
http://bassett.net  


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com