Macro - Copying from a read only file

davidhall

Board Regular
Joined
Mar 6, 2011
Messages
174
I have a master workbook named "workbook1" which a user is required to populate with information from a separate document always called "companydoc" which is downloaded from a program. I am trying to create a macro to perform a copy and paste from this read only file. I know how to assign a shortcut key to a macro which is ideally what I would like to do but I'm not familiar with how to write this kind of code.

The title of the document that is downloaded from the company program is always "companydoc" and defaults to a read only format.

This entire document needs to be Unmerged and Centered and pasted into sheet1 of workbooks starting at cell a1. I am currently clicking paste special-all using source theme.

I am trying to create a macro which does this process automatically. The user would be required to have "workbook1" already open and after clicking the download to excel feature in the company program, the user can simply press a shortcut key to automate the copy and paste feature.

Any suggestions?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
would this work?

Sub openfile()

Dim strFile As String
Dim Tempfile As Workbook
'delete old content
Sheets("Sheet1").Select
Cells.Select
Selection.Delete Shift:=xlUp
'open file
strFile = Application.GetOpenFilename
Workbooks.Open strFile
Set Tempfile = ActiveWorkbook
Cells.Copy
'paste into Book1.xls, sheet1
Windows("Book1.xls").Activate
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
'close temp open file
Application.CutCopyMode = False
Tempfile.Close
Range("A1").Select
End Sub
 
Upvote 0
Thanks for you help. I started a new macro and when I run it, it pops up a screen to open a file...similar to clicking on file-open


Is there anyways it can automatically reference the read only file named "companydoc" that was downloaded from the program and already opened before the macro is run?
 
Upvote 0
so upon opening the file, you want the macro to automatically go to a specific folder open the companydoc.xls file, copy the first sheet and paste it into sheet1 of your file?
 
Upvote 0
Prior to running this macro, the user is required to click on a "download to excel" option in a company program. Once the user clicks that download button, a message prompts them to open, save, or cancel. There is no need to save this document so they click open. The format is read only and the title is Companydoc.

I need the macro to automatically copy the only sheet that has data on the company doc and paste it in the other workbook
 
Upvote 0
So the file is temp file that you are trying to capture the info and pull into a workbook you are going to use. I am guessing the name of it always changes with each download?

That temp file would be the active workbook (at that moment), but I am not sure how to reference it when you open your file, if the name of the file changes. Maybe someone else can chime in?

I put this macro together to run automatically when you open your file. But you would need to save the downloaded file in a folder.
Also - if you use this, under the MS Visual Basic editor, put this under the ThisWorkbook - NOT in the Module section.

Private Sub Workbook_Open()

Dim strFile As String
Dim Tempfile As Workbook

'delete old content
Sheets("Sheet1").Select
Cells.Select
Selection.Delete Shift:=xlUp
'put your path between the ""
Workbooks.Open Filename:= _
"Q:\Depts\Shared\Questionnaires\Questionnaire Normalizations v2.xls"
Set Tempfile = ActiveWorkbook
Cells.Copy
'paste into Book1.xls, sheet1
Windows("Book1.xls").Activate
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
'close temp open file
Application.CutCopyMode = False
Tempfile.Close
Range("A1").Select
End Sub
 
Upvote 0
The file name is always the same when the user downloads it from the company program. Not sure if that makes a difference
 
Upvote 0
just get rid of or put an ' infront of:


Workbooks.Open Filename:= _
"Q:\Depts\Shared\Questionnaires\Questionnaire Normalizations v2.xls"


That should work
 
Upvote 0
The file name is always the same when the user downloads it from the company program. Not sure if that makes a difference
 
Upvote 0

Forum statistics

Threads
1,224,594
Messages
6,179,795
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top