What is wrong with this script?

[bowes884]

Option Compare Database

'------------------------------------------------------------
' Pipeline___Trades_to_Access
'
'------------------------------------------------------------
Function Pipeline___Trades_to_Access()
On Error GoTo Pipeline___Trades_to_Access_Err

Dim sNow As String

sNow = Format(Now(), "mmddyyyy-hhmmss")

DoCmd.TransferSpreadsheet acImport, 8, "Pipeline", "L:\pipeline.xls", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "Trades", "L:\trades.xls", True, ""

Pipeline_& sNow___Trades_& sNow_to_Access_Exit:
Exit Function

End Function

 

[Mile-O]

1. The function doesn't return any value so why is it a function?
2. What's with all these excessive underscores?
3. You need to use n for minutes as m is already used by months;
4. You have an On Error statement directing to a label that does not exist;
5. If you don't need an argument you can ignoare it this you can drop the , "" from the end of the Import lines;
6. You are trying to use a variable in the name of a label.

 

[bowes884]

Most of it is from a macro I created and converted to VBA. What I am trying to do with it is have Access import a spreedsheet and also tag the name with the date. Any tips?

 

[ghudson]

Most of it is from a macro I created and converted to VBA. What I am trying to do with it is have Access import a spreedsheet and also tag the name with the date. Any tips?

What is wrong with this script?

I have never heard of Visual Basic being called a script.

Your code and error handler is wrong. Converting a macro to VBA will not produce what you have posted.

As I stated in your similar posting at your other thread in the Macros section "What is wrong with this script?"... I question the logic to create a table with a date time stamp in the table name. That goes against "normalization". Search the forum or google for the keyword "normalization" and learn how to design and create a db that incorporates the logic of normalization.

Again, I disagree with your needs for this but here is how your code should look to import a spreadsheet, name the table with a date-time stamp...

Public Sub ImportSpreadsheet()
On Error GoTo ImportSpreadsheet_Err
    
    Dim sNow As String
    sNow = Format(Now(), "mmddyyyy-hhmmss")
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Pipeline_" & sNow, "L:\pipeline.xls", True
    
ImportSpreadsheet_Exit:
    Exit Sub
        
ImportSpreadsheet_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume ImportSpreadsheet_Exit
    
End Sub

 

[bowes884]

thank you. I am very new at this stuff as you could already tell. I'm used to writting html somewhat. There my teacher I had in college refered to the code as script. Thats why I refer to it as that.

Bowes

 

[Bat17]

Whilst I agree that it is probably the wrong thing to do, creating lots of tables, you will be better to use the format of yyyymmdd-hhmmss rather than mmddyyyy-hhmmss. at least then you will be able to sort them by table name into a sensible order.

Better would be to have just one table and append the data to that whilsts adding a date/time stamp to an extra column.

Peter

 

[Mile-O]

I'm used to writting html somewhat. There my teacher I had in college refered to the code as script. Thats why I refer to it as that.

You should go back to your teacher and correct them on that too. HTML is markup and not script.