workbook in Access vba doesn't open (1 Viewer)

S

so10070

Registered User.
Local time
Today, 09:05
Joined
Aug 18, 2016
Messages
51
When I try to open a workbook in my Excel application I get the error 1004 ("the file .....xlsx can't be opened because the file extension or the file format isn't correct"). I have checked this and the extension as the format is correct. I got the error on the line ".[FONT=&quot]Workbooks.Open (txtOpenWerkboekPad)"[/FONT]
Code: 
  [FONT=&quot]                                Dim XL As Object[/FONT]
  [FONT=&quot]                        Dim OpenWerkboek As Object[/FONT]
  
  [FONT=&quot]                        Set XL = CreateObject("Excel.Application")[/FONT]
  [FONT=&quot]                        txtOpenWerkboekPad = "C:\IGEANToets_FE\TeVersturenExcels\" & txtStamboeknummerVersturen & ".xlsx"[/FONT]
  
  [FONT=&quot]                        With XL[/FONT]
  [FONT=&quot]                            .Visible = False[/FONT]
  [FONT=&quot]                            .DisplayAlerts = False[/FONT]
  [FONT=&quot]                            .Workbooks.Open (txtOpenWerkboekPad)[/FONT]
  
  [FONT=&quot]                            With .OpenWerkboek[/FONT]
  [FONT=&quot]                                .Range("A2").Select[/FONT]
  [FONT=&quot]    '                            .Range("A2", Selection.End(xlDown)).Select[/FONT]
  
  [FONT=&quot]                            End With[/FONT]
  [FONT=&quot]                            OpenWerkboek.SaveAs "C:\IGEANToets_FE\TeVersturenExcels\" & txtStamboeknummerVersturen & ".xlsx"[/FONT]
  [FONT=&quot]                        End With[/FONT]
  
  [FONT=&quot]                        OpenWerkboek.Close[/FONT]
  [FONT=&quot]                        XL.Close[/FONT]
  
  [FONT=&quot]                        Set OpenWerkboek = Nothing[/FONT]
   [FONT=&quot]                        Set XL = Nothing[/FONT]
Additionally I try to make a Excel macro in my Access application. Is that possible?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:05
Joined
Oct 29, 2018
Messages
21,454
Hi. Where did the file you're trying to open come from? To test your code, try creating a new Excel file on your computer and try to open it using your code.
 
S

so10070

Registered User.
Local time
Today, 09:05
Joined
Aug 18, 2016
Messages
51
Excel file was corrupt. This works now. Thanks! :eek:
But my second question remains: is it possible to run an Excel macro from an Access application, like:
Code: 
    Range("L2").Select
    Range(Selection, Selection.End(xlDown)).Select
Code: 
    Range("M2:M26").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="0", Formula2:="3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Foute ingave"
        .InputMessage = ""
        .ErrorMessage = "Geef juiste waarde in!"
        .ShowInput = True
        .ShowError = True
    End With
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:05
Joined
Oct 29, 2018
Messages
21,454
Hi. Glad to hear you got the first issue sorted out. Unfortunately, I am not sure about your last question because I haven't tried it yet. Hopefully, someone who knows will jump in. Cheers!
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 08:05
Joined
Feb 19, 2013
Messages
16,607
You can - not in a position to prove the actual syntax bit something like

Xlapp.macroname.execute

Xlapp is the object you used to open excel (not shown in you code) and Macroname needs to be declared public
 
S

so10070

Registered User.
Local time
Today, 09:05
Joined
Aug 18, 2016
Messages
51
I have solved this problem. Thanks for the suggestions :). Now I want to make the "Range" in VBA variable and put the data in a string. When I want to set the range, "Set rng = string" I get an error "Types do not match". How can I modify the type of the string to match the object "Set".


Code: 
                        Dim iRijen As Long
                        Dim XL As Excel.Application
                        Dim OpenWerkboek As Excel.Workbook
                        Dim rng As Excel.Range
                        Dim txtRanges As String
                        Dim txtNaamKolom As Strings
                        
                        Set XL = CreateObject("Excel.Application")
                        Set OpenWerkboek = XL.Workbooks.Open("C:\IGEANToets_FE\TeVersturenExcels\" & txtStamboeknummerVersturen & ".xlsx")
                        
                        txtRanges = ""
                        txtNaamKolom = "MOQSUWY" 'Let op slechts 7 domeinen maximaal toe te kennen
                        
                        With XL
                            .Visible = False 'waarom False?
                            .DisplayAlerts = False
                            
                            With OpenWerkboek
                                'vanaf hier Excel formatting
                                'opletten op iAantalDomeinen!
                                'Bepaal het aantal Ranges volgens het aantal iAantalDomeinen
                                'Opnieuw de integer "i" gebruiken
                                For i = 1 To iAantalDomeinen
                                    txtRanges = txtRanges + "Range(""" & Mid(txtNaamKolom, i, 1) & ";" & iAantalLeerlingen & """),"
                                Next i
                                txtRanges = "Union(" & Mid(txtRanges, 1, Len(txtRanges) - 1) & ")"
                                
                                [U][B][COLOR=Blue]Set rng = txtRanges[/COLOR][/B][/U]
                                
                                With rng.Validation
                                    .Delete
                                    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0", Formula2:="3"
                                    .IgnoreBlank = True
                                    .InCellDropdown = True
                                    .InputTitle = "Punten ingeven"
                                    .ErrorTitle = "Foute ingave"
                                    .InputMessage = "Geef hier je punten in."
                                    .ErrorMessage = "Geef juiste waarde in!"
                                    .ShowInput = True
                                    .ShowError = True
                                End With

                            End With
                            OpenWerkboek.SaveAs "C:\IGEANToets_FE\TeVersturenExcels\" & txtStamboeknummerVersturen & ".xlsx"
                        End With
                        
                        OpenWerkboek.Close
                        XL.Close
                        
                        Set OpenWerkboek = Nothing
                        Set XL = Nothing
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 08:05
Joined
Sep 21, 2011
Messages
14,232
Debug.Print txtRanges

what does that show?

I've only ever set ranges like Set rng = Range("A1:C3")
 
You must log in or register to reply here.

Similar threads

oxicottin
Solved Export search form data to excel
Replies
11
Views
587
oxicottin
oxicottin
N
Solved Exporting Error If fist query has no records...
Replies
11
Views
644
theDBguy
theDBguy
D
Runtime error 1004 PasteSpecial method of Range class failed.
Replies
2
Views
315
Isaac
Isaac
C
Solved i want to change this code to merge two excel files to one sheet side by side.
Replies
2
Views
209
camiramzi
C
W
Check for open excel workbook and if open take control
2
Replies
23
Views
967
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom