Add Filename To Record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way in Access to add the filename (actually the first 8 letters of the filename) to each record when importing a file. I would have to somehow create the field and then for each record put the filename. I need to set this up in VBA so that it is automatic. I am transfering out of Excel into Access, and I know how to do this in excel just fine, but have no idea where to start in Access. Below is the VB code that I use in Excel, which works fine, but I don't know how to convert this process to Access

Any help would be greatly appreciated

Stev

Sub SetupReport(
Dim cLastRow As Lon
Dim i As Lon
Dim Sh As Workshee
Set Sh = ActiveWorkbook.ActiveShee
cLastRow = Cells(Rows.Count, "D").End(xlUp).Ro
'Looks in column D to get the filenam
For i = 2 To cLastRo
UpdateTextFile Left(Sh.Cells(i, "D").Value, Len(Sh.Cells(i, "D").Value) - 4
Next
End Su

Sub UpdateTextFile(Name As String
Dim wb As Workboo
'Opens the txtfil
Workbooks.OpenText
FileName:=ThisWorkbook.Path & "\RawTextFiles\" & Name,
DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False,
Tab:=True,
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), Array(14, 1), Array(15, 1),
Array(16, 1)
'finds the last row of data in the text fil
Dim LastRowForSerielNumber1 As Lon
LastRowForData = Cells(Rows.Count, "A").End(xlUp).Ro
'Adds the file name and then copies it down the pag
Range("Q1").Selec
ActiveCell.Formula = "=Left(Name,8)
Selection.Cop
Range("Q2:Q" & LastRowForData).Selec
Selection.Past
'Saves and closes the workboo
ActiveWorkbook.Close SaveChanges:=Tru
End Sub
 
Hi Steve,

There's no neat built-in way to do it. If you're importing from Excel,
you might just as well add the name to a column as now, and then import
the table complete with the added column.

Alternatively, import the data to a temporary table and then use a
calculated field in a query to add the name. Untested and incomplete
aircode:

Dim strFileName as String
Dim strSQL As String
Dim dbD as DAO.Database

Set dbD = CurrentDB()
strFileName = "D:\Folder\File.xls"
DoCmd.TransferSpreadsheet acImport, strFileName, "MyTempTable"
'build SQL append query
strSQL = "INSERT INTO MyRealTable _
(FileName, Field1, Field2, Field3) SELECT " _
& Left(StrFileName, 8) & " AS FileName, Field1, " _
& "Field2, Field3 FROM MyTempTable;"
dbD.Execute strSQL, dbFailOnError

Set dbD = Nothing



Is there a way in Access to add the filename (actually the first
Click to expand...
8 letters of the filename) to each record when importing a file. I
would have to somehow create the field and then for each record put the
filename. I need to set this up in VBA so that it is automatic. I am
transfering out of Excel into Access, and I know how to do this in excel
just fine, but have no idea where to start in Access. Below is the VB
code that I use in Excel, which works fine, but I don't know how to
convert this process to Access.
 
Back
Top