By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

importing an excel table into already existing access table with vba

P: 18
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:

Expand|Select|Wrap|Line Numbers
  1.  Dim ADODBConnection As New ADODB.Connection
  2.     Dim ADORS As New ADODB.Recordset
  3.     Dim fd As FileDialog
  4.     Dim arNames As Variant
  5.     Dim strMonat As String
  6.     Dim NewColumn As Object
  7.     Dim curDatabase As Object
  8.     Dim tblImported As Object
  9.     Dim tblImported1 As Object
  10.     Dim strSQL As String
  11.     Dim sFileName As String
  12.     Dim objTable As TableDef
  13.  
  14.  
  15.  
  16.     'Calls the dialog window
  17.     Set fd = Application.FileDialog(msoFileDialogFilePicker)
  18.  
  19.  
  20.     With fd
  21.         .AllowMultiSelect = True
  22.         .Filters.Clear
  23.         .Filters.Add "Excel", "*.xls"
  24.         If .Show = -1 Then
  25.             'runs throught selected files
  26.             For Each vrtSelectedItem In .SelectedItems
  27.  
  28.                 'checks whether the table already exists
  29.                 Set objTable = CurrentDb.TableDefs("Blublu")
  30.  
  31.                 'if it doesn't, import it
  32.                 If objTable Is Nothing Then
  33.  
  34.                    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Blublu", vrtSelectedItem, False, "Tabelle1!A1:D45"
  35.  
  36.  
  37.                      'inserts a new column
  38.  
  39.                    Set curDatabase = CurrentDb        
  40.                    Set tblImported = curDatabase.TableDefs("Blublu")
  41.                    Set NewColumn = tblImported.CreateField("Monat", DB_TEXT)
  42.                    tblImported.Fields.Append NewColumn
  43.  
  44.  
  45.                      'and fills it depending on a selected Excel table
  46.  
  47.                    sFileName = Mid(Dir(vrtSelectedItem), 9, 6)
  48.  
  49.                    If sFileName Like "I*" Then
  50.  
  51.                        strSQL = "UPDATE Blublu SET [Monat]= #31.12.2008# ;"
  52.                        'strSQL doesn't work: the compiler discovered a syntax error, which is a complete mystery to me
  53.                        DoCmd.RunSQL strSQL
  54.  
  55.                    ElseIf sFileName Like "E*" Then
  56.                         DoCmd.RunSQL "UPDATE Blublu SET Blublu.Monat = #31.2008# ;"
  57.  
  58.                    Else
  59.                         MsgBox "The file" & sFileName & " doesn't exist."
  60.  
  61.                    End If
  62.  
  63.              'if the table already exists, append the records from Excel to it:
  64.  
  65.            Else
  66.  
  67.                    DoCmd.RunSQL "INSERT INTO Blublu" & _
  68.                                  "SELECT *." & vrtSelectedItem & "FROM" & vrtSelectedItem & ";"
  69.  
  70.              End If
  71.  
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.
Jan 9 '08 #1
Share this Question
Share on Google+
15 Replies


Rabbit
Expert Mod 10K+
P: 12,355
Try #12/31/2008# instead.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO Blublu" & _
  2. "SELECT *." & vrtSelectedItem & "FROM" & vrtSelectedItem & ";"
  3.  
Assuming that vrtSelectedItem is the path of a file, let's say C:\Workbook1.xls then your code evaluates to:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO BlubluSELECT *.C:\Workbook1.xlsFROMC:\Workbook1.xls;
  2.  
Hopefully you can see why this will not work.
Jan 9 '08 #2

P: 18
Try #12/31/2008# instead.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO Blublu" & _
  2. "SELECT *." & vrtSelectedItem & "FROM" & vrtSelectedItem & ";"
  3.  
Assuming that vrtSelectedItem is the path of a file, let's say C:\Workbook1.xls then your code evaluates to:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO BlubluSELECT *.C:\Workbook1.xlsFROMC:\Workbook1.xls;
  2.  
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....
Jan 9 '08 #3

Rabbit
Expert Mod 10K+
P: 12,355
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:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Blublu
  2. SELECT *
  3. FROM Blublu;
  4.  
Jan 9 '08 #4

P: 18
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:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Blublu
  2. SELECT *
  3. FROM Blublu;
  4.  
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!
Jan 10 '08 #5

P: 41
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?
Jan 10 '08 #6

Rabbit
Expert Mod 10K+
P: 12,355
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#.
Jan 10 '08 #7

Rabbit
Expert Mod 10K+
P: 12,355
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.
Jan 10 '08 #8

P: 45
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.
Jan 10 '08 #9

P: 18
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!

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub cmdImport_Click()
  4.  
  5. Dim ADODBConnection As New ADODB.Connection
  6. Dim ADORS As New ADODB.Recordset
  7. Dim fd As FileDialog
  8. Dim curDatabase As Object
  9. Dim tblImported As Object
  10. Dim tblImported1 As Object
  11. Dim tblImported2 As Object
  12. Dim tblImported3 As Object
  13. Dim NewColumn As Object
  14. Dim NewColumn1 As Object
  15. Dim NewColumn2 As Object
  16. Dim NewColumn3 As Object
  17. Dim sFileName As String
  18. Dim strSQL As String
  19. Dim strSQL1 As String
  20.  
  21. Set fd = Application.FileDialog(msoFileDialogFilePicker)
  22.  
  23.  
  24.     With fd
  25.         .AllowMultiSelect = True
  26.         .Filters.Clear
  27.         .Filters.Add "Excel", "*.xls"
  28.         If .Show = -1 Then
  29.             'Durchläuft die ausgewählten Dateien
  30.             For Each vrtSelectedItem In .SelectedItems
  31.  
  32.             'checks whether the table exists
  33.  
  34.               If Existence("IS") = True Then
  35.  
  36.                   'if it's there, then transport the Excel sheet into a temporary table
  37.                   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
  38.                                             "Temporary", vrtSelectedItem, False, "IS!A1:Z20000"
  39.  
  40.                   'insert a new column to the temporary table
  41.                   Set curDatabase = CurrentDb
  42.  
  43.                   Set tblImported2 = curDatabase.TableDefs("Temporary")
  44.                   Set NewColumn2 = tblImported2.CreateField("Monat", DB_DATE)
  45.                   tblImported2.Fields.Append NewColumn2
  46.  
  47.                   'get the xls file name
  48.  
  49.                   sFileName = Mid(Dir(vrtSelectedItem), 19, 6)
  50.  
  51.                   'fill the inserted column dependant on the month chosen in xls
  52.  
  53.                   If sFileName Like "O*" Then
  54.  
  55.                     strSQL = "UPDATE Temporary SET [Monat]= '31.10.2007' ;"
  56.                     DoCmd.RunSQL strSQL
  57.  
  58.                   ElseIf sFileName Like "N*" Then
  59.                     DoCmd.RunSQL "UPDATE Temporary SET Temporary.Monat = ' 30.11.2007 ';"
  60.  
  61.                   ElseIf sFileName Like "D*" Then
  62.                     strSQL = "UPDATE Temporary SET [Monat]= '31.12.2007' ;"
  63.                     DoCmd.RunSQL strSQL
  64.  
  65.                   ElseIf sFileName Like "Ja*" Then
  66.                     strSQL = "UPDATE Temporary SET [Monat]= '31.01.2008' ;"
  67.                     DoCmd.RunSQL strSQL
  68.  
  69.                   ElseIf sFileName Like "F*" Then
  70.                     strSQL = "UPDATE Temporary SET [Monat]= '28.02.2008' ;"
  71.                     DoCmd.RunSQL strSQL
  72.  
  73.                   ElseIf sFileName Like "Mä*" Then
  74.                     strSQL = "UPDATE Temporary SET [Monat]= '31.03.2008' ;"
  75.                     DoCmd.RunSQL strSQL
  76.                     DoCmd.SetWarnings False
  77.  
  78.                   ElseIf sFileName Like "Ap" Then
  79.                     strSQL = "UPDATE Temporary SET [Monat]= '30.04.2008' ;"
  80.                     DoCmd.RunSQL strSQL
  81.                     DoCmd.SetWarnings False
  82.  
  83.                   ElseIf sFileName Like "Mai" Then
  84.                     strSQL = "UPDATE Temporary SET [Monat]= '31.05.2008' ;"
  85.                     DoCmd.RunSQL strSQL
  86.                     DoCmd.SetWarnings False
  87.  
  88.                   ElseIf sFileName Like "Jun" Then
  89.                     strSQL = "UPDATE Temporary SET [Monat]= '30.06.2008' ;"
  90.                     DoCmd.RunSQL strSQL
  91.                     DoCmd.SetWarnings False
  92.  
  93.                   ElseIf sFileName Like "Jul*" Then
  94.                     strSQL = "UPDATE Temporary SET [Monat]= '31.03.2008' ;"
  95.                     DoCmd.RunSQL strSQL
  96.                     DoCmd.SetWarnings False
  97.  
  98.                   ElseIf sFileName Like "Aug" Then
  99.                     strSQL = "UPDATE Temporary SET [Monat]= '31.08.2008' ;"
  100.                     DoCmd.RunSQL strSQL
  101.                     DoCmd.SetWarnings False
  102.  
  103.                   ElseIf sFileName Like "Sep*" Then
  104.                     strSQL = "UPDATE Temporary SET [Monat]= '30.09.2008' ;"
  105.                     DoCmd.RunSQL strSQL
  106.                     DoCmd.SetWarnings False
  107.  
  108.                   Else
  109.                     MsgBox "Datei IT-IS Verrechnung " & sFileName & " existiert nicht."
  110.  
  111.                   End If
  112.  
  113.                   'now append the temporary table into the existing IS table
  114.  
  115.                   strSQL = "INSERT INTO IS" _
  116.                                      & " SELECT Temporary.* FROM Temporary;"
  117.                   DoCmd.RunSQL strSQL
  118.  
  119.                   'delete the temporary table
  120.                   DoCmd.DeleteObject acTable, "Temporary"
  121.                   DoCmd.OpenTable "IS", acViewNormal, acReadOnly
  122.  
  123.  
  124.               'if the table doesn't exist, create it
  125.  
  126.               Else
  127.                   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
  128.                                             "IS", vrtSelectedItem, False, "IS!A1:Z20000"
  129.  
  130.  
  131.                   'insert a new column
  132.  
  133.                   Set curDatabase = CurrentDb
  134.  
  135.                   Set tblImported = curDatabase.TableDefs("IS")
  136.                   Set NewColumn = tblImported.CreateField("Monat", DB_DATE)
  137.                   tblImported.Fields.Append NewColumn
  138.  
  139.                   'and fill it depending on which month has been selected to import
  140.  
  141.                   sFileName = Mid(Dir(vrtSelectedItem), 19, 6)
  142.  
  143.                   If sFileName Like "O*" Then
  144.  
  145.                     strSQL = "UPDATE IS SET [Monat]= '31.10.2007' ;"
  146.                     DoCmd.RunSQL strSQL
  147.  
  148.                   ElseIf sFileName Like "N*" Then
  149.                     DoCmd.RunSQL "UPDATE IS SET IS.Monat = ' 30.11.2007 ';"
  150.  
  151.                   ElseIf sFileName Like "D*" Then
  152.                     strSQL = "UPDATE IS SET [Monat]= '31.12.2007' ;"
  153.                     DoCmd.RunSQL strSQL
  154.  
  155.                   ElseIf sFileName Like "Ja*" Then
  156.                     strSQL = "UPDATE IS SET [Monat]= '31.01.2008' ;"
  157.                     DoCmd.RunSQL strSQL
  158.  
  159.                   ElseIf sFileName Like "F*" Then
  160.                     strSQL = "UPDATE IS SET [Monat]= '28.02.2008' ;"
  161.                     DoCmd.RunSQL strSQL
  162.  
  163.                   ElseIf sFileName Like "Mä*" Then
  164.                     strSQL = "UPDATE IS SET [Monat]= '31.03.2008' ;"
  165.                     DoCmd.RunSQL strSQL
  166.                     DoCmd.SetWarnings False
  167.  
  168.                   ElseIf sFileName Like "Ap" Then
  169.                     strSQL = "UPDATE IS SET [Monat]= '30.04.2008' ;"
  170.                     DoCmd.RunSQL strSQL
  171.                     DoCmd.SetWarnings False
  172.  
  173.                   ElseIf sFileName Like "Mai" Then
  174.                     strSQL = "UPDATE IS SET [Monat]= '31.05.2008' ;"
  175.                     DoCmd.RunSQL strSQL
  176.                     DoCmd.SetWarnings False
  177.  
  178.                   ElseIf sFileName Like "Jun" Then
  179.                     strSQL = "UPDATE IS SET [Monat]= '30.06.2008' ;"
  180.                     DoCmd.RunSQL strSQL
  181.                     DoCmd.SetWarnings False
  182.  
  183.                   ElseIf sFileName Like "Jul*" Then
  184.                     strSQL = "UPDATE IS SET [Monat]= '31.03.2008' ;"
  185.                     DoCmd.RunSQL strSQL
  186.                     DoCmd.SetWarnings False
  187.  
  188.                   ElseIf sFileName Like "Aug" Then
  189.                     strSQL = "UPDATE IS SET [Monat]= '31.08.2008' ;"
  190.                     DoCmd.RunSQL strSQL
  191.                     DoCmd.SetWarnings False
  192.  
  193.                   ElseIf sFileName Like "Sep*" Then
  194.                     strSQL = "UPDATE IS SET [Monat]= '30.09.2008' ;"
  195.                     DoCmd.RunSQL strSQL
  196.                     DoCmd.SetWarnings False
  197.  
  198.                   Else
  199.                     MsgBox "Datei IT-IS Verrechnung " & sFileName & " existiert nicht."
  200.  
  201.                   End If
  202.  
  203.               DoCmd.OpenTable "IS", acViewNormal, acReadOnly
  204.  
  205.  
  206.  
  207.  
  208.               End If
  209.  
Part two follows in a separate post....
Jan 11 '08 #10

P: 18
Part two: nearly the same procedure.

Expand|Select|Wrap|Line Numbers
  1.   'now import the second worksheet: the same operation
  2.               'first check if the table has already been imported
  3.  
  4.               If Existence("IT") = True Then
  5.  
  6.                   'if it's there, then
  7.                   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
  8.                                             "Vremenno", vrtSelectedItem, False, "IT!A1:Z20000"
  9.  
  10.                   'insert a new column to the temporary table
  11.                   Set curDatabase = CurrentDb
  12.  
  13.                   Set tblImported1 = curDatabase.TableDefs("Vremenno")
  14.                   Set NewColumn1 = tblImported1.CreateField("Monat", DB_DATE)
  15.                   tblImported1.Fields.Append NewColumn1
  16.  
  17.                   'get the xls file name
  18.  
  19.                   sFileName = Mid(Dir(vrtSelectedItem), 19, 6)
  20.  
  21.                   'fill the inserted column dependant on the month chosen in xls
  22.  
  23.                   If sFileName Like "O*" Then
  24.  
  25.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '31.10.2007' ;"
  26.                     DoCmd.RunSQL strSQL1
  27.  
  28.                   ElseIf sFileName Like "N*" Then
  29.                     DoCmd.RunSQL "UPDATE Vremenno SET Vremenno.Monat = ' 30.11.2007 ';"
  30.  
  31.                   ElseIf sFileName Like "D*" Then
  32.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '31.12.2007' ;"
  33.                     DoCmd.RunSQL strSQL1
  34.  
  35.                   ElseIf sFileName Like "Ja*" Then
  36.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '31.01.2008' ;"
  37.                     DoCmd.RunSQL strSQL1
  38.  
  39.                   ElseIf sFileName Like "F*" Then
  40.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '28.02.2008' ;"
  41.                     DoCmd.RunSQL strSQL1
  42.  
  43.                   ElseIf sFileName Like "Mä*" Then
  44.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '31.03.2008' ;"
  45.                     DoCmd.RunSQL strSQL1
  46.                     DoCmd.SetWarnings False
  47.  
  48.                   ElseIf sFileName Like "Ap" Then
  49.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '30.04.2008' ;"
  50.                     DoCmd.RunSQL strSQL1
  51.                     DoCmd.SetWarnings False
  52.  
  53.                   ElseIf sFileName Like "Mai" Then
  54.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '31.05.2008' ;"
  55.                     DoCmd.RunSQL strSQL1
  56.                     DoCmd.SetWarnings False
  57.  
  58.                   ElseIf sFileName Like "Jun" Then
  59.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '30.06.2008' ;"
  60.                     DoCmd.RunSQL strSQL1
  61.                     DoCmd.SetWarnings False
  62.  
  63.                   ElseIf sFileName Like "Jul*" Then
  64.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '31.03.2008' ;"
  65.                     DoCmd.RunSQL strSQL1
  66.                     DoCmd.SetWarnings False
  67.  
  68.                   ElseIf sFileName Like "Aug" Then
  69.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '31.08.2008' ;"
  70.                     DoCmd.RunSQL strSQL1
  71.                     DoCmd.SetWarnings False
  72.  
  73.                   ElseIf sFileName Like "Sep*" Then
  74.                     strSQL1 = "UPDATE Vremenno SET [Monat]= '30.09.2008' ;"
  75.                     DoCmd.RunSQL strSQL1
  76.                     DoCmd.SetWarnings False
  77.  
  78.                   Else
  79.                     MsgBox "Datei IT-IS Verrechnung " & sFileName & " existiert nicht."
  80.  
  81.                   End If
  82.  
  83.                   'now append the temporary table to the existing IS table
  84.  
  85.                   strSQL1 = "INSERT INTO IT" _
  86.                                      & " SELECT Vremenno.* FROM Vremenno;"
  87.                   DoCmd.RunSQL strSQL1
  88.  
  89.                   'delete the temporary table
  90.                   DoCmd.DeleteObject acTable, "Vremenno"
  91.                   DoCmd.OpenTable "IT", acViewNormal, acReadOnly
  92.  
  93.  
  94.               'if the table doesn't exist, create it
  95.  
  96.               Else
  97.                   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
  98.                                             "IT", vrtSelectedItem, False, "IT!A1:Z20000"
  99.  
  100.  
  101.                   'insert a new column
  102.  
  103.                   Set curDatabase = CurrentDb
  104.  
  105.                   Set tblImported3 = curDatabase.TableDefs("IT")
  106.                   Set NewColumn3 = tblImported3.CreateField("Monat", DB_DATE)
  107.                   tblImported3.Fields.Append NewColumn3
  108.  
  109.                   'and fill it depending on which month has been selected to import
  110.  
  111.                   sFileName = Mid(Dir(vrtSelectedItem), 19, 6)
  112.  
  113.                   If sFileName Like "O*" Then
  114.  
  115.                     strSQL1 = "UPDATE IT SET [Monat]= '31.10.2007' ;"
  116.                     DoCmd.RunSQL strSQL1
  117.  
  118.                   ElseIf sFileName Like "N*" Then
  119.                     DoCmd.RunSQL "UPDATE IT SET IT.Monat = ' 30.11.2007 ';"
  120.  
  121.                   ElseIf sFileName Like "D*" Then
  122.                     strSQL1 = "UPDATE IT SET [Monat]= '31.12.2007' ;"
  123.                     DoCmd.RunSQL strSQL1
  124.  
  125.                   ElseIf sFileName Like "Ja*" Then
  126.                     strSQL1 = "UPDATE IT SET [Monat]= '31.01.2008' ;"
  127.                     DoCmd.RunSQL strSQL1
  128.  
  129.                   ElseIf sFileName Like "F*" Then
  130.                     strSQL1 = "UPDATE IT SET [Monat]= '28.02.2008' ;"
  131.                     DoCmd.RunSQL strSQL1
  132.  
  133.                   ElseIf sFileName Like "Mä*" Then
  134.                     strSQL1 = "UPDATE IT SET [Monat]= '31.03.2008' ;"
  135.                     DoCmd.RunSQL strSQL1
  136.                     DoCmd.SetWarnings False
  137.  
  138.                   ElseIf sFileName Like "Ap" Then
  139.                     strSQL1 = "UPDATE IT SET [Monat]= '30.04.2008' ;"
  140.                     DoCmd.RunSQL strSQL1
  141.                     DoCmd.SetWarnings False
  142.  
  143.                   ElseIf sFileName Like "Mai" Then
  144.                     strSQL1 = "UPDATE IT SET [Monat]= '31.05.2008' ;"
  145.                     DoCmd.RunSQL strSQL1
  146.                     DoCmd.SetWarnings False
  147.  
  148.                   ElseIf sFileName Like "Jun" Then
  149.                     strSQL1 = "UPDATE IT SET [Monat]= '30.06.2008' ;"
  150.                     DoCmd.RunSQL strSQL1
  151.                     DoCmd.SetWarnings False
  152.  
  153.                   ElseIf sFileName Like "Jul*" Then
  154.                     strSQL1 = "UPDATE IT SET [Monat]= '31.03.2008' ;"
  155.                     DoCmd.RunSQL strSQL1
  156.                     DoCmd.SetWarnings False
  157.  
  158.                   ElseIf sFileName Like "Aug" Then
  159.                     strSQL1 = "UPDATE IT SET [Monat]= '31.08.2008' ;"
  160.                     DoCmd.RunSQL strSQL1
  161.                     DoCmd.SetWarnings False
  162.  
  163.                   ElseIf sFileName Like "Sep*" Then
  164.                     strSQL1 = "UPDATE IT SET [Monat]= '30.09.2008' ;"
  165.                     DoCmd.RunSQL strSQL1
  166.                     DoCmd.SetWarnings False
  167.  
  168.                   Else
  169.                     MsgBox "Datei IT-IS Verrechnung " & sFileName & " existiert nicht."
  170.  
  171.                   End If
  172.  
  173.                DoCmd.OpenTable "IT", acViewNormal, acReadOnly
  174.               End If
  175.             Next vrtSelectedItem
  176.          End If
  177.     End With
  178.  
  179. End Sub
  180.  
Thanks again in advance!
Jan 11 '08 #11

P: 45
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.
Jan 11 '08 #12

P: 18
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...
Jan 11 '08 #13

P: 45
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.
Jan 11 '08 #14

P: 18
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...
Jan 11 '08 #15

P: 45
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?
Jan 11 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.