Misunderstanding export excel file

3D Design Volume Model
1

Hello

I created a program in vba that exports data from a table (Userform) to an excel file.

The problem is that the program will run normally the first time. The 2nd (without modification) there will be a bug 1004.

 

CODE:

Sub Export_excel()

Sun i, j        As Integer

Sun i0, j0      As Integer

Let's evaluate the number of points

j0 = 0

i0 = 3

k = 0

While j0 < 1

    If Array.Workbook1.Cells(i0, 1) <> "None" Then

        k = k + 1

        i0 = i0 + 1

    Else

        d0 = 1

    End If

Wend

If k = 0 Then

    MsgBox "The table is empty"

    Exit Sub

End If

Nbpoints = k + 1

Call AddNewWorkbook

'We create the Excel object

'Set xlApp = CreateObject("Excel.Application")

'Set xlBook = xlApp.Worksbooks

Open File

'Workbooks.Open fileName:=lien_fichierxlsx

Workbooks(Nom_Fichier & ".xlsx"). Activate Error '1004'

'Worksheets'. Select

For i = 1 TB Nbpoints + 1

    Range("A" & i) = Array.Workbook1.Cells(i, 1)

    Range("B" & i) = Array.Workbook1.Cells(i, 2)

    Range("C" & i) = Array.Workbook1.Cells(i, 3)

    Range("D" & i) = Array.Workbook1.Cells(i, 4)

    Range("E" & i) = Array.Workbook1.Cells(i, 5)

    Range("F" & i) = Array.Workbook1.Cells(i, 6)

Next

ActiveWorkbook.Save

ActiveWorkbook.Close

MsgBox "Export successful!", vbMsgBoxSetForeground + vbExclamation, "TRAPRO DESIGN"

End Sub

 

I'm attaching the macro to you if you want to see how I did it.

You need to create a folder: D:\Import-Export\ (or change the link in Userform_Initialize)

 

Thank you very much for your answers,

JB


copie_de_prog.swp
2

Here is an example that works the first time and not the second...


capture2.png
3

Hello

 

Which line does the error occur? When you debug, normally, the line is highlighted.

4

It's on the line:

Workbooks(Nom_Fichier & ".xlsx"). Activate

 

If I put it in the comment it's the line:

Range("A" & i) = Array.Workbook1.Cells(i, 1)

 

In fact I have the impression that the prb comes with the excel language

 

5

In fact your line:

 

'Workbooks.Open fileName:=lien_fichierxlsx

 

 

Is in the comments and at the end of your code you close your binder!

 

ActiveWorkbook.Close

 

So at the beginning of your macro, you have to test if it's already open and reopen it if necessary!

6

A snippet of code that allows you to test if a workbook is open and open it if necessary:

 

Dim wbName As String = "Test.xlsx"

Dim PathName As String = "c:\Test\" & wbName

Dim wbBook As Excel.Workbook

 

 

For Each wbBook In xlApp.Workbooks

 

If wbBook.Name = wbName Then

wbBook.Activate()

Exit Sub

End If

Next

Dim xl_wbBook As Excel.Workbook = xlApp.Workbooks.Open(PathName) 

7

In fact, I was creating an excel file in AddNewWorkbook.

I did some tests by opening it in the other macro.

 

Sub excel_ouvert()

Dim wbName As String

Dim PathName As String

Dim wbBook As excel. Workbook

 

'wbName = "Test.xlsx"

'PathName = "D:\Import-Export\" & wbName

 

For Each wbBook In xlApp.Workbooks

    If wbBook.Name = Nom_Fichier & ".xlsx" Then

        wbBook.Activate

        Exit Sub

    End If

Next

Sun xl_wbBook As excel. Workbook

 

xl_wbBook = xlApp.Workbooks.Open(lien_fichierxlsx)

 

End Sub

 

I have an error when creating the file:

xl_wbBook = xlApp.Workbooks.Open(lien_fichierxlsx)

 

To understand it I put the macro in the first comment.


capture4.png
8

There is the option 

 

UserForm1.Workbook1.Export

 

Can it be saved in a folder?

9

At the time for me, the snippet of code is not functional!

 

Instead, it should be created:

Dim newWorkbook As Excel.Workbook = Me.Application.Workbooks.Add()

 

Then save it under the chosen name:

 

Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=lien_fichierxlsx, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Application.DisplayAlerts = True

 

I didn't understand your last message.

10

I put

 

Dim newWorkbook As excel. Workbook

newWorkbook = Me.Application.Workbooks.Add()

 

and he tells me "Misuse of Me"

The program is in a module.

 

Should I put it in?

UserForm1.Workbook1.Application.Workbooks.Add()

 

 

The Export function in Workbook 1 exports all the data in the workbook to an excel file.

However, it is read-only and in a special format: .XML

 

Name of the file created:

OWCSheet91101.XML

 

I want to know if it is possible by macro to save it in .xlsx to the desired name.

it would fix my prb

11

Sorry

Workbooks.Add

should be enough before the save as instead of 

Dim newWorkbook As excel. Workbook

newWorkbook = Me.Application.Workbooks.Add()

12

Here is my final Code:

Call AddNewWorkbook

'Test if Workbook opened

For Each xlBook In xlApp.Workbooks

    If xlBook.Name = Nom_Fichier & ".xlsx" Then

        xlBook.Activate

        Exit Sub

    End If

Next

'We add a binder

Set xlBook = xlApp.Workbooks.Add

'We give the binder a name

xlBook.SaveAs (lien_fichierxlsx)

 

'renames the

Sheets("Sheet1"). Name = "Bottle Table"

Sheets("Sheet2"). Name = "Data"

 

'Export the file

For i = 1 TB Nbpoints + 1

    Range("A" & i) = Array.Workbook1.Cells(i, 1)

    Range("B" & i) = Array.Workbook1.Cells(i, 2)

    Range("C" & i) = Array.Workbook1.Cells(i, 3)

    Range("D" & i) = Array.Workbook1.Cells(i, 4)

    Range("E" & i) = Array.Workbook1.Cells(i, 5)

    'Range("F" & i) = Array.Workbook1.Cells(i, 6)

Next

 

ActiveWorkbook.Save

ActiveWorkbook.Close

 

Thank you Lucas for your time!!

 

JB

1 Like