VB Code to import from closed excel file

[rizmomin]

Hello:

I need VB Code to perform following:

I have closed excel file as attached located at location defined in cell J2 of attached file (Dest.xls) where i need
to import data.
The data which needs to be imported are in range A1:F500 and to be pasted at Cell A1 of Dest file.

Please let me know if you have any questions.
Thanks.

RM

[rizmomin]

Hello:

I have tried below code since i have been successfully using for some other purpose but currently it is giving error at

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[jindon]

Only if sheet name is known.

Please Login or Register  to view this content.

[rizmomin]

Hi Jindon:

I have tried the code and seems to give error.
Please refer to both attatched file.
Basically needs to bring data from Import.xls to Destination file.
Please let me know if you have any questions.
Thanks.

Riz

[jindon]

Import.xls needs to be fixed.

It says:
File type and extension doesn't match

when I try to open, so I created new file with the same file name.

[rizmomin]

Hi Jindon:

Have a look at the pic attached.
I too created new "Import.xls" and still error as attached.
Please let me know if you have any questions.
Thanks.

Riz

[jindon]

See attached

[rizmomin]

Hi Jindon:

One question is the name of the sheet where data is pulled is variable and it will be at cell I1.
Please help change the code below to accomodate this.

Please Login or Register  to view this content.

Thanks
Riz

[jindon]

Please Login or Register  to view this content.

[rizmomin]

Hello Jindon:

This works great.
May i ask you something.
The files you mentioned earlier "Import" file would not open?
I have lots of similar files in the folder, these files are generated by Payroll software and i do not have any control.
Is there a VB Code way we can convert all these files so that this problem of not opening does not exist any more.
Please help with your expertise in this area as i now realize that this will be ongoing issue.
Thanks a lot for all your help/.
Please let me know if you have any questions.
Thanks.

Riz

[jindon]

This will re-save .xls file as .xlsx file, so run this first and change the file extension in I2 to .xlsx.

Please Login or Register  to view this content.

[rizmomin]

Hi Jindon:
Great work....the code works excelllllllllllllllllllent....

Would appreciate if the code will be done so that it will pick up the file from location at say cell M1 and then save the new file at the same location.
Thanks a lot.
Riz

[rizmomin]

Hi Jindon:

Can you please help me with last tweaking on above code.

Please Login or Register  to view this content.

Instead asking to select the file which i want to convert from .xls to .xlsx, can you please change the code so that the file is picked up from the location in cell say K2 or something like that.
This way i will be able to automate without manual interuption.
Thanks a lot
Riz

[rizmomin]

Hi Jindon:

Can you please help me with last tweaking on above code.

Please Login or Register  to view this content.

Instead asking to select the file which i want to convert from .xls to .xlsx, can you please change the code so that the file is picked up from the location in cell say K2 or something like that.
This way i will be able to automate without manual interuption.
Thanks a lot
Riz

[rizmomin]

Hi Jindon:

I think i got it...
Look forward for future needs.
Thanks a lot for every thing.
Riz

[jolivanes]

@rizmomin
Is your tweak a secret?

[rizmomin]

Hi Jolivanes:

My Tweak is no secret...sorry.
Below is the modification i made and works like a charm.

Thanks
Riz

Please Login or Register  to view this content.

[jolivanes]

Thanks for sharing.
If someone is looking for a solution and ends up at this thread, it is not very helpful if it states that there is a solution but it is not published in this thread.
Anyway, have a Merry Christmas and a Happy, Healthy and Prosperous New Year.