Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Unanswered: Access VBA Help Needed

    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)
    End Function
    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

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE [" & Sht_Tab_Name & "].* FROM [" & Sht_Tab_Name & "];"
    DoCmd.SetWarnings True

    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
    End Function

    Function Import_CMS_Spreadsheet()
    Import_Table_Data "tblIMPORT_CMS_ALL", "CMS.XLS"
    End Function


    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)
    Do
    Time2 = (Now)
    If (Time2 - Time1) * 86400 >= Time_Seconds Then Exit Do
    Loop
    End Function


    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
    Exit Sub
    End If

    Application.DoCmd.SetWarnings False

    Application.FollowHyperlink "S:\2004Stat\Sales_MIS\OSG Handover\Export\CMS.xls"

    'nc3Time_Delay 10

    'SendKeys ("^w"), True
    'DoCmd.OpenQuery "qryAppend_CMS_By_Brand_ALL", acViewNormal, acEdit

    'Mess = ("CMS data has been successfully imported")

    End Sub

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Use the DoCmd.transferSpreadsheet command from Access to import the Excel spreadsheet into an existing or new table in Access.

    Ideally you should stage the import into a temporary table, then carry out any validation you need to before loading it into the live Access table using an INSERT INTO... SQL statment

    Regards
    Justin

  3. #3
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •