Excel Como exportar uma pasta de trabalho independente da raiz origem do Usuário?(VBA)

[Bruno_ABS]

Boa tarde pessoal !

Estou precisando de um help:
Criei um formulário bem simples de cadastro onde eu consigo, ao cadastrar os dados, apertar num botão "Exportar" ,  onde a macro copia a planilha contendo os dados cadastrados, salva numa outra pasta de trabalho, renomeia o nome do arquivo e também da planilha. 
Porém há um limitador, pois ele precisa ir na origem do arquivo pra realizar esse processo. 
E isso é um problema, porquê cada vez que alguém pegar esse formulário, precisaria entrar no Editor pra colocar o caminho da pasta de trabalho de seu próprio PC.

Há alguma maneira de contornar isso ?

Eu criei 2 Subs nesse processo, onde:

 

Sub SalvarNovaPlanilha()
Dim NovaPastadeTrabalho As Workbook
    Set NovaPastadeTrabalho = Workbooks.Add(xlWBATWorksheet)
        
        With NovaPastadeTrabalho
             ThisWorkbook.ActiveSheet.Copy After:=.Worksheets(Worksheets.Count) 'copio a planilha atual da pasta de trabalho
            .Worksheets(1).Delete                                              'deleto a primeira planilha da nova pasta de trabalho
            .SaveAs ThisWorkbook.Path & "\Nome_da_Planilha.xlsx"          'salvo o novo arquivo para a pasta de trabalho criada
            .Close False
        End With

End Sub

 

Sub Exportar()

Dim RootDir As String
Dim FileName As String

    Sheets("Plan1").Select
    Sheets("Plan1").Copy
        ActiveSheet.Shapes.Range(Array("Round Diagonal Corner Rectangle 2")).Select
        ActiveSheet.Shapes.Range(Array("Round Diagonal Corner Rectangle 2", _
            "Round Diagonal Corner Rectangle 1")).Select
    Selection.Delete
    Sheets("Plan1").Select
    Sheets("Plan1").Name = "Teste"
    
RootDir = "P:\Caminho\Caminho\"
RootDir = Replace(RootDir, "@1", Application.UserName)
FileName = "Novo Teste"

ActiveWorkbook.SaveAs RootDir & FileName & ".xlsx"
    
End Sub

 

Há uma maneira de não precisar especificar o Diretório de onde o arquivo se encontra ou não tem jeito ? 
Abraço a todos, desde já agradecendo !

[Basole]

não sei se entedi bem, o que esta precisando..... 

Se quer determinar um path (caminho) para salvar o arquivo em uma pasta comum para todos usuarios e em qualquer PC, pode usar a função [ Environ ]. 

 

Exemplos: 

 

Pasta Meus Documentos: 

MsgBox VBA.Environ("USERPROFILE") & "\Documents\"

 

Pasta Área de Trabalho:

MsgBox VBA.Environ("USERPROFILE") & "\Desctop\"

 

Pasta Temp:

MsgBox VBA.Environ("TEMP")

         =>  C:\Users\ [ usuario local] \AppData\Local\Temp

 

+ detalhes aqui: https://www.codevba.com/office/environ.htm#.YbElCNDMJdg

[Bruno_ABS]

Oi @Basole, obrigado por mais uma vez vir em meu socorro !

Vou mostrar aqui como estou querendo desenhar esse formulário e você se possível, me ajuda a melhorar e critique o que está errado please !

Basicamente, o formulário é poder cadastrar dados, além de poder edita-los e exportar a planilha numa pasta de trabalho nova (a ideia é ninguém digitar na planilha e imputar os dados pra que não tenham erros de ortografia etc, pra que eu possa trabalhar como importação no Power Query.

 

 

Esse Useform abre sem exibir a planilha, porém se a pessoa quiser ver o que está sendo cadastrado ela pode ativa-la pra visualizar (sei que seria muito melhor ter dentro desse Useform um objeto que mostrasse a lista com as colunas, mas não sei fazer e não cheguei a tentar).

Queria também que o ID pudesse ser acrescido automaticamente e sucessivamente (1,2,3 etc), mas como não entendi bem como fazer, coloquei de forma pra que a pessoa escrevesse e cadastrasse, porquê pelo menos assim eu consegui fazer.

 

Na ComboBox da Aplicação, ele está pegando uma range da própria planilha, pois se houver algo novo a ser acrescido e a pessoa não souber realizar um Additem, pelo menos dessa forma ela consegue encaixar escrevendo na tabela que serve de range.

 

Desativei o "X" da Useform pra pessoa ser obrigada a utiliza-la e apertar em "Sair" pra salvar e fechar. Não consegui pensar em nada melhor (nesse caso, preciso desativar as macros pra entrar e conseguir utilizar o VBE, pois desativando esse "X" não consigo dar Alt+F11 pra acessar o VBE.

A ideia do "Exportar" é:

Depois de cadastrar os dados na "Plan1" , o "Exportar" copiaria essa "Plan1", abriria uma nova Workbook ("Pasta1"), colaria os dados nessa nova Workbook, renomearia o nome da Planilha  e da própria Pasta de Trabalho e depois salvaria esse arquivo.

A pasta no caso não é comum a todos (não está compartilhada nem nada). Mas o desejo era de: se eu passasse esse formulário para alguém e ele exportar, vai conseguir realizar esse processo (em negrito) normalmente.
O problema que encontro é que o script que uso parece ser limitado à origem do meu PC (e que se eu passar isso pra alguém, vai dar erro óbvio):

 

Eu estava na variável "RootDir" inserindo o caminho todo do arquivo ("C:\caminho\caminho\etc\"), tentei colocar a variável como "ThisWorkBook.Path" (em azul) mas não serviu ou fiz algo errado.

E quando eu inseri o script pra desativar a visibilidade do Excel e deixar somente o Formulário, comecei a receber erro a partir do grifado em vermelho. O script é esse:

 

Espero que tenha conseguido melhorar o cenário pra ti !
Cordialmente,

[Basole]

@Bruno_ABS pelo que entendi está correto, referenciar o caminho (path) a partir da pasta de trabalho. Desta forma vai rodar em qualquer pc.sem precisar alterar isto ou gerar erros. 

 

Mas o que está faltando no seu codigo é uma coisa bem simples

 

 

A barra contraria [ \ ] entre o caminho e o nome do arquivo a ser salvo

 

ActiveWorkbook.SaveAs RootDir & "\" & Filename & ".xlsx"

 

Abrx. 

 

[Bruno_ABS]

@Basole Pior que fiz isso e continuou o erro ...

Como eu faço pra enviar por aqui arquivo ? Pelo que parece não aceita xlsm, apenas xlsx. Tem que ser em xlsx e aí você salva como xlsm ?! Ou há outra forma ?

[Basole]

@Bruno_ABS é o forum não aceita. voce precisa "zipar" (Compactar o arquivo), para fazer o upload

 

[Bruno_ABS]

@Basole Opa, pronto !

Cadastro de Eventos.rar

 

Aproveita e dá uma olhada pra saber se fiz muita besteira hahahah
 

[Basole]

voce se esqueceu de definir a variavel RootDir

 

RootDir = ThisWorkbook.Path & "\"

 

ficando assim : 

 

Sub Exportar()

Dim RootDir As String
Dim FileName As String
 
RootDir = ThisWorkbook.Path & "\"

    Sheets("Plan1").Select
    Sheets("Plan1").Copy
        ActiveSheet.Shapes.Range(Array("Round Diagonal Corner Rectangle 2")).Select
        ActiveSheet.Shapes.Range(Array("Round Diagonal Corner Rectangle 2", _
            "Round Diagonal Corner Rectangle 1")).Select
    Selection.Delete
    Sheets("Plan1").Select
    Sheets("Plan1").Name = "Tabulações"

ActiveWorkbook.SaveAs RootDir & FileName & ".xlsx"
    
End Sub

 

[Bruno_ABS]

@Basole Peço desculpas, provavelmente te passei o arquivo faltando linha porquê eu estava tentando outras coisas e esqueci de retornar rsrs

Mas pra mim tá dando erro ainda, onde na depuração a rotina pára aqui:

[Basole]

@Bruno_ABS sim eu vi que esses "shapes" não etão presentes, imageinei que tivesse removido

[Bruno_ABS]

@Basole Você teria uma solução melhor pra realizar esse procedimento ?!

A ideia é cadastrar tudo nessa planilha e exportar esses dados cadastrados para uma nova pasta de trabalho. Haveria histórico na planilha de cadastro, então toda vez que eu cadastrar algo novo é só exportar pra que essa base de dados seja atualizada conforme novos inputs.

 

Curioso é que eu havia feito uma outra planilha de cadastro mais simples e tinha funcionado perfeitamente, com renomeação da planilha e pasta de trabalho

[Basole]

Desconsiderando as linhas que contem os shapes, voce esqueceu tambem de definir a variavel FileName, por isso continua o erro.

 

FileName = "Defina_1_Nome_AQUI"

 

Segue o arquivo com algumas alteracoes

 

 

Cadastro de Eventos-v1.zip

[Bruno_ABS]

@Basole Curioso que numa das imagens acima eu havia definido o nome da FileName como "Teste" (a não ser que eu precisasse definir essa string depois das declarações. Curioso ...

Outra curiosidade: quando abri o arquivo que me enviou na área de trabalho, a rotina funcionou; quando coloquei ele nas subpastas de onde guardaria ela, falou rs. 
O que é muito curioso já que o diretório raiz foi uma variável definida como ThisWorkbook.Path.

 

Além de ter desconsiderado os shapes e declarado a variável FileName, fez mais alguma coisa ?! Olhei aqui por alto e me parece isso mesmo. 

[Bruno_ABS]

Olá @Basole ! 

Vou dar um up pra gente tentar entender qual pode ser o gap !
Tanto na Rotina que estávamos analisando quanto na abaixo, ela só funciona quando o arquivo se encontra na área de trabalho.
Quando eu aloco o arquivo em um caminho com muitas pastas (ex C:\Caminho\Caminho\Caminho etc) ela trava, parando na instância onde ela deve salvar (ThisWorkbook.Path).

 

Eu utilizei uma outra rotina que se encontra no arquivo que te passei, mas dei um tapa nela. 
Essa Sub funciona também de boas quando o arquivo está na Área de Trabalho. 

[Bruno_ABS]

Aparentemente há algum tipo de limitação quanto a extensão do caminho de diretório ...

Não sei se é exatamente isso, mas ao reestruturar as pastas para que o caminho ficasse mais curto, funcionou rsrsrs
Inclusive depois vou dar uma aprofundada nisso, pois vale tanto pra VB quanto .NET

 

Segue de onde tirei esse insight, na documentação da Microsoft:
https://docs.microsoft.com/pt-br/dotnet/api/system.io.pathtoolongexception?view=net-6.0