• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

Access 2007 export to Excel 2007 not working

I have a weird issue going on. I have an Access 2007 database and an XLS with multiple tabs. As of now, i use the acspreadsheettypeexcel9 command to export data from Access to the XLS putting specific tables of data into specific tabs. This works perfectly. However i would like to do the same thing but going to an XLSX. So i tried using acspreadsheettypeexcel12 and acspreadsheettypeexcel12Xml but when i run it no data gets transferred to the XLSX. Can anyone think of a reason why this might not work?

Below is a sample of one of my export lines. the sFn variable prompts the user to choose the XLSX.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "FC_Count of File Creations by Day", sFn, True, "2A_FileCreationbyDay!A1:B1"
 DoEvents

Open in new window

0
Yaniv Schiff
Asked:
Yaniv Schiff
  • 17
  • 16
1 Solution
 
Rey Obrero (Capricorn1)Commented:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "FC_Count of File Creations by Day", sFn, True, "2A_FileCreationbyDay"


i'll assume that you define  sFn as the path and name of the excel file
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
Yes, sFn returns "C:\Users\yschiff\Desktop\2.xls" for example
0
 
Rey Obrero (Capricorn1)Commented:
did you try the code i posted?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Yaniv SchiffDirector of Digital Forensics Author Commented:
or if i'm choosing an Excel 2007 file "C:\Users\yschiff\Desktop\1.xlsx"
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
Yes, i've tried both Excel12 and Excel12Xml
0
 
Rey Obrero (Capricorn1)Commented:
did you try the code i posted?

what is the result?
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
i tried you're code. same result...no data get's exported. The code executes, it doesn't look like it does anything, but then after a minute excel opens and the rest of my code executes (which is just applying formatting to the cells in excel).
0
 
Rey Obrero (Capricorn1)Commented:
can you upload your db
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
sorry, no.
0
 
Rey Obrero (Capricorn1)Commented:
ok, then sorry, i can't be of help no more
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
I can upload the sFn code and the some of the export code but the database contains a lot of proprietary code and data which i don't feel comfortable releasing. Are there any issues you can think of that might be a factor here? if not, thanks for trying.
0
 
Rey Obrero (Capricorn1)Commented:
just upload the codes related to this problem..
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
here is the code.
sFn.txt
export.txt
0
 
Rey Obrero (Capricorn1)Commented:
you have this

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "IH_IE History by URL Host", sFn, True, "5B_IEHist_Count!A1:B1"


try with this, just like my first post

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "IH_IE History by URL Host", sFn, True, "5B_IEHist_Count"
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
I tried your code with the same result. I tried stepping through the export lines and on every export procedure, it looks like it's exporting but then no data actually gets copied out. The cursor turns into an hourglass.
0
 
Rey Obrero (Capricorn1)Commented:
try this with hard coded file name

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "IH_IE History by URL Host", "c:\myExcel.xls", True, "5B_IEHist_Count"
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
same result.
0
 
Rey Obrero (Capricorn1)Commented:
do this

open "IH_IE History by URL Host"

from the ribbon
External Data > Export Group > excel

what happened?
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
it exported succesfully.
0
 
Rey Obrero (Capricorn1)Commented:
create a new module, place this codes

sub expXL()
on error goto ExpErr
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "IH_IE History by URL Host", "c:\myExcel.xls", True, "5B_IEHist_Count"

ExpErr:
     msgbox Err.number
     err.clear
     resume next
end sub

in the immediate window type  expXL  and hit enter

see if you are getting an error in a message box

0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
two msg boxes pop up, the first has error number 0, the second error number 20
0
 
Rey Obrero (Capricorn1)Commented:
was records exported to myExcel.xls ?  

error 0 and error 20 means no error
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
yes, data was written
0
 
Rey Obrero (Capricorn1)Commented:
so we' re done..
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
hmmm, not quite. maybe i wasn't clear in my description, and i apologize, but the issue is when i try to write to a xlsx that is created from a excel template i have. The procedure is, the user opens the template, saves a xlsx, closes excel then runs the vba which prompts the user for the xlsx. The data then gets exported to specific sheets in the workbook and some formatting is applied. This works just fine when doing it to a xls but not when doing it to a xlsx. The excel template has some charts and stuff in it so i can't just have access create a new excel document.

Does this make sense?
0
 
Rey Obrero (Capricorn1)Commented:
try modifying the codes we have at http:#a26210743
for an .xlsx file, that you created from the template
see what happens
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
ok i tried that and got error number 3274
0
 
Rey Obrero (Capricorn1)Commented:
the error is saying that  'External table is not in the expected format.'

unless i see the excel file, i can't help no more.
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
here is the xlsx created from my template
myexcel.xlsx
0
 
Rey Obrero (Capricorn1)Commented:
ok.. i am also getting an error 3274 using the transferspreadsheet using a .xlsx file
couldn't find a reason why..
here is the code that works



Sub exportXLSX(xlFile As String, strTable As String, strSheet As String)
Dim xlObj As Object, objSheet As Object, rs As DAO.Recordset
Dim fld As DAO.Field, intCol As Integer
Set xlObj = CreateObject("excel.application")
    xlObj.workbooks.Open xlFile
    Set rs = CurrentDb.OpenRecordset(strTable)
    
    With xlObj
        Set objSheet = .ActiveWorkbook.worksheets(strSheet)
        With objSheet
            For Each fld In rs.Fields
                intCol = intCol + 1
                .Cells(1, intCol) = fld.Name
            Next
            .Range("A2").CopyFromRecordset rs
        End With
        .ActiveWorkbook.Save
    End With
    xlObj.Quit
    rs.Close
End Sub

Open in new window

0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
I'm not quite sure how to use this Cap.
0
 
Rey Obrero (Capricorn1)Commented:
place the codes in a regular module..

to use the codes,
from where you are calling your codes use this

       Call exportXLSX("full path to the excel file", "the name of the table","the name of the sheet")

      using the code below as an example
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "IH_IE History by URL Host", sFn, True, "5B_IEHist_Count"
     
       this will be translated like this

            Call exportXLSX(sFn(), "IH_IE History by URL Host","5B_IEHist_Count")







     
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
Cap, that solution worked perfectly. Thank you for taking the time to troubleshoot this annoyance. It's still weird that the simple transferspreadsheet command doesn't work but actually,  your code is cleaner. Thanks again.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 16
Tackle projects and never again get stuck behind a technical roadblock.
Join Now