Importing Excel Spreadsheet (same data) from different days

[Newaccessuser]

Hello - i am new to access and i need some help. I am working with a custom UI tool that creates an Excel extract upon request. I run this excel report daily to perform calculations on changes. I have past excel snapshots on my PC.

I want to use Access to store the daily data, that way i can run queries to show trends/ charts over time and changes of the data from date 1 to date 2 etc.

Can anyone help me import the same excel spreadsheet (from past archives) to build my database? The spreadsheet does not contain dates in the records. The only way to determine date is the naming convention of the saved Excel spreadsheet.

I have tried to append to my table (the first import of data) but i keep getting errors on key violations. if anyone can help me, i would really appreciate it!

 

[ranman256]

Here's code to import ALL excel files from a folder to a table.
1. link an excel file that will be a permanent import file name. "\\server\FOLDER\ImportFile.xls" (replace w your file)
2. build a query "qaImportXLfile" that will import the linked file into the target table.
3. run the sub below
It will scan the entire folder, copy the next file to the standard import file: IMPORTFILE.XLS. then run the append query.
If your dates are only in the filename, it would be easier to open the excel file, put the date on every record and save before begining

the import.

Public Sub ScanAllFilesInDir(ByVal pvDir)
Dim vFil, vTargT
Dim i As Integer
Dim fso
Dim oFolder, oFile
Dim vSrc
const kTARG = "\\server\FOLDER\ImportFile.xls"


On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"

sTbl = "xlFile"
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)

For Each oFile In oFolder.Files
    vFil = pvDir & oFile.Name
    If InStr(vFil, ".xls") > 0 Then      'ONLY DO xl FILES
           vSrc = pvDir & oFile.Name 
           
           filecopy vSrc, kTARG 
           docmd.RunQuery "qaImportXLfile"
    End If
Next

Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
MsgBox "Done"
Exit Sub

errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
Exit Sub
Resume
End Sub

Hello - i am new to access and i need some help. I am working with a custom UI tool that creates an Excel extract upon request. I run this excel report daily to perform calculations on changes. I have past excel snapshots on my PC.

I want to use Access to store the daily data, that way i can run queries to show trends/ charts over time and changes of the data from date 1 to date 2 etc.

Can anyone help me import the same excel spreadsheet (from past archives) to build my database? The spreadsheet does not contain dates in the records. The only way to determine date is the naming convention of the saved Excel spreadsheet.

I have tried to append to my table (the first import of data) but i keep getting errors on key violations. if anyone can help me, i would really appreciate it!

 

[Newaccessuser]

do you have instructions on where to put the code...?

Hello - i am new to access and i need some help. I am working with a custom UI tool that creates an Excel extract upon request. I run this excel report daily to perform calculations on changes. I have past excel snapshots on my PC.

I want to use Access to store the daily data, that way i can run queries to show trends/ charts over time and changes of the data from date 1 to date 2 etc.

Can anyone help me import the same excel spreadsheet (from past archives) to build my database? The spreadsheet does not contain dates in the records. The only way to determine date is the naming convention of the saved Excel spreadsheet.

I have tried to append to my table (the first import of data) but i keep getting errors on key violations. if anyone can help me, i would really appreciate it!

 

[ranman256]

Click modules. Add new module, paste, save.

 

[Newaccessuser]

Here is what i did - i wish i could post screen shots....
I saw the Module icon in the Access toolbar. I clicked it.
It launched a separate window for Microsoft Visual Basic for applications.
In the module window, i named the module qaImportXLfil module.
I cut and pasted your code.
I updated the Server\folder name in the code to my server\folder name where all the files reside.
in that same location, i created an IMPORTFILE.xls (blank Excel doc).
i then selected the icon to Run sub/user form.

another window appeared to name the macro.
I named the macro ImportFile.

I ran the macro. nothing happened.
I then stepped through the macro, and there was a highlight on the below, indicating an error:

errImp:

Sub ImportFile()

 

[ranman256]

oops, you cant run SUB from a macro.
change it to FUNCTION, then you can via runcode IMPORTFILE( "\\server\folder\")