Open text and save as csv format

[mrxdotcom]

Any help on this would be very much appreciated: I would like to open a text in access via EXCEL VBA, and save the file as Comma delimited format. I have the following code below: it produces the following error message: Expect Function or variable:






Dim objXL As New Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim strSQL1 As String
Dim strSQL2 As String

'Dim db As Database, rs As Recordset, r As Long
'Set db = OpenDatabase("H:\User Man\Workgroup\Databases\EmployeesLocations.mdb")
On Error GoTo E_Handler
'Set objXLBook = objXL.Workbooks.Open("c:\drives.txt")

Set objXLBook = objXL.Workbooks.OpenText("H:\User Man\Workgroup\AnitaDiskSpaceMacro\Drives.txt", Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), TrailingMinusNumbers:=True)

Set objXLSheet = objXLBook.Worksheets(1)

Cells.Select
Cells.EntireColumn.AutoFit

Range("A1").Select
objXLBook.SaveAs _
"H:\User Man\Workgroup\CommaDrivers.csv ", FileFormat:=xlCSV _
, CreateBackup:=False


' Call ImportTextFile

Set objXLSheet = Nothing
Set objXLBook = Nothing
objXL.Quit
Set objXL = Nothing
Exit Function

E_Handler:
Select Case Err
Case 32755 ' Dialog Cancelled
MsgBox "You cancelled the dialog box"
Case Else
MsgBox "Unexpected error. Err " & Err & " : " & Error
End Select

Set objXLSheet = Nothing
Set objXLBook = Nothing
objXL.Quit
Set objXL = Nothing

End Function
















Public Function ImportTextFile()

Dim strTextFile As String
Dim strSpecs As String
Dim strRawTable As String
Dim dbs As Database
Dim rst As Recordset

Dim strErrorsTable As String

strSpecs = "CSV"
strRawTable = "Drives"
strQuery = "qmakContacts"
strTextFile = "CommaDrivers.csv"
strTextFile = SysCmd(acSysCmdAccessDir) & "\" & strTextFile
strErrorsTable = "CommaDrives_ImportErrors"

On Error Resume Next

'Delete old tables (if any)
DoCmd.DeleteObject acTable, strRawTable
DoCmd.DeleteObject acTable, strErrorsTable

On Error GoTo ImportTextFileError

'Import text file into a table
DoCmd.TransferText transfertype:=acImportDelim, specificationname:=strSpecs, _
tablename:=strRawTable, Filename:=strTextFile, hasfieldnames:=False

ImportTextFileExit:
Exit Function

ImportTextFileError:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ImportTextFileExit

End Function

 

[ecniv]

I may be a little thick here...

You want to open a text file in Excel, save it as CSV then import this to Access??


Why not just import into Access, using code, parsing the text, and putting it into an import table, then fiddle with the records afterwards?


Vince

 

[mrxdotcom]

Vince

Im very new to VBA stuff and im not sure how to programmatically import the data into a table. e.g selecting a line from the text file and import it into Access. i would be grateful if you should me how to do it your way or any code would help.

thanks again.