importing an excel table into already existing access table with vba

[OfficeDummy]

Hello all!

I searched the Internet and this forum for a similar problem, but I found no help... Also, I'm a complete newbie to the fascinating world of programming, VBA and Access, so my question can very well be very stupid.

The tasks are:

1)Import an Excel table into Access
2)Add a new column and fill it with variables of date/time type.

Steps 1 and 2 need to be done only once, and I've almost managed to accomplish them. Now comes the hack:

3)This imported table needs to be updated daily... In other words, it will grow with every day.

I created a FileDialog object where I can choose the Excel file to import. First, the programme checks whether the requested Access table already exists and if it doesn't, the code does a simple TransferSpreadsheet. If the table exists, I tried to make an append query to "glue" the Excel table records - that's where I failed completely and miserably.

Here's a code snippet from what I have so far:

 Dim ADODBConnection As New ADODB.Connection
    Dim ADORS As New ADODB.Recordset
    Dim fd As FileDialog
    Dim arNames As Variant
    Dim strMonat As String
    Dim NewColumn As Object
    Dim curDatabase As Object
    Dim tblImported As Object
    Dim tblImported1 As Object
    Dim strSQL As String
    Dim sFileName As String
    Dim objTable As TableDef
 
 
 
    'Calls the dialog window
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
 
    With fd
        .AllowMultiSelect = True
        .Filters.Clear
        .Filters.Add "Excel", "*.xls"
        If .Show = -1 Then
            'runs throught selected files
            For Each vrtSelectedItem In .SelectedItems
 
                'checks whether the table already exists
                Set objTable = CurrentDb.TableDefs("Blublu")
 
                'if it doesn't, import it
                If objTable Is Nothing Then
 
                   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Blublu", vrtSelectedItem, False, "Tabelle1!A1:D45"
 
 
                     'inserts a new column
 
                   Set curDatabase = CurrentDb        
                   Set tblImported = curDatabase.TableDefs("Blublu")
                   Set NewColumn = tblImported.CreateField("Monat", DB_TEXT)
                   tblImported.Fields.Append NewColumn
 
 
                     'and fills it depending on a selected Excel table
 
                   sFileName = Mid(Dir(vrtSelectedItem), 9, 6)
 
                   If sFileName Like "I*" Then
 
                       strSQL = "UPDATE Blublu SET [Monat]= #31.12.2008# ;"
                       'strSQL doesn't work: the compiler discovered a syntax error, which is a complete mystery to me
                       DoCmd.RunSQL strSQL
 
                   ElseIf sFileName Like "E*" Then
                        DoCmd.RunSQL "UPDATE Blublu SET Blublu.Monat = #31.2008# ;"
 
                   Else
                        MsgBox "The file" & sFileName & " doesn't exist."
 
                   End If
 
             'if the table already exists, append the records from Excel to it:
 
           Else
 
                   DoCmd.RunSQL "INSERT INTO Blublu" & _
                                 "SELECT *." & vrtSelectedItem & "FROM" & vrtSelectedItem & ";"
 
             End If
 

Any help would be greatly appreciated!

Thanks a lot in advance,
OfficeDummy

PS: I'm using Windows XP and Access 2003 if it's relevant.

[Rabbit]

Try #12/31/2008# instead.

DoCmd.RunSQL "INSERT INTO Blublu" & _
"SELECT *." & vrtSelectedItem & "FROM" & vrtSelectedItem & ";"
 

Assuming that vrtSelectedItem is the path of a file, let's say C:\Workbook1.xls then your code evaluates to:

INSERT INTO BlubluSELECT *.C:\Workbook1.xlsFROMC:\Workbook1.xls;
 

Hopefully you can see why this will not work.

[OfficeDummy]

Try #12/31/2008# instead.

DoCmd.RunSQL "INSERT INTO Blublu" & _
"SELECT *." & vrtSelectedItem & "FROM" & vrtSelectedItem & ";"
 

Assuming that vrtSelectedItem is the path of a file, let's say C:\Workbook1.xls then your code evaluates to:

INSERT INTO BlubluSELECT *.C:\Workbook1.xlsFROMC:\Workbook1.xls;
 

Hopefully you can see why this will not work.

Thank you, Rabbit!

I still get the syntax error on the date... I had tried

Format("31.12.2008", vbShortDate)

but it didn't work either.

As for the append query, I know that vrtSelectedItem is wrong. But I don't know how can I refer to the Excel table withing that query....

[Rabbit]

Thank you, Rabbit!

I still get the syntax error on the date... I had tried

Format("31.12.2008", vbShortDate)

but it didn't work either.

As for the append query, I know that vrtSelectedItem is wrong. But I don't know how can I refer to the Excel table withing that query....

The problem may lie in the fact that you defined the field Monat as text when you created it.

You can't refer to the excel table. But you don't have to because you imported the excel table into Access under the name Blublu. Why you want to append a set of duplicate records is beyond me.

What you want to do is the equivalent of:

INSERT INTO Blublu
SELECT *
FROM Blublu;
 

[OfficeDummy]

The problem may lie in the fact that you defined the field Monat as text when you created it.

You can't refer to the excel table. But you don't have to because you imported the excel table into Access under the name Blublu. Why you want to append a set of duplicate records is beyond me.

What you want to do is the equivalent of:

INSERT INTO Blublu
SELECT *
FROM Blublu;
 

Dear Rabbit, the thing is that I have to import similar-looking Excel tables to Access on daily basis. So I'm not appending a set of duplicate records, I really need to import my selected Excel file into the Blublu table.

But the code doesn't work even at the very beginning where I check whether the Blublu table already exists. I get runtime error 3075 - the element from the list can't be found.

By the way, I still get the error on the date field even after I'd changed the format from text to date and tried #12.31.2008# instead.

Any ideas? I'm getting really desperate here!

[jambonjamasb]

Dear Rabbit, the thing is that I have to import similar-looking Excel tables to Access on daily basis. So I'm not appending a set of duplicate records, I really need to import my selected Excel file into the Blublu table.

But the code doesn't work even at the very beginning where I check whether the Blublu table already exists. I get runtime error 3075 - the element from the list can't be found.

By the way, I still get the error on the date field even after I'd changed the format from text to date and tried #12.31.2008# instead.

Any ideas? I'm getting really desperate here!

I am doing something very similar, apart from not creating a new column. I used the macro builder to import from the exact excel doc, to my specific access table? I am only a newbie and wondered if it is easier to do this?

[Rabbit]

Dear Rabbit, the thing is that I have to import similar-looking Excel tables to Access on daily basis. So I'm not appending a set of duplicate records, I really need to import my selected Excel file into the Blublu table.

But the code doesn't work even at the very beginning where I check whether the Blublu table already exists. I get runtime error 3075 - the element from the list can't be found.

By the way, I still get the error on the date field even after I'd changed the format from text to date and tried #12.31.2008# instead.

Any ideas? I'm getting really desperate here!

First, that's not what your code is doing, you're not using 2 different excel tables. Your blublu table is imported from vrtItemSelected, then you try to append that exact same excel data to blublu, which is a copy of that excel data. So your code is trying to get duplicate data, no where do you have an option to choose 2 different excel files.

Second, I did not say to use #12.31.2008# I said to use #12/31/2008#.

[Rabbit]

I am doing something very similar, apart from not creating a new column. I used the macro builder to import from the exact excel doc, to my specific access table? I am only a newbie and wondered if it is easier to do this?

Macros are basically a container for VBA. You can always convert macros to VBA. VBA allows for more flexibility but if they work for you, then you don't need to convert it to VBA unless you want to know how it works.

[zaidlig]

Can I make a suggestion?

Don't Import the Excel file - Link to it instead:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "Blublu", fileaddress, True

Now you have full access to the entire table without any issues on the data types.

I really don't understand the point of adding a column of the same date for every record. Since this date can be saved in a separate table - I'm also against redundant data.

Now that you have the entire table linked to your database you can run a simple delete/append query to another table or just use it as another table in your query builder. If you do use the delete/append method you can also break the link and make the link on each update.

So whats the benefit: 1) when you link you never have to re-import just change the original file data keeping the same location and name; 2) you never have to worry about import errors, or data conversions.

I have a very similiar tool that takes 50,000 records from an exported Discoverer report (Excel) and populates a simple report generator that allows a hundred users the ability to create custom reports in seconds instead of the 45 minutes it takes to get the base data from Oracle. It gets updated every 12 hours - I store the report date once.

[OfficeDummy]

Thank you, zaidlig! Yes, I did think about linking the table - but the trouble is, that I'm working with the existing database and I have no authority to change its construction. So, I actually have to import them.

To Rabbit: I'm European, so my local settings are dd.mm.yyyy. Should I still use the format 12.31.2007 in my tables?

Now to the background: my Excel tables are called "IT-IS Verrechnung [Month] 2008", and I have to import them monthly. They contain two worksheets - IS and IT. One of my tasks is to calculate IT costs for a certain period of time in a search form - that's what a need a month column for.

I've finally succeeded in writing a working code, but it's VERY clumsy and too long. Is there a way to optimise it?

Thanks again for your help!

Option Compare Database
 
Private Sub cmdImport_Click()
 
Dim ADODBConnection As New ADODB.Connection
Dim ADORS As New ADODB.Recordset
Dim fd As FileDialog
Dim curDatabase As Object
Dim tblImported As Object
Dim tblImported1 As Object
Dim tblImported2 As Object
Dim tblImported3 As Object
Dim NewColumn As Object
Dim NewColumn1 As Object
Dim NewColumn2 As Object
Dim NewColumn3 As Object
Dim sFileName As String
Dim strSQL As String
Dim strSQL1 As String
 
Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
 
    With fd
        .AllowMultiSelect = True
        .Filters.Clear
        .Filters.Add "Excel", "*.xls"
        If .Show = -1 Then
            'Durchläuft die ausgewählten Dateien
            For Each vrtSelectedItem In .SelectedItems
 
            'checks whether the table exists
 
              If Existence("IS") = True Then
 
                  'if it's there, then transport the Excel sheet into a temporary table
                  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                            "Temporary", vrtSelectedItem, False, "IS!A1:Z20000"
 
                  'insert a new column to the temporary table
                  Set curDatabase = CurrentDb
 
                  Set tblImported2 = curDatabase.TableDefs("Temporary")
                  Set NewColumn2 = tblImported2.CreateField("Monat", DB_DATE)
                  tblImported2.Fields.Append NewColumn2
 
                  'get the xls file name
 
                  sFileName = Mid(Dir(vrtSelectedItem), 19, 6)
 
                  'fill the inserted column dependant on the month chosen in xls
 
                  If sFileName Like "O*" Then
 
                    strSQL = "UPDATE Temporary SET [Monat]= '31.10.2007' ;"
                    DoCmd.RunSQL strSQL
 
                  ElseIf sFileName Like "N*" Then
                    DoCmd.RunSQL "UPDATE Temporary SET Temporary.Monat = ' 30.11.2007 ';"
 
                  ElseIf sFileName Like "D*" Then
                    strSQL = "UPDATE Temporary SET [Monat]= '31.12.2007' ;"
                    DoCmd.RunSQL strSQL
 
                  ElseIf sFileName Like "Ja*" Then
                    strSQL = "UPDATE Temporary SET [Monat]= '31.01.2008' ;"
                    DoCmd.RunSQL strSQL
 
                  ElseIf sFileName Like "F*" Then
                    strSQL = "UPDATE Temporary SET [Monat]= '28.02.2008' ;"
                    DoCmd.RunSQL strSQL
 
                  ElseIf sFileName Like "Mä*" Then
                    strSQL = "UPDATE Temporary SET [Monat]= '31.03.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Ap" Then
                    strSQL = "UPDATE Temporary SET [Monat]= '30.04.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Mai" Then
                    strSQL = "UPDATE Temporary SET [Monat]= '31.05.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Jun" Then
                    strSQL = "UPDATE Temporary SET [Monat]= '30.06.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Jul*" Then
                    strSQL = "UPDATE Temporary SET [Monat]= '31.03.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Aug" Then
                    strSQL = "UPDATE Temporary SET [Monat]= '31.08.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Sep*" Then
                    strSQL = "UPDATE Temporary SET [Monat]= '30.09.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  Else
                    MsgBox "Datei IT-IS Verrechnung " & sFileName & " existiert nicht."
 
                  End If
 
                  'now append the temporary table into the existing IS table
 
                  strSQL = "INSERT INTO IS" _
                                     & " SELECT Temporary.* FROM Temporary;"
                  DoCmd.RunSQL strSQL
 
                  'delete the temporary table
                  DoCmd.DeleteObject acTable, "Temporary"
                  DoCmd.OpenTable "IS", acViewNormal, acReadOnly
 
 
              'if the table doesn't exist, create it
 
              Else
                  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                            "IS", vrtSelectedItem, False, "IS!A1:Z20000"
 
 
                  'insert a new column
 
                  Set curDatabase = CurrentDb
 
                  Set tblImported = curDatabase.TableDefs("IS")
                  Set NewColumn = tblImported.CreateField("Monat", DB_DATE)
                  tblImported.Fields.Append NewColumn
 
                  'and fill it depending on which month has been selected to import
 
                  sFileName = Mid(Dir(vrtSelectedItem), 19, 6)
 
                  If sFileName Like "O*" Then
 
                    strSQL = "UPDATE IS SET [Monat]= '31.10.2007' ;"
                    DoCmd.RunSQL strSQL
 
                  ElseIf sFileName Like "N*" Then
                    DoCmd.RunSQL "UPDATE IS SET IS.Monat = ' 30.11.2007 ';"
 
                  ElseIf sFileName Like "D*" Then
                    strSQL = "UPDATE IS SET [Monat]= '31.12.2007' ;"
                    DoCmd.RunSQL strSQL
 
                  ElseIf sFileName Like "Ja*" Then
                    strSQL = "UPDATE IS SET [Monat]= '31.01.2008' ;"
                    DoCmd.RunSQL strSQL
 
                  ElseIf sFileName Like "F*" Then
                    strSQL = "UPDATE IS SET [Monat]= '28.02.2008' ;"
                    DoCmd.RunSQL strSQL
 
                  ElseIf sFileName Like "Mä*" Then
                    strSQL = "UPDATE IS SET [Monat]= '31.03.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Ap" Then
                    strSQL = "UPDATE IS SET [Monat]= '30.04.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Mai" Then
                    strSQL = "UPDATE IS SET [Monat]= '31.05.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Jun" Then
                    strSQL = "UPDATE IS SET [Monat]= '30.06.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Jul*" Then
                    strSQL = "UPDATE IS SET [Monat]= '31.03.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Aug" Then
                    strSQL = "UPDATE IS SET [Monat]= '31.08.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Sep*" Then
                    strSQL = "UPDATE IS SET [Monat]= '30.09.2008' ;"
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings False
 
                  Else
                    MsgBox "Datei IT-IS Verrechnung " & sFileName & " existiert nicht."
 
                  End If
 
              DoCmd.OpenTable "IS", acViewNormal, acReadOnly
 
 
 
 
              End If
 

Part two follows in a separate post....

[OfficeDummy]

Part two: nearly the same procedure.

  'now import the second worksheet: the same operation
              'first check if the table has already been imported
 
              If Existence("IT") = True Then
 
                  'if it's there, then
                  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                            "Vremenno", vrtSelectedItem, False, "IT!A1:Z20000"
 
                  'insert a new column to the temporary table
                  Set curDatabase = CurrentDb
 
                  Set tblImported1 = curDatabase.TableDefs("Vremenno")
                  Set NewColumn1 = tblImported1.CreateField("Monat", DB_DATE)
                  tblImported1.Fields.Append NewColumn1
 
                  'get the xls file name
 
                  sFileName = Mid(Dir(vrtSelectedItem), 19, 6)
 
                  'fill the inserted column dependant on the month chosen in xls
 
                  If sFileName Like "O*" Then
 
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '31.10.2007' ;"
                    DoCmd.RunSQL strSQL1
 
                  ElseIf sFileName Like "N*" Then
                    DoCmd.RunSQL "UPDATE Vremenno SET Vremenno.Monat = ' 30.11.2007 ';"
 
                  ElseIf sFileName Like "D*" Then
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '31.12.2007' ;"
                    DoCmd.RunSQL strSQL1
 
                  ElseIf sFileName Like "Ja*" Then
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '31.01.2008' ;"
                    DoCmd.RunSQL strSQL1
 
                  ElseIf sFileName Like "F*" Then
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '28.02.2008' ;"
                    DoCmd.RunSQL strSQL1
 
                  ElseIf sFileName Like "Mä*" Then
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '31.03.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Ap" Then
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '30.04.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Mai" Then
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '31.05.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Jun" Then
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '30.06.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Jul*" Then
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '31.03.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Aug" Then
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '31.08.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Sep*" Then
                    strSQL1 = "UPDATE Vremenno SET [Monat]= '30.09.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  Else
                    MsgBox "Datei IT-IS Verrechnung " & sFileName & " existiert nicht."
 
                  End If
 
                  'now append the temporary table to the existing IS table
 
                  strSQL1 = "INSERT INTO IT" _
                                     & " SELECT Vremenno.* FROM Vremenno;"
                  DoCmd.RunSQL strSQL1
 
                  'delete the temporary table
                  DoCmd.DeleteObject acTable, "Vremenno"
                  DoCmd.OpenTable "IT", acViewNormal, acReadOnly
 
 
              'if the table doesn't exist, create it
 
              Else
                  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                            "IT", vrtSelectedItem, False, "IT!A1:Z20000"
 
 
                  'insert a new column
 
                  Set curDatabase = CurrentDb
 
                  Set tblImported3 = curDatabase.TableDefs("IT")
                  Set NewColumn3 = tblImported3.CreateField("Monat", DB_DATE)
                  tblImported3.Fields.Append NewColumn3
 
                  'and fill it depending on which month has been selected to import
 
                  sFileName = Mid(Dir(vrtSelectedItem), 19, 6)
 
                  If sFileName Like "O*" Then
 
                    strSQL1 = "UPDATE IT SET [Monat]= '31.10.2007' ;"
                    DoCmd.RunSQL strSQL1
 
                  ElseIf sFileName Like "N*" Then
                    DoCmd.RunSQL "UPDATE IT SET IT.Monat = ' 30.11.2007 ';"
 
                  ElseIf sFileName Like "D*" Then
                    strSQL1 = "UPDATE IT SET [Monat]= '31.12.2007' ;"
                    DoCmd.RunSQL strSQL1
 
                  ElseIf sFileName Like "Ja*" Then
                    strSQL1 = "UPDATE IT SET [Monat]= '31.01.2008' ;"
                    DoCmd.RunSQL strSQL1
 
                  ElseIf sFileName Like "F*" Then
                    strSQL1 = "UPDATE IT SET [Monat]= '28.02.2008' ;"
                    DoCmd.RunSQL strSQL1
 
                  ElseIf sFileName Like "Mä*" Then
                    strSQL1 = "UPDATE IT SET [Monat]= '31.03.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Ap" Then
                    strSQL1 = "UPDATE IT SET [Monat]= '30.04.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Mai" Then
                    strSQL1 = "UPDATE IT SET [Monat]= '31.05.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Jun" Then
                    strSQL1 = "UPDATE IT SET [Monat]= '30.06.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Jul*" Then
                    strSQL1 = "UPDATE IT SET [Monat]= '31.03.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Aug" Then
                    strSQL1 = "UPDATE IT SET [Monat]= '31.08.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  ElseIf sFileName Like "Sep*" Then
                    strSQL1 = "UPDATE IT SET [Monat]= '30.09.2008' ;"
                    DoCmd.RunSQL strSQL1
                    DoCmd.SetWarnings False
 
                  Else
                    MsgBox "Datei IT-IS Verrechnung " & sFileName & " existiert nicht."
 
                  End If
 
               DoCmd.OpenTable "IT", acViewNormal, acReadOnly
              End If
            Next vrtSelectedItem
         End If
    End With
 
End Sub
 

Thanks again in advance!

[zaidlig]

I'm not sure how linking verses importing is changing the database since you are only importing to a temporary table.

In any case you are basing your appended date on the file name why not use the file date instead. If the Excel file is created at the end of the month you can use the file date to directly calculate the needed date for your date column. If the file is created the day or week after the end of the month just use a datediff to calculate your date column.

If you are running reports based on the month why do you need the last day of the month. You can easily run your report with a column of month/year dates regardless of the day which defaults to 1 if you don't include it. It is easier to create "1-Jan-2008" then the exact last day of the month.

[OfficeDummy]

I'm not sure how linking verses importing is changing the database since you are only importing to a temporary table.

In any case you are basing your appended date on the file name why not use the file date instead. If the Excel file is created at the end of the month you can use the file date to directly calculate the needed date for your date column. If the file is created the day or week after the end of the month just use a datediff to calculate your date column.

If you are running reports based on the month why do you need the last day of the month. You can easily run your report with a column of month/year dates regardless of the day which defaults to 1 if you don't include it. It is easier to create "1-Jan-2008" then the exact last day of the month.

Thanks, zaidlig! I like your idea, but isn't it easier to import the whole thing if the database has to be automated?

I don't exactly need the last day of the month - Access just needs to "know" that it must search for records in January. But I couldn't format the date column so that I can just type 'January'. Hmmm...

[zaidlig]

Converting a string to a date will automatically default to the first day of the month. So there is no reason to have all your if statements.

As for Linking and automation. Think about it if you have a database the checks a target folder when it opens and if it locates a new Excel file (based on the file date) it can then link to it, run its append query (also saving the file date) and break the link.

No fuss no muss. No selecting and importing a file, the database does it all. You could have it check every time it opens or at nighty when nobody is using it.

[OfficeDummy]

Converting a string to a date will automatically default to the first day of the month. So there is no reason to have all your if statements.

As for Linking and automation. Think about it if you have a database the checks a target folder when it opens and if it locates a new Excel file (based on the file date) it can then link to it, run its append query (also saving the file date) and break the link.

No fuss no muss. No selecting and importing a file, the database does it all. You could have it check every time it opens or at nighty when nobody is using it.

Thanks, zaidlig! It's a great idea, only how do you do it with VBA? Maybe, you could give me an Internet link to an sample code or something?

Is it actually more "professional" to link than to import? I was actually told that importing is better...

[zaidlig]

Thanks, zaidlig! It's a great idea, only how do you do it with VBA? Maybe, you could give me an Internet link to an sample code or something?

Is it actually more "professional" to link than to import? I was actually told that importing is better...

Most of my work is VBA but I do use objects from many references and VB.Net. Everything I have described so far is simple VBA.

Step by step
1) If you know the path, read the filedate using the "FileDateTime" function or you can use the Dir function to step through a folder and find matching files and compare the stored file date.
2) save the file date in an internal table to use later
3) Link to the file using the tranferspreadsheet with the transfer type set to acLink using the path found/known in 1).
4) Run your query to append the linked table to your master table and write the file date for each of the new records.
5) Break the link by deleting the linked table. Or run again for Sheet2

Linking is perferrable to Importing if you don't have control over the external data. You can't have an Import error with a linked file. Each record is not checked until it called for. Which means a linked file can have Null values where you require values - a simple update query would filter out all the Null values when it runs. Or the update query can substitue values during the update. The beauty of Linking is you connect and take what you want without all the garbage you don't. Also you can use the same link over and over - so you can link to sheet 1 then rerun the same link code for sheet 2. Think of the power: say you need to know the invoice total per client that is contained in 1300 invoice spreadsheets in a folder. You write a few lines of code to link to each invoice save the customer number, name and invoice total break the link and move to the next file. The answer is done in seconds.

So exactly where do you need help - by step number?