OpenText -- what am I doing wrong?

[McDimwitty]

I am getting a compile error in the second sub in the OpenText statement.

Any idea why?





Private Function GetSelectedFile() As String
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog
.Filters.Clear
.Filters.Add "Excel Files", "*.xls"
.Filters.Add "All Files", "*.*"

If .Show = True Then
GetSelectedFile = .SelectedItems(1)
Else
GetSelectedFile = ""
End If

End With
End Function


Private Sub CmdImpStuff_Click()
On Error GoTo StuffImport_Err


Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWBs As Excel.Workbooks
Dim xlWS As Excel.Worksheet
Dim strBookName As String
Dim temp As String


Set xlApp = CreateObject("Excel.Application")


Dim DelimFileName As String
DelimFileName = GetSelectedFile

If DelimFileName <> "" Then
DoCmd.SetWarnings False


'xlWBs.OpenText

xlWBs.OpenText(FileName:=DelimFileName, _
Origin:=437, StartRow:=2, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 1), Array(4, 2), Array(5, 4), Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 1), Array(10, 2), Array(11, 1), Array(12, 2), Array(13, 1), Array(14, 1), Array(15 _
, 2), Array(16, 2), Array(17, 4), Array(18, 4), Array(19, 4), Array(20, 4), Array(21, 2), _
Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 1), Array(26, 1), Array(27, 1)), _
TrailingMinusNumbers:=True)

xlWB = ActiveWorkbook


'xlWB.FullName
strBookName = (Left(xlWB.FullName, (Len(xlWB.FullName) - 4)) & "_temp.xls")


xlWB.SaveAs strBookName

DoCmd.OpenQuery "qdel_StuffData", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblStuff_RawData", FileName, True


xlWB.Close

' DoCmd.TransferText acImportDelim, , "tblStuff_RawData", FileName, False


Set xlApp = Nothing
Set xlWB = Nothing
Set xlWBs = Nothing
Set xlWS = Nothing










MsgBox "File uploaded Successfully.", vbOKOnly, "Upload Successful"
DoCmd.SetWarnings True
End If

StuffImport_Exit:
Exit Sub

StuffImport_Err:
MsgBox Error$
Resume StuffImport_Exit
End Sub

 

[boblarson]

1. You have xlWBs declared as WorkbookS and I don't think you can declare it as the workbooks collection.

2. Why are you opening the text file that way. What is the purpose? I am curious as I think there may be an easier way.

 

[boblarson]

1. You have xlWBs declared as WorkbookS and I don't think you can declare it as the workbooks collection.

Never mind, I figured out why you were doing that.
Still not clear on the reason for this way:

2. Why are you opening the text file that way. What is the purpose? I am curious as I think there may be an easier way.

 

[McDimwitty]

Never mind, I figured out why you were doing that.
Still not clear on the reason for this way:

Basically, the access vba app that I am writing for work has to import a semicolon delimited file daily which is created by another system.

1) It has an file extension of xls.

2) This other system places the headers which I don't want. The Access table already is defined. With this in mind, I thought I would start importing at row 2.

I was thinking about using TransferText and ACImportDelim, but I wasn't sure how it would handle these two things.

That's it in a nutshell.

Mr. McDimwitty

 

[boblarson]

How about this:

Public Sub ImportTextFile()
    [color=green]' to use the ADODB.Recordset, be sure you have a reference set to ADO[/color]
    Dim rst As ADODB.Recordset
    Dim strFile As String
    Dim strInput As String
    Dim varSplit As Variant
    Dim intCount As Integer

    Set rst = New ADODB.Recordset
    [color=green]' CHANGE THE TABLE NAME HERE[/color]
    rst.Open "TextImportTest", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    [color=green]' CHANGE THE TEXT FILE NAME AND LOCATION HERE[/color]
    strFile = "C:\Temp\TestText.xls"

    Open strFile For Input As #1

    Do Until EOF(1)
        [color=green]' This counter is just to get to the applicable line before importing[/color]
        intCount = intCount + 1
        [color=green]' reads the text file line by line[/color]
        Line Input #1, strInput
        [color=green]' starts importing on the second line.  Change the number to match which line you
        ' want to start importing from[/color]
        If intCount >= 2 Then
            [color=green]' creates a single dimension array using the split function[/color]
            varSplit = Split(strInput, ";", , vbTextCompare)
            [color=green]' adds the record[/color]
            With rst
                .AddNew
                .Fields(1) = varSplit(0)
                .Fields(2) = varSplit(1)
                .Fields(3) = varSplit(2)
                .Update
                .MoveNext
            End With
        End If
    Loop
    [color=green]' garbage collection[/color]
    Close #1
    rst.Close
    Set rst = Nothing

End Sub

 

[McDimwitty]

Bob,

I started VBA in Excel a few months ago and in Access last week. Although I didn't try it yet in my application, I think that's exactly what I was looking for. It's also very straightforward that I think I am grasping how Access, VBA and ADO works.

Thank you! I will try it in the morning.

=-= Mr. McDimwitty =-=
YBTH
"You Bet That Helps"

 

[curtinch]

Hi Bob, your post above has been very helpful. I was wonder if you could give some insight. I'm trying to import a text file with 273 fields into two tables. I've been able to do this with the help of your code in two steps and I'm now trying to accomplish everything with one step. Could you take a look at the changes I've made to your originat code. The file I'm importing is tab delimited text file. With this current code I'm only able to populate the first record in the table and then I get error message. (Run-time error '3265') (Item cannot be found in the collection corresponding to the reqested name or ordinal).

Public Sub ImportTextFile()
   ' to use the ADODB.Recordset, be sure you have a reference set to ADO
   Dim rst As ADODb.Recordset
   Dim rst2 As ADODb.Recordset
   Dim strFile As String
   Dim strInput As String
   Dim varSplit As Variant
   Dim intCount As Integer
   
   Set rst = New ADODb.Recordset
   Set rst2 = New ADODb.Recordset
   ' CHANGE THE TABLE NAME HERE
   rst.Open "Importtabledata", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   rst2.Open "importtabledata2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   ' CHANGE THE TEXT FILE NAME AND LOCATION HERE
   strFile = "G:\Home\RiskMgtReports\AutoDatabase\fullextract.txt"
   Open strFile For Input As #1
   Dim i As Integer
   Do Until EOF(1)
       ' This counter is just to get to the applicable line before importing
       intCount = intCount + 1
       ' reads the text file line by line
       Line Input #1, strInput
       ' starts importing on the second line.  Change the number to match which line you
       ' want to start importing from
       If intCount >= 2 Then
           ' creates a single dimension array using the split function
           varSplit = Split(strInput, vbTab, , vbTextCompare)
           ' adds the record
           With rst
               .AddNew
                For i = 0 To 255
                    .Fields(i) = varSplit(i)
                .Update
                Next i
           End With
           With rst2
                .AddNew
                .Fields(0) = varSplit(255)
                .Fields(1) = varSplit(256)
                .Fields(2) = varSplit(257)
                .Fields(3) = varSplit(258)
                .Fields(4) = varSplit(259)
                .Fields(5) = varSplit(260)
                .Fields(6) = varSplit(261)
                .Fields(7) = varSplit(262)
                .Fields(8) = varSplit(263)
                .Fields(9) = varSplit(264)
                .Fields(10) = varSplit(265)
                .Fields(11) = varSplit(266)
                .Fields(12) = varSplit(267)
                .Fields(13) = varSplit(268)
                .Fields(14) = varSplit(269)
                .Fields(15) = varSplit(270)
                .Fields(16) = varSplit(271)
                .Fields(17) = varSplit(272)
                .Fields(18) = varSplit(273)
                .Update
            End With
       End If
   Loop
   ' garbage collection
   Close #1
   rst.Close
   Set rst = Nothing
   rst2.Close
   Set rst2 = Nothing
End Sub

Thank you for any help.