Use Macro to open a file with excel (underfined type of file)

Dear Professionals

I wish to write a macro to do the following steps:

1. open a (fileA) (undefined type) with excel.
2. After opened the file, copying the content into the active workbook (Database).

I have tried the following code, it fails

Sub openQIR()
'
' openQIR 巨集
'

  
Dim wbSource As Workbook, wbDestination As Workbook
          
           
    Set wbSource = Workbooks.Open("C:\user\desktop\tatfish\fileA.xlsx")
    Set wbDestination = ThisWorkbook
    
    With wbSource.Sheets(1).Select
    Range("A1:H1", Range("A20000:H20000").End(xlUp)).Select
    Selection.Copy
        
    Windows("Database.xlsm").Activate
    Sheets("2").Select
    Range("A" & Rows.Count).End(xlUp).Offset(2).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    
End With
        
    Sheets("1").Select
    'Close source:
    wbSource.Close
'

End Sub

I understand it must fail because the type of fileA is not .xlsx
The file A is underfined type but it can be opened with excel (currently do it manually).

Please kindly teach me to amend my code for making it done.
Thousands thanks.

Set wbSource = Workbooks.Open("C:\user\destop\fileA.xlsx")'-Change the extension to the proper one.
Set wbDestination = ThisWorkbook'-You are not in chronological order.-this has to be before you open the other workbook.

You haven't explained what fails!!

check this line:-

    Set wbSource = Workbooks.Open("C:\user\destop\fileA.xlsx")

destop?        desktop

user\destop?   user\tatfish\desktop

See if this opens your file NB the :-

Sub Macro1()
Dim fso, fsoFolder, fsoFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(ActiveWorkbook.Path)
' replace with, if macro not in same folder " Set fsoFolder =Workbooks.Open("C:\user\tatfish\desktop"

For Each fsoFile In fsoFolder.Files

strname = fsoFile.Name

If Left(strname, 6) = "fileA." Or Right(strname, 5) = ".xlsx" Then

    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & strname
    'ActiveWindow.Close False
End If

Next

200 ' Exit
End Sub

Originally Posted by tatfish87

Dear Mehmetcik

...........]

the reason is that, fileA is not a excel file. But i wish to open it with excel.

Thanks

When you use the macro recorder to open the file what does the code look like?

See if this opens your file NB the :-

Sub Macro1()
Dim fso, fsoFolder, fsoFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(ActiveWorkbook.Path)
' replace with, if macro not in same folder " Set fsoFolder =Workbooks.Open("C:\user\tatfish\desktop"

For Each fsoFile In fsoFolder.Files

strname = fsoFile.Name

If Left(strname, 6) = "fileA." Or Right(strname, 5) = ".xlsx" Then

    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & strname
    'ActiveWindow.Close False
End If

Next

200 ' Exit
End Sub

Dear mehmetcik

I have tried your code with little amendment for my path as follow:

Sub Macro1()
Dim fso, fsoFolder, fsoFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = Workbooks.Open("C:\Users\tatfish\Desktop\ABC20141216")

' replace with, if macro not in same folder " Set fsoFolder =Workbooks.Open("C:\user\tatfish\desktop"

' if same folder, Set fsoFolder = fso.GetFolder(C:\Users\tatfish\Desktop\database)

For Each fsoFile In fsoFolder.Files

strname = fsoFile.Name

If Left(strname, 6) = "ABC20141216." Or Right(strname, 5) = ".xls" Then

    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & strname
    'ActiveWindow.Close False
End If

Next

200 ' Exit
End Sub

After i run the macro, the file is opened, but an error occur at this line

For Each fsoFile In fsoFolder.Files

It still open the file successfully, although the format inside is a bit strange.

For further clarification, "database" is the excel with the macro inside.

ABC20141216 is the fileA.

Thanks

Sub Macro1()
Dim fso, fsoFolder, fsoFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = Workbooks.Open("C:\Users\tatfish\Desktop\ABC20141216")

' replace with, if macro not in same folder " Set fsoFolder =Workbooks.Open("C:\user\tatfish\desktop"

' if same folder, Set fsoFolder = fso.GetFolder(C:\Users\tatfish\Desktop\database)

On Error resume next
For Each fsoFile In fsoFolder.Files

strname = fsoFile.Name

If Left(strname, 6) = "ABC20141216." Or Right(strname, 5) = ".xls" Then

    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & strname

on error goto 0


    'ActiveWindow.Close False
End If

Next

200 ' Exit
End Sub

Dear Mehmetcik

Thank you, thank error is no longer happen. It comes to another strange thing after the file is opened.

When the file is opened, there are many column and rows, the data are allocated in the respective cells correctly (the same as if i open the file manually with excel program).

some of the columns are "dates", i mean the information of the cell is a "date"
some are fine, but some of them are wrong format.

For example, some dates are "13-12-14" but some are "9/1/2013", i have tried to select them and manually change format, but it fails; regarding this, I realize that is a small green triangle show at the cells which have bad format "13-12-14". I have to manually click on the green triangle to fix the cell one by one. (I would like to have all date in the format of dd/mm/yyyy)

Can the macro cater for this?

Thank you very much.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic