execution of this application has stopped due to a run-time error

[Gasman]

As I mentioned before, we are now using runtime and also, we have moved to google suite and removed the full version of office
How would I now export to google sheets instead of excel?

Not sure they thought of everything when they made this decision

Below was the previous code

What would we do without search engines these days?

Best I could find. https://stackoverflow.com/questions/15790675/exporting-from-msaccess-to-google-spreadsheet

From https://duckduckgo.com/?t=palemoon&q=ccess+export+to+google+sheets&ia=web

 

[bastanu]

Try googling "MS Access + Google Sheets", there are many posts regarding this. It will not be easy, I suggest you simply do your exports as CSVs and let users choose how they want to view them (Excel, G sheets, etc.)

Connecting to Google Sheets

I am trying to connect to a google sheet document. My intention is to copy the information to a spreadsheet, then import it into MS Access, but I want all the code to be done from a MS Access user interface. I found the thread listed at the bottom of this post and attempted to replicate it...

www.access-programmers.co.uk

Cheers,

 

[Gismo]

How do I transfer text to acExportDelim without saving the file to hard drive?

What I would like to do is export a query to a csv file and have the user save the file if needed or save in other format they wish to have

I do not want VBA to export to csv and save the file, just to open with data view

Below code has the following message

Private Sub ExcelOrderLineItems_Click()
On Error GoTo ExcelOrderLineItems_Click_Err

DoCmd.TransferText acExportDelim, , "CS Order line Items Qry", "CS Order Line Items.csv"
'DoCmd.TransferText acExportDelim, "Specification Name", "CS Order line Items Qry", "CS Order Line Items.csv"

'DoCmd.OutputTo acOutputQuery, "CS Order line Items Qry", "Excel Workbook(*.xlsx)", "CS Orders - Line Items" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xlsx", True, "", , acExportQualityPrint
ExcelOrderLineItems_Click_Exit:

Exit Sub

ExcelOrderLineItems_Click_Err:
MsgBox Error$
Resume ExcelOrderLineItems_Click_Exit
End Sub

 

[gemma-the-husky]

you would have to open and display the query, and then ask the user if he wants to save it.

alternatively you could in code open a blank spreadsheet, load it with the data, and then let the use discard it or save it., but that's quite a bt harder.

another alternative is to save it originally with a standard name "provisional.xslx", in the same folder as the database (currentproject.path) and then let the user save it with a real name in whatever location he needs. Each time you do this you overwrite the last "provisional.xlsx", so there's only ever 1 draft. Personally, I would go this way - I can't see the issue with pre-saving the spreadsheet, if the user is allowed to save the spreadsheet anyway. It just makes it easier for the use to edit the spreadsheet if he needs to.

 

[Gismo]

pre saving I need to provide a location, correct?

either way, I am not sure how to do this as the message I receive does not explain what the problem is

Private Sub ExcelOrderLineItems_Click()
On Error GoTo ExcelOrderLineItems_Click_Err

'DoCmd.TransferText acExportDelim, , "CS Order line Items Qry", "CS Order Line Items.csv", ""
'DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", "CS Order Line Items.csv", "%USERPROFILE%\Desktop\"
DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", "CS Order Line Items.csv", "C:\Desktop\"
'DoCmd.OutputTo acOutputQuery, "CS Order line Items Qry", "Excel Workbook(*.xlsx)", "CS Orders - Line Items" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xlsx", True, "", , acExportQualityPrint
ExcelOrderLineItems_Click_Exit:

Exit Sub

ExcelOrderLineItems_Click_Err:
MsgBox Error$
Resume ExcelOrderLineItems_Click_Exit
End Sub

 

[Gasman]

Really, it seems self explanatory to me?

Have you looked at the syntax for the command?

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transfertext

You need the full path name in ONE argument?

 

[gemma-the-husky]

- note that the database path does not have a trailing backslash, so for a csv you need a path of, eg

currentproject.path & "\" & "draft.csv"

DoCmd.TransferText acExportDelim, , "CS Order line Items Qry", "CS Order Line Items.csv", ""

arguments are
1 . export type
2. specification, if you have one, or just leave blank
3. query name (table name)
4. file name - but you need a fully specified path, not just the file name
5. field names is true or false - whether you want column headers in the output file

so in your case
your item 4 is not a useable path
your item 5 needs to be true (-1) or false (0), not a string. If you omit it it uses the default - offhand I am not sure whether the default is true or false

 

[Gismo]

Hi

Really, it seems self explanatory to me?

Have you looked at the syntax for the command?

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transfertext

You need the full path name in ONE argument?

,

Yes I have but still not sure why there is a wrong data type, I am using CSV, is that not a valid variable

I tried a few different formats but still get the same error

 

[Gismo]

- note that the database path does not have a trailing backslash, so for a csv you need a path of, eg

currentproject.path & "\" & "draft.csv"

DoCmd.TransferText acExportDelim, , "CS Order line Items Qry", "CS Order Line Items.csv", ""

arguments are
1 . export type
2. specification, if you have one, or just leave blank
3. query name (table name)
4. file name - but you need a fully specified path, not just the file name
5. field names is true or false - whether you want column headers in the output file

so in your case
your item 4 is not a useable path
your item 5 needs to be true (-1) or false (0), not a string. If you omit it it uses the default - offhand I am not sure whether the default is true or false

Still getting wrong data type message

Dim strExportFile As String
strExportFile = CurrentProject.Path & "\" & "CS Order Line Items.csv"

DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", "CS Order Line Items.csv", ""

 

[Gasman]

Type the command using Intellisense.
That should show you what goes where?

You create strExportFile, which holds the FULL path to the output file (which is correct syntax\) yet do not use it?

Look at the link that I posted and compare that to what you have entered for all the parameters.
@gemma-the-husky has pointed out in detail in post #47 the parameters, what they are for, and their position.

 

[gemma-the-husky]

this

DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", "CS Order Line Items.csv", "C:\Desktop\"

is this your current version?

then it should be this

DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", "C:\Desktop\CS Order Line Items.csv", true (or false)

except that I don't believe "C:\Desktop\CS Order Line Items.csv" will be a valid path
the last true or false determines whether you want the column headers including in the output file

 

[Gismo]

this

DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", "CS Order Line Items.csv", "C:\Desktop\"

is this your current version?

then it should be this

DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", "C:\Desktop\CS Order Line Items.csv", true (or false)

except that I don't believe "C:\Desktop\CS Order Line Items.csv" will be a valid path
the last true or false determines whether you want the column headers including in the output file

I dont necessarily want to create a new folder on the desktop.

The file being exported can be saved directly on the Desktop

When I remove the Folder, I get a read only message
When I specify the folder, I get a message that the folder does not exist

Dim strExportFile As String

strExportFile = Environ("USERPROFILE") & "\Desktop\" & "CS Order Line Items.csv"

DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", "strExportFile", True

 

[Minty]

PMFJI, I'm struggling to see why you don't use the path to the current database you are in, as you know that exists without any additional code?

All you need to do is check if the file already exists and delete it if it does.

And in your code "strExportFile" should be strExportFile without the quotes as it's a variable holding the path string.

 

[Gismo]

PMFJI, I'm struggling to see why you don't use the path to the current database you are in, as you know that exists without any additional code?

All you need to do is check if the file already exists and delete it if it does.

And in your code "strExportFile" should be strExportFile without the quotes as it's a variable holding the path string.

Any location I use gives me the same error

 

[Minty]

What is your current code ? Based on what I see above try this;

Dim strExportFile As String

strExportFile = CurrentProject.Path & "\CS Order Line Items.csv"

DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", strExportFile, True

 

[Gismo]

What is your current code ? Based on what I see above try this;

Dim strExportFile As String

strExportFile = CurrentProject.Path & "\CS Order Line Items.csv"

DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", strExportFile, True

I tried Currentproject.path as well, still same error message

Current code is

Dim strExportFile As String

strExportFile = Environ("USERPROFILE") & "\Desktop\" & "CS Order Line Items.csv"

DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", "strExportFile", True

Both gives same output message

 

[Minty]

Ah - that's because you don't have a saved export specification, it's nothing to do with the location.

How to Create an Export Specification | BTAB Development

btabdevelopment.com

 

[Gasman]

I tried Currentproject.path as well, still same error message

Current code is


Both gives same output message
View attachment 91453

Loving God!!!, you still have quotes around the strExportFile variable???? as well

 

[Gismo]

Loving God!!!, you still have quotes around the strExportFile variable???? as well

Sorry, I did remove them, I just copied from a previous post

 

[vhung]

it was below code
DoCmd.NavigateTo ("acNavigationCategoryObjectType")

This sample is using loop...

If Me.listObjects.Column(0) = "table" Then
DoCmd.NavigateTo "acnavigationcategoryobjecttype", "acnavigationgrouptables"
End If

Also, could be OnCurrent
DoCmd.NavigateTo "acnavigationcategoryobjecttype", "acnavigationgrouptables"