Export Queries to Excel spreadsheet not working

I am trying to modify an existing form found on the internet which allows the export of queries selected in a listbox into an existing Excel .xlsx spreadsheet. It utilized a popup form which asked for the file pathway. I need it to use a default pathway in a table...

On the form is a listbox, named lst_Export_Queries, with the rowsource as:
SELECT [MsysObjects].[Name], Mid([Name],5) AS QueryName FROM MsysObjects WHERE (((Left([Name],4))="qry_")) ORDER BY [MsysObjects].[Name];

Open in new window

multiselect = True,

and a bound textbox named "txt_Default Path", the field is named "Default_Pathway_File", and is based on t_Defaults (Defaults_ID,PK, and Default_Pathway_File" text)

I would like the code to use whatever is shown in the default file pathway as the area to export the spreadsheets into. This drive pathway exists as does the Excel Form.

The pathway to the Excel file to import into is: "C:\Excel DE to Import 2\Book1.xlsx"
Name of the Excel File is: Book1.xlsx

There is a command button named "Command)" and the code is listed below.

I keep getting a Run-time Error 3027 Cannot Update. Database or Object is Read Only.

When debugging this is highlighted:

DoCmd.TransferSpreadsheet transferType:=acExport, _
								  spreadsheetType:=acSpreadsheetTypeExcel9, _
								  tableName:=Me.lst_Export_Queries.ItemData(varItem), _
								  FileName:=strFile
	Next

Open in new window


And this is the on click event Command Button code:

Option Compare Database
Option Explicit

Private Sub Command0_Click()
  Dim strFile As String
  Dim varItem As Variant
  
	strFile = (Me.lst_Export_Queries.Column(0) & "Export")
	
	If (strFile = vbNullString) Then Exit Sub
	
	For Each varItem In Me.lst_Export_Queries.ItemsSelected
		DoCmd.TransferSpreadsheet transferType:=acExport, _
								  spreadsheetType:=acSpreadsheetTypeExcel9, _
								  tableName:=Me.lst_Export_Queries.ItemData(varItem), _
								  FileName:=strFile
	Next
	
	MsgBox "Process complete.", vbOKOnly, "Export"
	
	End Sub

Open in new window

Bill NelsonITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
For xlsx files, the constant is acSpreadsheetTypeExcel12Xml.

And you strFile doesn't seem to include the filename itself. It must hold the full path: "C:\Excel DE to Import 2\Book1.xlsx"

/gustav
0
Bill NelsonITAuthor Commented:
Since the Master of Databases is in ( luckily for me ! ) may I ask the following:

Do I need the XML after the 12 as you suggested ?

I did just change it to 12 and no errors...

As for the name not being passed, I changed the rowsource of the listbox column 0 to include it but I still get no update occurring to Book1.xlsx

Can I ask you what would be the best way to do this?

I am thinking maybe I could also include a textbox to allow for a unique filename to concatenate into the mix...

OR

Should I just create a Master database to export into from the other databases? My situation is we have no server, so everyone has a local copy which I then have to go around backing up to an external hard drive, then export to excel and merge and then apply a unique ID to identify (which is complicated as I am also exporting a junction table)...

Any advice OTHER THAN quitting my job :/
0
Gustav BrockCIOCommented:
Do I need the XML after the 12 as you suggested ?

Yes. If not, the file format will be different, the binary xlsb.

As for the path, concatenate it with the filename:

strFile = Me!TextboxWithFolder.Value & "\Book1.xlsx"

Open in new window

/gustav
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bill NelsonITAuthor Commented:
It still will not update the Excel workbook. I have enabled Full control permissions and nobody else has access to this machine, however, I am getting the following error:  Runtime error 3051. Its already open exclusively (no it isn't) or you need permission to view and write it's data. Please see Error.png and you will notice it looks like it has concatenated it incorrectly as: "C:\Excel DE To Import 2Book1.xlsx"

Any advice on what I am doing wrong?

Thank you,

Bill
Debug_Print_Screen.png
Error-Message.png
0
Gustav BrockCIOCommented:
You have omitted the backslash before Book1.xlsx.

/gustav
0
Bill NelsonITAuthor Commented:
Now I have Runtime error 3011. Pics attached including Debug Values.

Thanks for your help !

Bill
Error-3011.png
Error3011DebugValues.png
0
Gustav BrockCIOCommented:
As you can see, you append the name of the file name, you wish to export to, to the name of the query you wish to export. Don't do that.

/gustav
0
Bill NelsonITAuthor Commented:
Ok, I have been trying all sorts of variations with no luck as I clearly do not know where the append is happening.
Ibroke out the FilePath and FileName to see if I could figure it out but I do not understand where you are talking about in the code below:


Private Sub Command0_Click()
  Dim strFile As String
  Dim varItem As Variant
  Dim strPath As String
  
    strPath = Me.TextboxWithFolder.Value   'so in this case the default is C:\Excel DE to Import 2
    strFile = "\Book1.xlsx"  ' the File to inmport into is named "\Book1.xlsx"
    If (strPath = vbNullString) Then Exit Sub
    
    For Each varItem In Me.lst_Export_Queries.ItemsSelected
        DoCmd.TransferSpreadsheet transferType:=acExport, spreadsheetType:=acSpreadsheetTypeExcel12Xml, tableName:=Me.lst_Export_Queries.ItemData(varItem), FileName:=strFile
    Next
    
    MsgBox "Process complete.", vbOKOnly, "Export"
    
    End Sub

Open in new window


I am afraid I am lost in this, Gustav...
0
Gustav BrockCIOCommented:
Th append is happening - or rather, has happened - here:

Me.lst_Export_Queries.ItemData(varItem)

Open in new window

Somehow this pulls both the name of the query and "\Book1.xlsx" and concatenate these.
You could try:

tableName:=Me.lst_Export_Queries.ItemData(varItem)(0)

or:

tableName:=Me.lst_Export_Queries.ItemData(varItem).Column(0)

or simply:

tableName:=varItem(0)

Open in new window

/gustav
0
Bill NelsonITAuthor Commented:
Sadly, none of these variations work either...

I give up
0
Gustav BrockCIOCommented:
Well, what do they return?
Or attach a sample database.

/gustav
0
Bill NelsonITAuthor Commented:
Ok I will be able too in a few hours...
0
Bill NelsonITAuthor Commented:
Ok, so I simplified, I think, and set the pathway in stone, however, I need the Excel workbook name to be dynamic, and this is why the query has been linked to the form...so that this Excel filename (yyyy-mm-dd) will be created with the two worksheets.

It executes with no errors but nothing is created.

The pathway already exists: C:\Excel DE to Import To

I am not sure what I am doing wrong !!!!

Bill
EE-v-1.zip
0
Gustav BrockCIOCommented:
Not sure where the date came in ... but you could do like this:

Private Sub Command0_Click()

    Dim strPathway  As String
    Dim strFilename As String

    strPathway = "c:\Excel DE to Export 2\"
    strFilename = Format(Me!txt_Excel_File_Name, "yyyy-mm-dd") & "_Book1.xls"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_MCRs_to_Output", _
        strPathway & strFilename, True

End Sub

Open in new window

Please see attached.

/gustav
EE-v-2.zip
0
Bill NelsonITAuthor Commented:
It is saying it is not a valid path. Runtime Error 3044 (is not a valid path...see pics

C:\Excel DE to Import 2 is a legit pathway on this local computer.

I have also created an Excel workbook with the filename, both .xls and .xlsx as well as an empty folder... same error each time.

The date is what I would like to name the workbook vs. Book1, however, if I have to have an empty Excel workbook to import into and then rename it to the date I guess I could do that as well...

Thanks,

Bill
Error-3044.png
Pathway.png
0
Gustav BrockCIOCommented:
This seems to be valid:
     C:\Excel DE to Import 2

However, this seems not to exist:
    C:\Excel DE to Export 2

/gustav
0
Bill NelsonITAuthor Commented:
and yet if you look at the folder structure picture it clearly does exist.

I am attaching the original version found on the internet which supplies a multi-select listbox with a list of the tables to select from.
Problem 1 is I need queries to fill the listbox
Problem 2 is I do not want to have to enter in the full pathway and file name in the popup input box...I want them to use the concatenated file pathway bound to t_Defaults and the month which is also taken from t_Defaults and is the dynamic name of the file.

Using this in the Input Box popup form works: C:\Excel DE to Import 2\Book1
Original_Multi_Table_Export_To_Excel.zip
0
Gustav BrockCIOCommented:
This expert suggested creating a Gigs project.
First, I cannot locate any folder on the picture containing "export", only "import".

Second, many things in your download. As is, it - kind of - works, but it writes all selections to the same filename, thus only the last selection will be exported to the file.

Please have in mind, that we have real work to do also. If you wish me (or others) to completely write up your two tasks, please open a project in Gigs.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill NelsonITAuthor Commented:
Thanks very much for your help !

Hope you have an awesome day !

Bill
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.