I have now resolved that issue below. My new problem is that it keeps telling
me "Property Not Found" what does this mean.
This is a sample of my code.
Private Sub cmdImport_Click()
Dim MyDatabase As Database
Dim MyTableDef As TableDef
Dim datCurrentDate As Date
Dim db As Database
Dim MyFile, MyPath, MyName
Dim x As Integer
Dim strTable As String
Dim sQRY As String
Dim varRuleType As Variant
On Error GoTo Import_Err
If MsgBox("Warning!" & vbCrLf & "Clicking Yes will delete table information
and replace with new data. Are you sure you want to import?", _
vbYesNo + vbCritical, _
"Housing Services Group Reporting Tool - Confirm Import") <> vbNo Then
'Check for the existence of each file before attempting import process.
'If any not found, then prompt user with details of which file is missing.
For x = 14 To 25
strTable = DLookup("Location", "tblExtractLocations", "Table = " & x)
MyFile = Dir(strTable)
If MyFile = "" Then
MsgBox "File '" & strTable & "' not found. " & _
"Import process aborted. No data has been imported.", _
vbExclamation, "Housing Services Group Reporting Tool - Import
Failure"
Exit Sub
End If
Next x
DoCmd.SetWarnings False
Set db = CurrentDb
datCurrentDate = Format$(Now(), "dd/mm/yyyy hh:nn:ss")
strTable = DLookup("[Location]", "tblExtractLocations", "= 15")
DoCmd.TransferText acImportDelim, "", "tblTEST", strTable, False, ""
John Nurick said:Jez,
The "sample code" you quote looks as if you've just replaced
"TransferSpreadsheet" with "TransferText", and that's not going to work.
These are two quite different methods and they take different arguments.
For instance the constants for the first argument of TransferText are
acImportDelim, acImportFixed and acImportHTML; it's just coincidence
that acImport (for TransferSpreadsheet) has the same value as
acImportDelim.
More important, the second argument of TransferSpreadsheet is a number
indicating the version of Excel you're using (8 =
acSpreadsheetTypeExcel9), while the second argument of TransferText is a
string value with the name of an import specification that you must
create in the way I described.
John Nurick said:What line of code produces the error? What is the error number?
I have now resolved that issue below. My new problem is that it keeps telling
me "Property Not Found" what does this mean.
This is a sample of my code.
Private Sub cmdImport_Click()
Dim MyDatabase As Database
Dim MyTableDef As TableDef
Dim datCurrentDate As Date
Dim db As Database
Dim MyFile, MyPath, MyName
Dim x As Integer
Dim strTable As String
Dim sQRY As String
Dim varRuleType As Variant
On Error GoTo Import_Err
If MsgBox("Warning!" & vbCrLf & "Clicking Yes will delete table information
and replace with new data. Are you sure you want to import?", _
vbYesNo + vbCritical, _
"Housing Services Group Reporting Tool - Confirm Import") <> vbNo Then
'Check for the existence of each file before attempting import process.
'If any not found, then prompt user with details of which file is missing.
For x = 14 To 25
strTable = DLookup("Location", "tblExtractLocations", "Table = " & x)
MyFile = Dir(strTable)
If MyFile = "" Then
MsgBox "File '" & strTable & "' not found. " & _
"Import process aborted. No data has been imported.", _
vbExclamation, "Housing Services Group Reporting Tool - Import
Failure"
Exit Sub
End If
Next x
DoCmd.SetWarnings False
Set db = CurrentDb
datCurrentDate = Format$(Now(), "dd/mm/yyyy hh:nn:ss")
strTable = DLookup("[Location]", "tblExtractLocations", "= 15")
DoCmd.TransferText acImportDelim, "", "tblTEST", strTable, False, ""
John Nurick said:Jez,
The "sample code" you quote looks as if you've just replaced
"TransferSpreadsheet" with "TransferText", and that's not going to work.
These are two quite different methods and they take different arguments.
For instance the constants for the first argument of TransferText are
acImportDelim, acImportFixed and acImportHTML; it's just coincidence
that acImport (for TransferSpreadsheet) has the same value as
acImportDelim.
More important, the second argument of TransferSpreadsheet is a number
indicating the version of Excel you're using (8 =
acSpreadsheetTypeExcel9), while the second argument of TransferText is a
string value with the name of an import specification that you must
create in the way I described.
Hi John,
Thanks for that. I am not sure if thats what I am after. This is a sample
code I have 'DoCmd.TransferText acImport, 8, "tbl3rdPartyReport",
strTable, True, ""
I have used this when importing from excel, but this brings an error with
the 8 Its says "the text file specification '8' does not exist. you cannot
import using the speciification.
I dont understand that section.
:
Hi Jez,
To control how text files are imported, use either an import
specification or a schema.ini file. To create an import specification,
import the file once manually, and click the Advanced... button in the
text import wizard. Once you've made the necessary settings, save them
as a specification. To use the specification in VBA, just pass its name
in the TransferText statement that imports the file.
Hi,
I am trying to write in vba some code to import any txt files into my tables
created, and that when it does this the headers in my tables are kept. how is
this possible to do?
Thanks,
Jez.
strTable = DLookup("[Location]", "tblExtractLocations", "= 15")
DoCmd.TransferText acImportDelim, "", "tblTEST", strTable, False, ""
Its the last 2 lines that bring back an error. It runs everything. finds the
file locations ok and then comes back with a message "Property Not Found" all
I can do is click OK., this then shows me all my tables.
John Nurick said:What line of code produces the error? What is the error number?
I have now resolved that issue below. My new problem is that it keeps telling
me "Property Not Found" what does this mean.
This is a sample of my code.
Private Sub cmdImport_Click()
Dim MyDatabase As Database
Dim MyTableDef As TableDef
Dim datCurrentDate As Date
Dim db As Database
Dim MyFile, MyPath, MyName
Dim x As Integer
Dim strTable As String
Dim sQRY As String
Dim varRuleType As Variant
On Error GoTo Import_Err
If MsgBox("Warning!" & vbCrLf & "Clicking Yes will delete table information
and replace with new data. Are you sure you want to import?", _
vbYesNo + vbCritical, _
"Housing Services Group Reporting Tool - Confirm Import") <> vbNo Then
'Check for the existence of each file before attempting import process.
'If any not found, then prompt user with details of which file is missing.
For x = 14 To 25
strTable = DLookup("Location", "tblExtractLocations", "Table = " & x)
MyFile = Dir(strTable)
If MyFile = "" Then
MsgBox "File '" & strTable & "' not found. " & _
"Import process aborted. No data has been imported.", _
vbExclamation, "Housing Services Group Reporting Tool - Import
Failure"
Exit Sub
End If
Next x
DoCmd.SetWarnings False
Set db = CurrentDb
datCurrentDate = Format$(Now(), "dd/mm/yyyy hh:nn:ss")
strTable = DLookup("[Location]", "tblExtractLocations", "= 15")
DoCmd.TransferText acImportDelim, "", "tblTEST", strTable, False, ""
:
Jez,
The "sample code" you quote looks as if you've just replaced
"TransferSpreadsheet" with "TransferText", and that's not going to work.
These are two quite different methods and they take different arguments.
For instance the constants for the first argument of TransferText are
acImportDelim, acImportFixed and acImportHTML; it's just coincidence
that acImport (for TransferSpreadsheet) has the same value as
acImportDelim.
More important, the second argument of TransferSpreadsheet is a number
indicating the version of Excel you're using (8 =
acSpreadsheetTypeExcel9), while the second argument of TransferText is a
string value with the name of an import specification that you must
create in the way I described.
Hi John,
Thanks for that. I am not sure if thats what I am after. This is a sample
code I have 'DoCmd.TransferText acImport, 8, "tbl3rdPartyReport",
strTable, True, ""
I have used this when importing from excel, but this brings an error with
the 8 Its says "the text file specification '8' does not exist. you cannot
import using the speciification.
I dont understand that section.
:
Hi Jez,
To control how text files are imported, use either an import
specification or a schema.ini file. To create an import specification,
import the file once manually, and click the Advanced... button in the
text import wizard. Once you've made the necessary settings, save them
as a specification. To use the specification in VBA, just pass its name
in the TransferText statement that imports the file.
Hi,
I am trying to write in vba some code to import any txt files into my tables
created, and that when it does this the headers in my tables are kept. how is
this possible to do?
Thanks,
Jez.
John Nurick said:By "the last two lines" I presume you meanstrTable = DLookup("[Location]", "tblExtractLocations", "= 15")
DoCmd.TransferText acImportDelim, "", "tblTEST", strTable, False, ""
I see that you are passing empty strings to DoCmd.TransferText for the
import specification and HTMLTableName arguments. These are optional
arguments, and it is not permissible to assume that passing an empty
string is the same as not passing anything.
If that's not the problem, which of these lines produces the error? You
need to set a breakpoint earlier in the code and then step through it
line by line (using the buttons on the Debug Toolbar) until the error
happens. This gives you a message box with a Debug button. Click that.
You can then check the values of your variables - for instance, does
strTable contain the path and name of a comma-delimited text file?
Its the last 2 lines that bring back an error. It runs everything. finds the
file locations ok and then comes back with a message "Property Not Found" all
I can do is click OK., this then shows me all my tables.
John Nurick said:What line of code produces the error? What is the error number?
I have now resolved that issue below. My new problem is that it keeps telling
me "Property Not Found" what does this mean.
This is a sample of my code.
Private Sub cmdImport_Click()
Dim MyDatabase As Database
Dim MyTableDef As TableDef
Dim datCurrentDate As Date
Dim db As Database
Dim MyFile, MyPath, MyName
Dim x As Integer
Dim strTable As String
Dim sQRY As String
Dim varRuleType As Variant
On Error GoTo Import_Err
If MsgBox("Warning!" & vbCrLf & "Clicking Yes will delete table information
and replace with new data. Are you sure you want to import?", _
vbYesNo + vbCritical, _
"Housing Services Group Reporting Tool - Confirm Import") <> vbNo Then
'Check for the existence of each file before attempting import process.
'If any not found, then prompt user with details of which file is missing.
For x = 14 To 25
strTable = DLookup("Location", "tblExtractLocations", "Table = " & x)
MyFile = Dir(strTable)
If MyFile = "" Then
MsgBox "File '" & strTable & "' not found. " & _
"Import process aborted. No data has been imported.", _
vbExclamation, "Housing Services Group Reporting Tool - Import
Failure"
Exit Sub
End If
Next x
DoCmd.SetWarnings False
Set db = CurrentDb
datCurrentDate = Format$(Now(), "dd/mm/yyyy hh:nn:ss")
strTable = DLookup("[Location]", "tblExtractLocations", "= 15")
DoCmd.TransferText acImportDelim, "", "tblTEST", strTable, False, ""
:
Jez,
The "sample code" you quote looks as if you've just replaced
"TransferSpreadsheet" with "TransferText", and that's not going to work.
These are two quite different methods and they take different arguments.
For instance the constants for the first argument of TransferText are
acImportDelim, acImportFixed and acImportHTML; it's just coincidence
that acImport (for TransferSpreadsheet) has the same value as
acImportDelim.
More important, the second argument of TransferSpreadsheet is a number
indicating the version of Excel you're using (8 =
acSpreadsheetTypeExcel9), while the second argument of TransferText is a
string value with the name of an import specification that you must
create in the way I described.
Hi John,
Thanks for that. I am not sure if thats what I am after. This is a sample
code I have 'DoCmd.TransferText acImport, 8, "tbl3rdPartyReport",
strTable, True, ""
I have used this when importing from excel, but this brings an error with
the 8 Its says "the text file specification '8' does not exist. you cannot
import using the speciification.
I dont understand that section.
:
Hi Jez,
To control how text files are imported, use either an import
specification or a schema.ini file. To create an import specification,
import the file once manually, and click the Advanced... button in the
text import wizard. Once you've made the necessary settings, save them
as a specification. To use the specification in VBA, just pass its name
in the TransferText statement that imports the file.
Hi,
I am trying to write in vba some code to import any txt files into my tables
created, and that when it does this the headers in my tables are kept. how is
this possible to do?
Thanks,
Jez.