Import Excel spreadsheet.

  • Thread starter Thread starter Stockwell43
  • Start date Start date
S

Stockwell43

Hello,

I am hoping to get some help with this as I am not sure how to do this.

I am using Albert Kallal's Word Merge Database and I am using Access 2003. I
have managed to use my spreadsheet "PaidLoans" and it works perfectly if I
import the spreadsheet myself. What I would like to know is:

1. from the form on the database, can the user click a button to
automatically import the spreadsheet.

2. The spreadsheet name is dated everyday so today will be PaidLoans6/09/08
and tomorrow will be PaidLoans6/10/06. Can I get it to download automatically
without changing the date every day?

3. When the new spreadsheet for today is imported, will it automatically
delete or override Friday's?

Any help would be most appreciated and if I left something out or was not
clear on explaining something please let me know as I would really like to
know how to do this.

Thank you all in advance!!!!
 
Assuming the spreadsheet will be in the same folder and only the name will be
different, this is not difficult to do. You can create a string varialbe to
use in the TransferSpreadsheet method as the file name. Also, you can use
the DeleteObject method to delete the reference to yesterday's spreadsheet.
It does not delete the
file, it only deletes the link to the fiile

Const conLnkedTable As String = "YourLinkedTableName"
Dim strFileName As String

'Check to see if yesterday's file is still linked and delete it if it does

If TableExists(conLinkedTable) Then
Docmd.DeleteObject acTable, conLinkedTable
End If

'Link Today's File

strFileName = "C:\MyfilePath\Spreadsheets\PaidLoans\" & _
Format(Date, ShortDate) & ".xls"

DoCmd.TransferSpreadsheet acLink, , conLinkedTable, _
strFileName, True

**************
Here is the function to see if the table exists.

Public Function TableExists(strTableName As String) As Boolean
Dim tdfs As TableDefs
Dim tdf As TableDef

TableExists = False
Set tdfs = CurrentDb.TableDefs
For Each tdf In tdfs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf
Set tdfs = Nothing

End Function
 
Hi Klatuu, thank you for responding.

Ok, now I have never attempted anything like this so please bare with me as
I may sound a bit dense about this.

The spreadsheet will always be in the same folder. for testing purposes I
currently have the database and spreadsheet here:

H:\Test Folder\Mail Merge Project

The questions I have is where does all this code go? Do I create a module or
does it go on the form in a button?

I apologize but I would like really like to learn how to do this to expand
the capabilities of my databases.

Thanks!!!
 
Not a problem, happy to help.
The funtion I posted (TableExists) should go in a standard module. That way
it can be called from anywhere in your application. You may find it useful
for other things.
The other code that does the transfer should be in the Click event of a
command button. If you are not familiar with how to create an event
procedure, let me know and I can walk you through it.
 
Thank you Klatuu, I really appreciate the help.

Ok, I placed the function in a module and named it TableExists.

I place a button on the form and in the OnClick event I selected Event
Procedure open the code window and pasted the rest of the coed chnaging
"YourLinkedTableName" to "PaidLoans"

It stops on this part pf the code where the (conLinkedTable) is. Am I doing
something wrong? I'm thinking when I click the button, it will replace the
new spreadsheet with the old information so I can then click on the mail
merge button to merge the new information. Am I correct in thinking this or
is it suppose to work another way? In otherwords, should the rest of the code
go into the mail merge button instead?

If TableExists(conLinkedTable) Then
DoCmd.DeleteObject acTable, conLinkedTable
End If

when clicked the button I got an Compile Error: Variable not defined
 
Change the name of the module. A module cannot have the sam name as any
function or sub it it. I always prefix the name of my modules with mod. For
example, that code came from a collection of procedures named modUtilities.
That may correct the variable name not defined problem. If not, post back.
 
I changed the name to modUtilities and then tried paidletterupdate and
everytime I click on the button it stops on the same part with the same
error. If I don't click the button and manually import the spreadsheet into
the database and remove the date so the name is just PaidLoans and open the
merge form it updates fine. but that's a lot of steps I was hoping to
eliminate. Is there something I need to change?
 
Sorry, there is a misspelling here:
Const conLnkedTable As String = "YourLinkedTableName"

Should be:
Const conLinkedTable As String = "YourLinkedTableName"
 
Oooo my bad, I should have caught that thanks. It's now getting stuck on
ShortDate. I do need to mention the date shows as 060908 or today 061008 no /
or - between the numbers. However, I tried to take the date out and leave it
as paidloans.xls and it still got stuck in the same place with the same error
as before. Sorry about all this Klatuu but thank you for hanging with me.
 
Not sure what you mean by same error, but if you don't want the delimiters,
change this line:
strFileName = "C:\MyfilePath\Spreadsheets\PaidLoans\" & _
Format(Date, ShortDate) & ".xls"

To:
strFileName = "C:\MyfilePath\Spreadsheets\PaidLoans\" & _
Format(Date, "mmddyy") & ".xls"

strFileName is dimmed in the procedure, so it shouldn't have a problem with
that.
 
It works! you're the greatest! Thank you for staying with me on this I really
appreciate it! Have a great day Klatuu!

By same error I meant it was the same Compile Error as before.
 
Back
Top