execution of this application has stopped due to a run-time error (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 11:37
Joined
Sep 21, 2011
Messages
14,051
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

AWF VIP
Local time
Today, 04:37
Joined
Apr 13, 2010
Messages
1,401
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.)
Cheers,
 

Gismo

Registered User.
Local time
Today, 13:37
Joined
Jun 12, 2017
Messages
1,298
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

1620377796111.png
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Sep 12, 2006
Messages
15,614
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

Registered User.
Local time
Today, 13:37
Joined
Jun 12, 2017
Messages
1,298
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


1620386139613.png
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Sep 12, 2006
Messages
15,614
- 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
 
Last edited:

Gismo

Registered User.
Local time
Today, 13:37
Joined
Jun 12, 2017
Messages
1,298

Gismo

Registered User.
Local time
Today, 13:37
Joined
Jun 12, 2017
Messages
1,298
- 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

Enthusiastic Amateur
Local time
Today, 11:37
Joined
Sep 21, 2011
Messages
14,051
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? :unsure:

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

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Sep 12, 2006
Messages
15,614
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

Registered User.
Local time
Today, 13:37
Joined
Jun 12, 2017
Messages
1,298
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

AWF VIP
Local time
Today, 11:37
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 13:37
Joined
Jun 12, 2017
Messages
1,298
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

AWF VIP
Local time
Today, 11:37
Joined
Jul 26, 2013
Messages
10,355
What is your current code ? Based on what I see above try this;

Code:
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

Registered User.
Local time
Today, 13:37
Joined
Jun 12, 2017
Messages
1,298
What is your current code ? Based on what I see above try this;

Code:
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
1620650870579.png
 

vhung

Member
Local time
Today, 04:37
Joined
Jul 8, 2020
Messages
235
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"
 

Users who are viewing this thread

Top Bottom