Hi All,

I have a linked table which is a CSV file called week 14
this csv file is deleted and added with a new week, week 15
Can someone help me with the VB code in how this linked table can be updated with the new CSV name.

P.s. the week 14 variable is called StrWeek

If its a CSV file, I would pull the entire contents in each time you open the database, and not link it. THis will make processing much faster.

Also, I would have a folder structure like this:

X:\Data
X:\Data\OldData

I would put the CSV file in the Data folder each week with the same name, and move the old copy to the OldData folder each week and rename it to reflect the week.

A better solution would be to pull it into SQL and yank the data from there each time you need it, rather than link. This will keep track of all the weeks if you add a date field.

As a last resort, and getting to what you want to do, you can create code based on the file name and the date. The csv filename would have to have the date in it. Do you have more information on file names?

HTH

mmcdonal

Hi mmcdonal,

This code is from Impromptu and opens saves the file as csv and e-mails thrue notes the user. The rest (which hasn't been completed yet hopefully with your help, is to link that specific CSV file with week number into access. The next time the report is run with another week number everything should repeat given the fact that the linked table has changed week number. I agree with old and new files but I do prefer to keep the week number during the current active week.

The filename info is shown in the code.

the code:

Declare Sub SendNotesMail(Subject , Attachment , Recip , BodyText , SaveIt)

Sub Main()

Dim objImpApp As Object
Dim objImpRep As Object
Dim objExcelApp As Object
Dim path As String
Dim strpromptvalue As String
dim destpath as string
dim prompt as string

' ********** Create Impromptu application
Set objImpApp = CreateObject("CognosImpromptu.Application")

' ********** Make impromptu visible and login onto catalog and database
objImpApp.Visible 0
objImpApp.OpenCatalog "D:\Data\Application-Data\EDW_OPS\Catalog\OPS Catalog.cat""


' ********** input filter
Dim msgtext
msgtext="Please enter the reporting year and week YYYYWW"
prompt=InputBox$(msgtext)

Dim Fileloc(1) As String
dim x as integer

const reportname = "Damaged Sectors"

'declaring an array of filelocations

' ********** Location of source file of report

FileLoc(0) = "M:\OPS_Processes\Reports\Damaged Sectors\pvb.imr"


Dim FileDest(1) as String

' ********** Location of destination of report output

Filedest(0) = "M:\OPS_Processes\Reports\Damaged Sectors\pvb"


x=0

do

filenaam = fileloc(x)
filedestnaam = filedest(x)

Set objImpRep = objImpApp.OpenReport(filenaam, prompt)
objImpRep.export filedestnaam & " " & prompt & ".csv", "x_ascii.flt"

x=x+1
loop until x=1

objImpRep.closereport
objImpApp.Quit

Set objImpRep = Nothing
Set objImpApp = Nothing

Dim subject as string
Dim message as string



'**************************************
'********** Access section *********
'** create new linked table with the prompt variable
'**





' ********** MESSAGE

message = "Dear user," & chr(10) & chr(10) & _
"Please be advised that report " & reportname & " has been created and saved on: " & filedestnaam & " " & prompt & ".csv" & chr(10) & _
chr(10) & "Kind regards," & chr(10) & chr(10) & "Paul van Baarsen"


' ******* e-mail detail's

dim recip(2) as string
recip (0) = "[email protected]"
'recip (1) = "[email protected]"

' ********** e-mail loop

n=0
do
Call SendNotesMail( "Report " & reportname & " week " & prompt & " is ready." , "C:\Temp\NotesSendMail.vbs" , recip(n), message , True)
n=n+1
loop until n=1


End Sub



'*********************************************
'******** MAIL SECTION **********
'*********************************************
'


Sub SendNotesMail(Subject , Attachment , Recip , BodyText , SaveIt )
'Set up the objects required for Automation into lotus notes
Dim Maildb as object 'The mail database
Dim UserName as string 'The current users notes name
Dim MailDbName as string 'THe current users notes mail database name
Dim MailDoc as object 'The mail document itself
Dim AttachME as object 'The attachment richtextfile object
Dim Session as object 'The notes session
Dim EmbedObj as object 'The embedded object (Attachment)
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
'Get the sessions username and then calculate the mail file name
'You may or may not need this as for MailDBname with some systems you
'can pass an empty string
UserName = Session.UserName
MailDbName = Left(UserName, 1) & Right(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
'Open the mail database in notes
Set Maildb = Session.GETDATABASE("", MailDbName)
'Set Maildb = Session.GETDATABASE("", "mail.box")
If Maildb.ISOPEN = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = Recip
MailDoc.Subject = Subject
MailDoc.Body = BodyText
MailDoc.SAVEMESSAGEONSEND = SaveIt
'Set up the embedded object and attachment and attach it
'If Attachment <> "" Then
'Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
'Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
'MailDoc.CREATERICHTEXTITEM ("Attachment")
'End If
'Send the document
MailDoc.SEND 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub

If your naming convention is something like "Week14.csv", "Week15.csv" etc, then you can do the following:

Automate moving the file from Data to OldData, and then dropping in the new file to Data.

Then have your script check for the existence of a file with Left(, 4) and look for the name "Week" and just load that. You will have to use a general folder scan instead of going right for the actual filename. Then pop the name of the file found into a variable and use that filename for linking.

Does that help?


mmcdonal

I think thats the best option to use the "working" file without the week number and copy the file with the weeknumber to anoter location.
But do you know the code to automatic link a csv file without the change of week numbers?
I know that this can be done with the wizard but I would like to understand the code.

rgrds,

Paul.

As I mentioned earlier, I think the best thing to do would be to import the data to a table, after running a delete query on it to get the old data out. I would automate this so it happened when the user opened the app. All users will have to have the same drive mappings.

Something like:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETEOldData"
DoCmd.SetWarnings True
Create Scripting.FileSystemObject
Open CSV file for reading
Create connection to current
Create recordset on empty table
Parse through one line of the file at a time (ReadLine)
Do Until EOF
Array = Split(strLine, ",")
rs.AddNew
   ("FirstField") = Array(0)
   ("SecondField") = Array(1)
   ...
rs.Update
Loop

That sort of thing.

Does that help?





mmcdonal