Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-15-2019, 06:44 AM   #1
so10070
Newly Registered User
 
Join Date: Aug 2016
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
so10070 is on a distinguished road
workbook in Access vba doesn't open

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 ".Workbooks.Open (txtOpenWerkboekPad)"
Code:
                                  Dim XL As Object
                          Dim OpenWerkboek As Object
  
                          Set XL = CreateObject("Excel.Application")
                          txtOpenWerkboekPad = "C:\IGEANToets_FE\TeVersturenExcels\" & txtStamboeknummerVersturen & ".xlsx"
  
                          With XL
                              .Visible = False
                              .DisplayAlerts = False
                              .Workbooks.Open (txtOpenWerkboekPad)
  
                              With .OpenWerkboek
                                  .Range("A2").Select
      '                            .Range("A2", Selection.End(xlDown)).Select
  
                              End With
                              OpenWerkboek.SaveAs "C:\IGEANToets_FE\TeVersturenExcels\" & txtStamboeknummerVersturen & ".xlsx"
                          End With
  
                          OpenWerkboek.Close
                          XL.Close
  
                          Set OpenWerkboek = Nothing
                           Set XL = Nothing
Additionally I try to make a Excel macro in my Access application. Is that possible?

so10070 is offline   Reply With Quote
Old 04-15-2019, 06:48 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,454
Thanks: 20
Thanked 366 Times in 357 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: workbook in Access vba doesn't open

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 04-15-2019, 07:33 AM   #3
so10070
Newly Registered User
 
Join Date: Aug 2016
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
so10070 is on a distinguished road
Re: workbook in Access vba doesn't open

Excel file was corrupt. This works now. Thanks!
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

so10070 is offline   Reply With Quote
Old 04-15-2019, 07:46 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,454
Thanks: 20
Thanked 366 Times in 357 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: workbook in Access vba doesn't open

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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 04-15-2019, 12:27 PM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,697
Thanks: 40
Thanked 3,470 Times in 3,358 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: workbook in Access vba doesn't open

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 04-16-2019, 12:11 PM   #6
so10070
Newly Registered User
 
Join Date: Aug 2016
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
so10070 is on a distinguished road
Re: workbook in Access vba doesn't open

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) & ")"
                                
                                Set rng = txtRanges
                                
                                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
so10070 is offline   Reply With Quote
Old 04-16-2019, 12:39 PM   #7
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,275
Thanks: 358
Thanked 558 Times in 540 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: workbook in Access vba doesn't open

Debug.Print txtRanges

what does that show?

I've only ever set ranges like Set rng = Range("A1:C3")


__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ACCESS VBA - check if workbook is open or not open johnseito Modules & VBA 3 08-30-2017 05:23 AM
Open and update Excel workbook from Access bb29829 Modules & VBA 5 06-27-2017 08:35 PM
Check if Excel workbook is open in Access Andre Bruin Modules & VBA 4 05-13-2016 07:16 AM
[SOLVED] Open excel workbook from Access, clear cell contents Lifeseeker Modules & VBA 3 12-06-2013 05:54 PM
Open Workbook from Access Problem Carly Excel 2 06-15-2011 05:50 AM




All times are GMT -8. The time now is 10:22 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World