Hi, I am trying to fix an existing database that is updated every day from an Excel sheet. all i want to do is to basically copy everything from the Excel sheet and paste append into an existing table in access, then run a query that appends it all into yet another table. Im have a basic knowledge of Excel VBA but some of the code just confuses me. Please help. I have managed to automate everything else needed in Excel already to the point where the data is even sitting there copied. but at the moment from there on it has to be manual. Many thanks in advance for any help you can provide.
This is the code from a Module called 'Import File From Excel' so i guess this has something to do with the process. Option Explicit
Const Report_Data_Path = ":\2004Stat\Sales_MIS\OSG Handover\Export"
Function Get_Database_Path() As String
Get_Database_Path = Left(Application.CurrentDb.Name, 1)
Function Import_Table_Data(Sht_Tab_Name As String, Optional File_If_Diff_From_Tabl As String, _
Optional Any_Non_Excel_Format As String, Optional Any_Spec_Name As String, _
Optional Any_Formatting_Queries As Variant)
If File_If_Diff_From_Tabl = Empty Then File_If_Diff_From_Tabl = Sht_Tab_Name
Dim rdP As String, Fx As Integer
rdP = Get_Database_Path
Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, Sht_Tab_Name, _
rdP & Report_Data_Path & "\" & File_If_Diff_From_Tabl, True
Import_Table_Data "tblIMPORT_CMS_ALL", "CMS.XLS"
Function nc3Time_Delay(Optional Time_Seconds As Integer, Optional Use_InputBox As Integer) As String
Dim Time1 As Date, Time2 As Date
If Time_Seconds = Empty Then If Use_InputBox = Empty Then Time_Seconds = InputBox("Enter a time delay (S):")
Time1 = (Now)
Time2 = (Now)
If (Time2 - Time1) * 86400 >= Time_Seconds Then Exit Do
this is the code from the button that (in theory) actions the whole process.
Private Sub Command4_Click()
Dim mess As String
mess = ("CLOSE EXCEL COMPLETELY BEFORE CONTINUING." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "This will now run a macro to import all CMS data into Dashboard. Press 'No' to cancel this event." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Have you downloaded all 4 CMS exports for the correct date and saved them to the correct location?")
If MsgBox(mess, vbYesNo + vbExclamation, "Dashboard 2004") = vbNo Then
You can also link to the Excel spreadsheet in much the same way you link to a table. If the data on the spreadsheet is arrayed in table-like structure, you can use it in queries and such just like any other table. Whenever you update the sheet, run your queries to append. No code or importing needed.