Access Upload Feature on Form

[wchitamb]

I am trying to have a upload feature that allows the user pick a file (excel) from any location/ folder on their computer and the database will record the file path and store in a variables, rename the file (eg TestFile.xlsx- a generic name that i will use on the import) then move the file for a different location say D/Files.

The next step will be importing the xlsx into the access database & rename the file (using the 1st two cells) to A2 & B2 + date

1st code

Option Compare Database

Sub filetest()

Dim fDialog As Office.FileDialog
Dim xDirPath, xNewFileName, xNewPath, xDT, xVend, xDocID, xOldFileName

Dim varFile As Variant

' Clear listbox contents.
'Me.FileList.RowSource = ""

' Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

xDT = Mid(Date, 1, 2) & Mid(Date, 4, 2) & Mid(Date, 7, 4)
xVen = "Smith"
xDocID = "Doc1234"
xCount = 1

With fDialog

' Allow user to make multiple selections in dialog box
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Please select one or more files"

' Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "Excel projects", "*.xlsx"
.Filters.Add "All Files", "*.*"

' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then

'Loop through each file selected and add it to our list box.
For Each varFile In .SelectedItems

xDirPath = Left(varFile, InStrRev(varFile, "\")) ' File Path
'MsgBox (varFile)
'MsgBox (xDirPath)
'MsgBox (Dir(varFile)) ' File name
' copy file to new location and new name

' varables needed but maybe before loop maybe not
xOldFileName = Dir(varFile)
xNewFileName = "Articles - " & xVen & " - " & xDocID & " - " & xDT & xCount & ".xls"
xNewPath = "D:\"

FileCopy varFile, xNewPath & xNewFileName

Next

Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With

End Sub
-----------------------------------------------------------------------

2nd Code - To Import Excel to Access (Table)

Private Sub Command9_Click()
Dim db As DAO.Database
Dim strUpdate As String
Dim filepath As String
Set db = CurrentDb
filepath = "D:\Old\Test\TestFile.xlsx"
'Clear table before Import
DoCmd.RunSQL "DELETE TempFromExcel.*" & vbCrLf & _
"FROM TempFromExcel;"

DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", filepath, True
DoCmd.OpenQuery "QryAppendToTblArticle", acViewNormal
MsgBox ("New Data Imported")
Call Move_File
Kill filepath
End Sub

 

[pbaldy]

Unless I'm blind, you haven't actually asked a question. Are you getting an error? If so, what is it? Is the code doing something it shouldn't? Not doing something it should?

 

[wchitamb]

Thanks i am missing the bit where i want to rename the file using the excel value in cells A1 & B1