Replies

I would start by just manually pasting the data into an Access table and then writing VBA code in Access which makes a table and populates it from the “pasted” table. Once that works, then it’s a matter of using ‘automation’ (or whatever its called these days) to run Access VBA code from Excel that does the same thing except the new table is populated from the array which you pass to Access. Have you tried something like this yet?

I see under “Access Options” I have the ability to change the Default file format for creating new databases. So, I will change this to “Access 2003”, create a new database and import all the objects from my “Access 2010” database.

I will post again here if this doesn’t work.

For anyone interested, here is the answer.

Working with the website developers, we decided that the website form would publish to a Comma-separated-values (.CSV) text file, which would be sitting at an ftp site. Each day at midnight, a new file (which has the date as part of the filename) is created. Then each day at 2 am, Access uses the Windows API and calls methods in WinInet.dll to go get the file from the previous day and download it to the local harddrive.

I’m still working on this, but the basic ftp download from wihtin Access is working. I’ve attached the code.

Thank you Hans. I’ve now successfully attached the file. Yes I did mean post 553,191 please make it editable and I will attach the code to it as well.

Hi – the attached code will go to an ftp server, look for a file, and download it and parse it into an Access table. It can be called manually by a button click or by a timer.

The main routine is called DownloadCouponRequest which calls Windows API functions in wininet.dll.

p.s. I’m posting this because Frances Donahoe contacted me privately re a thread which now seems to be closed.

Thank you Hans! and goodbye brick wall

Nothing I tried would work! I spent HOURS and it was all because I went

Dim rngRangeToSelect as Range BADBADBAD!

instead of

Dim rngRangeToSelect as Excel.Range

At last I can start making progress. Thank you again.

Thank you Hans, especially for elegant “Set xlBook = xlApp.Workbooks.Open(strFilePath)” . Apologies the duplicate code – i sloppily edited more complicated code trying to illustrate my problem. What I *really* need is an AutoFilter and some column widths formatted, but for the life of me I can’t do the simplest things like assign a range object … will you look at this with your eagle eye? Everything is inside “With xlApp” so why doesn’t it work? I’ve tried lots of variations on the Set statement to set the range variable, but no joy at all.

.
.
With xlApp
.Visible = True
Set xlBook = .Workbooks.Open(strFilePath)
Set xlSheet = xlBook.ActiveSheet
xlSheet.Range(“A1”).Select
‘THE NEXT LINE GIVES “TYPE MISMATCH” AS THO TRYING TO ASSIGN A STRING TO A RANGE OBJECT
Set rngRangeToSelect = ActiveCell.CurrentRegion
End With ‘xlApp
.
.

… and also there’s all of us who read the answers and are silently enlighteded as well! I add my thanks to yours.

Thanks Hans – I’ll re-think how I’m doing this.

Charlotte and Wendell – Thank you so much for this discussion. Most informative.

Thank you Chris – I’ve just spent an hour trying to figure this out.

You’ll also need to create a procedure to change colours for the text of Total Exposure. You’ll need to do this everytime you change either Likelihood or Consequence PLUS everytime you move from one record to another. Since this code will have to go into 3 places, it’s better to create a separate sub routine or function and then call it from the Form’s Current event (which occurs when you move from one record to another), and the AfterUpdate events of Likelihood and Consequence.

You need to co into the Code behind the form and put this:

Private Sub Form_Current()
Call ChangeExposureColor
End Sub

Private Sub Likelihood_AfterUpdate()
Call ChangeExposureColor
End Sub

Private Sub Consequence_AfterUpdate()
Call ChangeExposureColor
End Sub

Sub ChangeExposureColor()
Select Case Me.Exposure
Case 1 To 5
Me.Exposure.ForeColor = 8453888 ‘green
Case 6 To 10
Me.Exposure.ForeColor = 65535 ‘yellow
Case Is > 10
Me.Exposure.ForeColor = 255 ‘red
End Select
End Sub

Hi Scott, First – THANK YOU

I really like the code within your application because you’re using the Windows API directly. Your code is working for me, but now that I’m ready to start work, the specs have changed.

The ISP is now going to deposit a CSV or XML file for me each day at midnight (the file will have the date as part of the filename), so now I have to go get the file off the site instead of reading an Access db on the site. Once I’ve got the file, I’ll save it on a local hard drive, parse it and then update my Access app.

Does the Winsock API have a function like WinInet’s FtpGetFile function? Do you have any suggestions about the best way to grab a file off the site from within Access?
Gwenda

Makes a lot of sense and is fascinating.

Hi Chris – I found this interesting reading: ASP.NET & applicational domains (AppDomain) at http://www.codeproject.com/aspnet/aspwp.asp. I guess if you really wanted to delete this account you’d have to change the Machine.config file too so that ASP.NET doesn’t constantly try to create its worker process.