Transfer Sheet Macro

[craig1988]

Hi



I am developing a database that will act as a repository for 4 channels of traffic that we have.

I currently have 4 tables; DDIn, DDOut, CTIn and CTOut.

We recieve 3 daily excel spreadsheets. They all contain a number of worksheets. They all have at least 4 worksheets that correspond to the tables that I have in the database. Some have summary worksheets that can be diregarded.

I want the users to be able to click a command button which will open a dialog box to choose the file (the spreadsheet) and append the data in each of the 4 worksheets to the corresponding tables of the database.

Hope somebody can help me!

[ranman256]

I would save the workbook to a generic file name so it can be saved to the same place everytime. This way the queries always point to the same worksheet. Attach all the worksheets as tables.
Then build the 4 queries to import the data from all tables.
put all these in an IMPORT MACRO....say mImportAllXLSheets

So the user will pick the file,
the file will save to the generic name (therefore already be connected to the db)
the macro runs
done...all data imported.

Below is the UserSelectFile code: (you can hookin your macro)

' by ranman256
Private Const kGENERICxl = "c:\abc\generic.xlsx"
'-----------------------
Sub ImportXLData()
'-----------------------
Dim vFile
MsgBox "YOU MUST ADD REFERENCE : Microsoft Office Object Library", , "Then delete this msg"
vFile = UserPick1File("c:\startpath")
If vFile <> "" Then
    FileCopy vFile, kGENERICxl
    
    If MsgBox("Begin Import", vbQuestion + vbYesNo, "Confirm") = vbYes Then
       docmd.runmacro "mImportAllXLdata"
    End If
End If
End Sub
'-----------------------
Public Function UserPick1File(Optional pvPath)
'-----------------------
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String
'===================
'YOU MUST ADD REFERENCE : Microsoft Office x.x Object Library, in vbe menu, TOOLS, REFERENCES
'===================
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls;*.xlsx"
    .Filters.Add "All Files", "*.*"
    .InitialFileName = "c:\"
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    
        If .Show = 0 Then
           'There is a problem
           Exit Function
        End If
    
    'Save the first file selected
    UserPick1File = Trim(.SelectedItems(1))
End With
End Function

[craig1988]

Hi Ranman256

I would be very grateful if you could talk me through the first few steps...

Attach all the worksheets as tables. Build 4 queries to import the data from all tables.

Regards
Craig

[NTC]

R256 advice is always good - but know also that this is an embedded feature of the Access product requiring no coding at all. You list yourself as an Access 2003 user and I don't work with that old edition much and can't recall the layout of the screen - - - but generically speaking there is an 'external data' feature whereby you select excel and a wizard will launch that walks you through the import of the excel sheet to append into an existing table.

So in terms of your desire to 'click a button' - that button is already there, in a sense, in the generic feature set.....