Ask Your Question

Basic Macro to test if open document is a spreadsheet

asked 2017-12-06 14:24:16 +0200

dickm gravatar image

I created a macro to autofit a spreadsheet that is imported from a CVS file. But need the syntax to do the following logic

If Open document is a spreadsheet then do code else do nothing or If open document is a CSV format then do code else do nothing


edit retag flag offensive close merge delete


Does "is a spreadsheet" mean that the file has been saved as .ods or .xlsx or similar, and not .csv?

Jim K gravatar imageJim K ( 2017-12-06 21:33:25 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-12-06 21:47:28 +0200

Jim K gravatar image

updated 2017-12-06 21:48:05 +0200

A CSV file becomes a spreadsheet when it is opened in Calc. It sounds like you are asking about the file format instead.

Sub WhatFileType
    sURL = ThisComponent.getURL()
    strings = Split(sURL, ".")
    extension = strings(UBound(strings))
    MsgBox "File Type is " & extension
End Sub
edit flag offensive delete link more

answered 2017-12-06 15:05:13 +0200

librebel gravatar image

updated 2017-12-07 07:57:10 +0200

pierre-yves samyn gravatar image

Hello @dickm,

Please try if the following:

Function dummy()
    Dim oDoc As Object  : oDoc = ThisComponent      REM The current document.
    If oDoc.supportsService( "" ) Then
        REM it is a Spreadsheet ( also includes CSV opened in Calc ):
        msgbox "'do code"
        'do nothing'
    End If
End Function
edit flag offensive delete link more


Hi @librebel - I just added the quote at the end of the line comment (obviously useless in basic but necessary here)

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-12-07 07:59:24 +0200 )edit

Merci @Pierre-Yves, i have to remember to do that from now on..

librebel gravatar imagelibrebel ( 2017-12-07 20:51:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-06 14:24:16 +0200

Seen: 146 times

Last updated: Dec 07 '17