Hi,
please help. Is it possible to create a macro that would do the following.
- I have an open workbook, when I run the macro I want to copy the value of cells B1, C3 and D5 from the sheet "Job Sheet"
- then I want a new workbook to be opened, then the value of B1 to be copied to G4, C3 to E7, D5 to M2.
- the new workbook to be saved with the same name as the source plus the extension "JS"
Hi,
like this:
Please Login or Register to view this content.
Best regards, Alexandra
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved"
Hi,
That's exactly what I need, thank you very much.
Hi,
you´re welcome, i´m glad that i could help you!
BR, Alexandra
I can't find button for acept solutions
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add * Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved"
Hi,
can you help me please? When I run this macro from the file all good. When I run the same macro from XLSB, this error appears.
Screenshot 2025-07-15 135410.png
Last edited by KreshBell; 07-15-2025 at 09:03 AM.
Invalid Attachment?i can´t open it
Hi, now is ok
Hi,
please check the Sheetname?
BR, Alexandra
checked, sheetname is correct. Is it possible ignore sheetname because only one sheet name (Job Sheet) is in source file?
Hi,
yes with ActiveCell like this:
For me does the code works also in a .xlsb file. Is your xlsb file saved in a path or is it only a new opened file, which is not yet saved?Please Login or Register to view this content.
Please upload your sample file...
BR, Alexandra
Hi,
the macro now generates a new file and saves it in the folder where the xlsb file is, it also takes the file name from the xlsb file. I would like it to take the name of the file that is open when I generate the macro and save it in the same folder where the file is saved
Sorry, i can´t understand.
We have Source.xlsb in Path "SamplePath" > Start the macro > we have a new file with the same name as the xlsb file with the name "SourceJS.xlsx", this file will be also in the same path saved as the xlsb. Can you exactly explain what you mean?
BR, Alexandra
I have a file called Project Data.xls saved in my My Documents folder. I open that file, then run the macro that is saved in the xlsb file. The macro generates a new file named Project Data JS.txt and saves it in my My Documents folder.
Hi,
i´m confused!
And you realy mean TxT? What if you have more then this workbooks open, how should the code know, which name to take?
BR, Alexandra
no, the macro is just saved in the xlsb. It should copy the data from the file that is currently open when it is run
So the code have to check, which workbooks are open with the name <> .xlsb, (condition is that you have no other files open) copy the data to new file and save the new file with the same name as your opened file & JS in the same path like your xls file, right? Or do you want, when you start the code, that you can choose the file have to be open(.xls), so like this you can have a lot of ohter workbooks opened, no matter.?
BR, Alexandra
I make the connection between ERP and CAD. Everything works great and I have no problems when it comes to Autodesk Inventor. For some reason we also use stupid AutoCAD. The only way to extract data from ERP to AutoCAD is through a txt file.
1 step - I generate xlsx from ERP
2nd step - I open that xlsx and run the macro (which is saved in xlsb), generate a txt file
Step 3 - I import the txt file into AutCAD
OK, and what is the delimiter for the txt file?
also i think you have more then the 3 cells, which you want in your txt file, right? In this case i need s sample file xls how look your sourcedata and a sample txt file, how should it be as txt file!
BR, Alexandra
please check attached, AutoCAD read only this
OK, now i understand, i just need your xls source file as sample with all columns and 2-3 rows data.
BR, Alexandra
please find attached xlsx
https://1drv.ms/x/c/e752d71a92a84c62...9iXIg?e=MYjy4R
Are you sure, this is the sheet you want to export to txt file? This is a form!
see next post
Are you sure, this is the sheet you want to export to txt file? This is a form! This is the file you receive from ERP?
this is a problem, I can extract from ERP only xlsx, but stupid AutoCAD can import only txt
But the txt file content does not corespondent with the your xlsx file content?
BR, AlexandraPlease Login or Register to view this content.
yes, need rearange
ActiveSheet.Range("A1").Value = "HANDLE"
ActiveSheet.Range("B1").Value = "BLOCKNAME"
ActiveSheet.Range("C1").Value = "DESIGNER"
ActiveSheet.Range("D1").Value = "D365"
ActiveSheet.Range("E1").Value = "COMPONENTCODE"
ActiveSheet.Range("F1").Value = "COMPONENTNAME"
ActiveSheet.Range("G1").Value = "ITEMCODE"
ActiveSheet.Range("H1").Value = "ITEMNAME"
ActiveSheet.Range("I1").Value = "AREACODE"
ActiveSheet.Range("J1").Value = "AREANAME"
ActiveSheet.Range("K1").Value = "PROJECTCODE"
ActiveSheet.Range("L1").Value = "PROJECTNAME"
ActiveSheet.Range("M1").Value = "CLIENT"
ActiveSheet.Range("C2").Value = ActiveSheet.Range("P2").Value
ActiveSheet.Range("D2").Value = ActiveSheet.Range("H8").Value
ActiveSheet.Range("E2").Value = ActiveSheet.Range("H7").Value
ActiveSheet.Range("F2").Value = ActiveSheet.Range("C7").Value
ActiveSheet.Range("G2").Value = ActiveSheet.Range("H5").Value
ActiveSheet.Range("H2").Value = ActiveSheet.Range("C5").Value
ActiveSheet.Range("I2").Value = ActiveSheet.Range("H4").Value
ActiveSheet.Range("J2").Value = ActiveSheet.Range("C4").Value
ActiveSheet.Range("K2").Value = ActiveSheet.Range("H3").Value
ActiveSheet.Range("L2").Value = ActiveSheet.Range("C3").Value
ActiveSheet.Range("M2").Value = ActiveSheet.Range("C9").Value
Hi,
OK, this is completly other topic as the last one, for the next time please open a new thread. However, not sure if i understand you correctly, but just test it.
1. The code is in the attached xlsb file
2. Pust the button, then you are able to choose the sourcefile
3. The code will grab the requested date into an temporary excel file
4. the content of the temporary excel file will be exportet to a txt file in the same path like the source file
5. Source and Tempfile will be closed, no saving changes
BR, AlexandraPlease Login or Register to view this content.
Hi,
sorry for the delay, thanks for your help, but after selecting the xlsx file this error appears
Attachment 896173
Hi,
please start the code in VBA Editor using F8 and go row by row, when you pass the line "txtfile = WB.Path.......". Please go with the mouse over "txtfile" and show me what does it exactly show!?
BR, Alexandra
thanks for your reply, sorry again for delay, please check ...
Hi,
in the 3rd pic, where i can see your excel files, please show me the whole windows, where i can see also the whole path etc?
BR, Alexandra
unfortunately doesn't want to show the whole path
OK, is your sharepoint synced with your local computer?
yep, I already use lots of macro's saved to this xlsb
OK, then i wondering, why your debug show a "HTTP://" address?
Please go by windows explorer to your xlsb file and to your source file, each right mouseclick then "properties" and show me the path like "C:\users\..." etc. you don´t have to show the whole path, but the beginning
BR, Alexandra
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks