Thingol
Technical User
- Jan 2, 2002
- 169
- 0
- 0
Hi All,
I have created the code below (code block 1) to convert sets of tab-separated text files to MS-excel (2000) workbooks. I want to import the excel-files into MS-Access (2000), and I would prefer to run the conversion routine from text to excel from within Access as well. This way I could do the conversion and import into Access with the push of just one button.
I am not quite familiar with the way to approach Excel from within Access. I tried adapting the code below (code block 1) to a piece of code I could use in Access. This doesn't work, since I'm using some functions that I think may not be available from within Access. (see code block 2) How could I adapt the code in code block 1 to successfully convert all the txt-files to xls?
I hope some one can help me out! Any help will be greatly appreciated.
Best regards,
Martijn Senden.
p.s. Some of the code is in Dutch, such as the comments and the variables. I hope this doesn't make too much of a difference. If it does, I'd be happy to translate the code.
code block 1 - Converting text-files to xls-files from within Excel
code block 2 - Adaption of conversion-code to get it to work from within Access (it doesn't work)
In the Beginning there was nothing, which exploded.
--Terry Pratchett, Lords and Ladies--
I have created the code below (code block 1) to convert sets of tab-separated text files to MS-excel (2000) workbooks. I want to import the excel-files into MS-Access (2000), and I would prefer to run the conversion routine from text to excel from within Access as well. This way I could do the conversion and import into Access with the push of just one button.
I am not quite familiar with the way to approach Excel from within Access. I tried adapting the code below (code block 1) to a piece of code I could use in Access. This doesn't work, since I'm using some functions that I think may not be available from within Access. (see code block 2) How could I adapt the code in code block 1 to successfully convert all the txt-files to xls?
I hope some one can help me out! Any help will be greatly appreciated.
Best regards,
Martijn Senden.
p.s. Some of the code is in Dutch, such as the comments and the variables. I hope this doesn't make too much of a difference. If it does, I'd be happy to translate the code.
code block 1 - Converting text-files to xls-files from within Excel
Code:
Option Explicit
Sub Tekstbestand_converteren()
Dim Titel As String
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Bestandsnaam As Variant
Dim Pad As String
Dim Station As String
Dim Bericht As String
' Bestandsfilters
Filter = "Text Files (*.txt),*.txt,"
' Standaardfilter instellen op *.txt
FilterIndex = 1
' Stel de titel van het dialoogvenster in
Titel = "Kies de bestand(en) die u wilt openen"
' Kies de drive en het pad waarin gewerkt moet worden
Pad = ThisWorkbook.Path
Station = Left(Pad, 1)
ChDrive (Station)
ChDir (Pad)
With Application
' Stel het Array van bestandsnamen in op de geselecteerde bestanden (toestaan meerdere bestanden)
Bestandsnaam = .GetOpenFilename(Filter, FilterIndex, Titel, , True)
' Reset het beginstation/-pad
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With
' Stoppen als er geannuleerd wordt
If Not IsArray(Bestandsnaam) Then
MsgBox "Er was geen bestand geselecteerd."
Exit Sub
End If
' Bestanden openen
For i = LBound(Bestandsnaam) To UBound(Bestandsnaam)
Workbooks.OpenText Filename:=Bestandsnaam(i), DataType:=xlDelimited, Tab:=True
Workbooks(Workbooks.Count).SaveAs Filename:=(Pad & "\xls\" & Left(Workbooks(Workbooks.Count).Name, Len(Workbooks(Workbooks.Count).Name) - 4)), FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks(Workbooks.Count).Close
Next i
MsgBox Bericht, vbInformation, i - 1 & " Bestanden geconverteerd" ' Dit kan worden verwijderd
End Sub
code block 2 - Adaption of conversion-code to get it to work from within Access (it doesn't work)
Code:
Sub conversie_tekst_excel()
'Excelobjecten declareren en instellen
Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("c:\workbook1")
'Variabelen voor conversieroutine declareren
Dim Titel As String
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Bestandsnaam As Variant
Dim Pad As String
Dim Station As String
Dim Bericht As String
'Excel starten en conversie van tekstbestanden uitvoeren
With objXLApp
' Bestandsfilters
Filter = "Text Files (*.txt),*.txt,"
' Standaardfilter instellen op *.txt
FilterIndex = 1
' Stel de titel van het dialoogvenster in
Titel = "Kies de bestand(en) die u wilt openen"
' Kies de drive en het pad waarin gewerkt moet worden
Pad = CurrentProject.Path
MsgBox (Pad)
Exit Sub
Station = Left(Pad, 1)
ChDrive (Station)
ChDir (Pad)
' Stel het Array van bestandsnamen in op de geselecteerde bestanden (toestaan meerdere bestanden)
Bestandsnaam = .GetOpenFilename(Filter, FilterIndex, Titel, , True)
' Reset het beginstation/-pad
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
' Stoppen als er geannuleerd wordt
If Not IsArray(Bestandsnaam) Then
MsgBox "Er was geen bestand geselecteerd."
Exit Sub
End If
' Bestanden openen
For i = LBound(Bestandsnaam) To UBound(Bestandsnaam)
Workbooks.OpenText Filename:=Bestandsnaam(i), DataType:=xlDelimited, Tab:=True
Workbooks(Workbooks.Count).SaveAs Filename:=(Pad & "\xls\" & Left(Workbooks(Workbooks.Count).Name, Len(Workbooks(Workbooks.Count).Name) - 4)), FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks(Workbooks.Count).Close
Next i
'MsgBox Bericht, vbInformation, i - 1 & " Bestanden geconverteerd" ' Dit kan worden verwijderd
End With
End Sub
In the Beginning there was nothing, which exploded.
--Terry Pratchett, Lords and Ladies--