Importing Excel Spreadsheet based on date in Form

D

Diane

I am trying to import a particular Excel Spreadsheet based on a date setup on
a form. I am trying to use an if-then-else statement and it continues to
bomb. If I just use on statement it works but if I try to add an Else
statment it doesn't. Any suggestions.
 
K

Klatuu

Post the code you are using including the code that fails.
Include the error number and point out the line where it fails.
 
D

Diane

This is my code.... I am getting an error of "Else without If"
Private Sub datemo_AfterUpdate()
If [datemo] = #3/1/2008# Then DoCmd.TransferSpreadsheet ,
acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\03 March 2008 Vendor Billingtest.xls", True,
"miscbilling"

ElseIf [datemo] = #4/1/2008# Then DoCmd.TransferSpreadsheet ,
acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\04 April 2008 Vendor Billingtest.xls", True,
"miscbilling"

ElseIf [datemo] = #5/1/2008# Then DoCmd.TransferSpreadsheet ,
acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\05 May 2008 Vendor Billingtest.xls", True, "miscbilling"
 
F

fredg

I am trying to import a particular Excel Spreadsheet based on a date setup on
a form. I am trying to use an if-then-else statement and it continues to
bomb. If I just use on statement it works but if I try to add an Else
statment it doesn't. Any suggestions.
Click to expand...

My suggestion would be that you post your code, and also tell us what
you mean by "date setup on a form".
None of us are mind readers on Tuesdays. ;-)
 
D

Douglas J. Steele

The code after Then must be on a separate line in order to use ElseIf or
Else.

If [datemo] = #3/1/2008# Then
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\03 March 2008 Vendor Billingtest.xls", True,
"miscbilling"
ElseIf [datemo] = #4/1/2008# Then
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\04 April 2008 Vendor Billingtest.xls", True,
"miscbilling"
ElseIf [datemo] = #5/1/2008# Then
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\05 May 2008 Vendor Billingtest.xls", True,
"miscbilling"
End If

Of course, you could probably simplify that to

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\" & Format([datemo], "mm mmmm yyyy") & " Vendor
Billingtest.xls", _
True, "miscbilling"



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Diane said:
This is my code.... I am getting an error of "Else without If"
Private Sub datemo_AfterUpdate()
If [datemo] = #3/1/2008# Then DoCmd.TransferSpreadsheet ,
acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\03 March 2008 Vendor Billingtest.xls", True,
"miscbilling"

ElseIf [datemo] = #4/1/2008# Then DoCmd.TransferSpreadsheet ,
acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\04 April 2008 Vendor Billingtest.xls", True,
"miscbilling"

ElseIf [datemo] = #5/1/2008# Then DoCmd.TransferSpreadsheet ,
acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\05 May 2008 Vendor Billingtest.xls", True,
"miscbilling"


Klatuu said:
Post the code you are using including the code that fails.
Include the error number and point out the line where it fails.
Click to expand...
Click to expand...
 
K

Klatuu

Doug answered the basic question, but you have a design issue here. I don't
think you really want to have to change your code periodically to do this.
There is a better way.

Private Sub datemo_AfterUpdate()
Dim strFilePath As String

strFilePath = "V:\Diane for Access\" & Format([datemo],"mm mmmm yyyy") &
" Vendor Billingtest.xls"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "miscbilling", _
strFilePath, True, "miscbilling"


That is all you need. The Format Function will return the month and year
like you want it.

Also, I wouldn't recommend you do this in the After Update event of the text
box. If you enter an invalid date or a date you didn't intend to enter, you
will get an error or create a file you didn't want to. I would suggest you
move the code to a command button. And use the Before Update event of the
text box to ensure it is a valid date. Then the user can take a second to be
sure it is the date she wants before clicking the button:

Private Sub datemo_BeforeUpdate(Cancel As Integer)

If Not IsDate(Me.datemo) Then
MsgBox "Invalid Date Entered"
Me.datemo.Undo
Cancel = True
End IF

End Sub
--
Dave Hargis, Microsoft Access MVP

#4/1/2008#

Diane said:
This is my code.... I am getting an error of "Else without If"
Private Sub datemo_AfterUpdate()
If [datemo] = #3/1/2008# Then DoCmd.TransferSpreadsheet ,
acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\03 March 2008 Vendor Billingtest.xls", True,
"miscbilling"

ElseIf [datemo] = #4/1/2008# Then DoCmd.TransferSpreadsheet ,
acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\04 April 2008 Vendor Billingtest.xls", True,
"miscbilling"

ElseIf [datemo] = #5/1/2008# Then DoCmd.TransferSpreadsheet ,
acSpreadsheetTypeExcel9, "miscbilling", _
"V:\Diane for Access\05 May 2008 Vendor Billingtest.xls", True, "miscbilling"


Klatuu said:
Post the code you are using including the code that fails.
Include the error number and point out the line where it fails.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Klatuu said:
Doug answered the basic question, but you have a design issue here. I
don't
think you really want to have to change your code periodically to do this.
There is a better way.

Private Sub datemo_AfterUpdate()
Dim strFilePath As String

strFilePath = "V:\Diane for Access\" & Format([datemo],"mm mmmm yyyy")
&
" Vendor Billingtest.xls"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "miscbilling", _
strFilePath, True, "miscbilling"
Click to expand...


And how is that different than my second solution? (Yeah, there was a typo:
I meant to say "you should probably simplify that to", rather than "you
could...")
 
K

Klatuu

Sorry, Doug. I didn't scroll down to see that.
--
Dave Hargis, Microsoft Access MVP


Douglas J. Steele said:
Klatuu said:
Doug answered the basic question, but you have a design issue here. I
don't
think you really want to have to change your code periodically to do this.
There is a better way.

Private Sub datemo_AfterUpdate()
Dim strFilePath As String

strFilePath = "V:\Diane for Access\" & Format([datemo],"mm mmmm yyyy")
&
" Vendor Billingtest.xls"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "miscbilling", _
strFilePath, True, "miscbilling"
Click to expand...


And how is that different than my second solution? (Yeah, there was a typo:
I meant to say "you should probably simplify that to", rather than "you
could...")
Click to expand...
 
Top