• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel cannot open this file - The file format or file extension is not valid.|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Excel cannot open this file - The file format or file extension is not valid.|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosExcel cannot open this file - The f…
sp_PrintTopic sp_TopicIcon
Excel cannot open this file - The file format or file extension is not valid.
Avatar
Daniel Darvill

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
1
July 7, 2020 - 6:19 am
sp_Permalink sp_Print sp_EditHistory

Hey there! 

I'm fairly new to VBA so sorry if this is a stupid question. 

I have written a fairly simple Macro which copies a sheet from multiple different workbooks and pastes it into one workbook. This works fine when I run it on my PC but when I transfer over to mac I get the following error: 

Excel cannot open this file - Microsoft CommunityImage Enlarger

 

 

 

When I go to look at the files manually I can open them no problem its only when the Macro tries to open them that the error is thrown. 

Any Ideas on how to fix it? 

My code is as follows:

Sub Add_Bridge_1(fileStr)
Dim wbk1 As Workbook, wbk2 As Workbook, mas As Worksheet, fileLoc As String

fileLoc = Range("B10").Value

'add your own file path
'fileStr = "C:\Users\QQ\Desktop\Test\Order 1.xlsx"

Set wbk1 = ActiveWorkbook
Set mas = wbk1.ActiveSheet
Set wbk2 = Workbooks.Add(fileLoc & fileStr)

If wbk2.Sheets(1).Name = "Export Summary" Then
wbk2.Sheets(1).Delete
End If

'wbk2.Sheets("Bridge 1").Copy After:=Workbooks("WorkbookNameYouCopyCodeInto").Sheets(1)
wbk2.Sheets(1).Copy After:=wbk1.Sheets(2)
wbk2.Saved = True
wbk2.Close

wbk1.Activate
mas.Activate

End Sub
Sub RepeatAdd()

Dim fileNames() As Variant
Dim fileLoc As String
Dim wbk1 As Workbook
Dim mas As Worksheet

Set wbk1 = ActiveWorkbook
Set mas = wbk1.ActiveSheet

fileLoc = Range("B10").Value
fileNames = GetFiles(fileLoc)

mas.Activate

For Each fileStr In fileNames
Add_Bridge_1 (fileStr)
Next fileStr

Sheets.Add After:=wbk1.Sheets(2)
ActiveSheet.Name = "START"

Sheets.Add After:=wbk1.Sheets(wbk1.Sheets.Count)
ActiveSheet.Name = "END"

mas.Activate

End Sub
Function GetFileNames(ByVal FolderPath As String) As Variant
Dim Result As Variant
Dim i As Integer
Dim MyFile As Object
Dim MyFSO As Object
Dim MyFolder As Object
Dim MyFiles As Object

Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files

ReDim Result(1 To MyFiles.Count)

i = 1

For Each MyFile In MyFiles
Result(i) = MyFile.Name
i = i + 1
Next MyFile

GetFileNames = Result

End Function

Function GetFiles(ByVal FolderPath As String) As Variant

Dim Books As Variant
Dim FileName As String
Dim Files As Variant
Dim Folder As Variant
Dim n As Long

ReDim Books(1 To 1)
ReDim Files(1 To 1)

n = 1

'Folder = "C:\Users\QQ\Desktop\POLARTEST\Hockey\"

' Filter for .xls, .xlsb, .xlsx, .xlsm, etc.
FileName = Dir(FolderPath & "*.xls*")

While FileName <> ""
Books(n) = Folder
Files(n) = FileName
FileName = Dir()
n = n + 1
If FileName <> "" Then
ReDim Preserve Books(1 To n)
ReDim Preserve Files(1 To n)
End If
Wend

GetFiles = Files

End Function

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1513
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
July 7, 2020 - 8:44 pm
sp_Permalink sp_Print

Hi Daniel,

That code works fine for me on my W10 PC.

At what line does it debug on the Mac?  Have you stepped through the code to see where it errors?

https://www.myonlinetraininghu.....g-vba-code

https://www.myonlinetraininghu.....ugging-vba

I don't have a Mac so I'd guess that the path in Range("B10").Valuedoesn't exist on the Mac?

Regards

Phil

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 613
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
3
July 7, 2020 - 11:12 pm
sp_Permalink sp_Print sp_EditHistory

Also note that you can't use Scripting.Filesystemobject on a Mac, and Dir doesn't always work terribly well either.

Avatar
Daniel Darvill

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
4
July 8, 2020 - 4:31 am
sp_Permalink sp_Print sp_EditHistory

Hey Philip, 

Yeah its very strange - it works fine on my PC too. 

On the Mac it is throwing the error on the following line: 

Set wbk2 = Workbooks.Add(fileLoc & fileStr)

The first time it tries to access the files

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1513
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
5
July 8, 2020 - 9:21 am
sp_Permalink sp_Print

Hi Daniel,

and when you step through the code, what value does fileLoc & fileStr have?

Does fileLoc exist and is fileStr a valid filename?

Regards

Phil

Avatar
Daniel Darvill

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
6
July 15, 2020 - 1:40 am
sp_Permalink sp_Print

Managed to fix it by changing

Workbooks.Add

to 

Workbooks.Open

No idea why that worked but it did! 

Thanks for your help

Dan

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham, Chandler Davis, Valentyn Kristioglo
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27212

 

Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.